Page 1 of 1

DB2 QUERY

PostPosted: Thu Oct 20, 2016 4:08 pm
by MFDEV
Hi,

I have two tables X1 and Y1, the table X contains a records as shown in the below

X1 table data

ID     NAME MIDDLE LAST CRTN_DTE   UPDTE_DTE
12345  JOHN JOHN   JOHN 2016-10-15 2016-10-20
 


Y1 table data

ID      STATUS     FK_ID CRTN_DTE   UPDTE_DTE
603356  2          12345 2016-10-20 2016-10-20  
603356  4          12345 2016-10-16 2016-10-18
 

The common colums are X1 Table ID attribute and y1 table , FK_ID attribute.

What I want here is when status is '2' then X1 data should not come and when it is '4' then X1 data should come .

Many Thanks In advance

CODE' D and ALIGNED
To make things more readable for the people who will spend their time trying to help You

Re: DB2 QUERY

PostPosted: Thu Oct 20, 2016 6:32 pm
by Akatsukami
And if status is 3?

Re: DB2 QUERY

PostPosted: Thu Oct 20, 2016 7:51 pm
by MFDEV
Hi Akatsukami,

Thanks for the reply.
And, there is not status '3' , only 2 and 4.

Many Thanks

Re: DB2 QUERY

PostPosted: Fri Oct 21, 2016 1:54 am
by NicC
What is your output - only the X1 record or data from y1 as well? Do the records have to match on ID? If so what then. Looks like a simple query if you are matching on ID and y1,status = 4. What problem are you having? What have you tried?

Re: DB2 QUERY

PostPosted: Fri Oct 21, 2016 3:37 pm
by MFDEV
Thanks Nic for the reply.

My requirement is when status is '4' then only delete X1 record but problem is even for status '2' the X1 record is same .
When tried this query

SELECT * FROM C1.x1 A  
WHERE EXISTS                  
(SELECT * FROM C1.Y1 B  
WHERE A.ID=B.FK_ID          
AND B.STS='4'                  
AND A.ID='12345')


The output is X1 table record and when change to STS to '2' same record will display because X1 record is same for both status.
I think, If I delete Y1 table sts '4' records first then it would be fine ? so no need to go for above stuff ?

Many Thanks

CODE' D AGAIN

Re: DB2 QUERY

PostPosted: Sat Oct 22, 2016 1:47 am
by NicC
Why not try it? And use the code tags when presenting code, data, control cards etc.