DB2 QUERY



IBM's flagship relational database management system

DB2 QUERY

Postby MFDEV » Thu Oct 20, 2016 4:08 pm

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
MFDEV
 
Posts: 15
Joined: Mon Dec 01, 2014 3:25 pm
Has thanked: 2 times
Been thanked: 0 time

Re: DB2 QUERY

Postby Akatsukami » Thu Oct 20, 2016 6:32 pm

And if status is 3?
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: DB2 QUERY

Postby MFDEV » Thu Oct 20, 2016 7:51 pm

Hi Akatsukami,

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

Many Thanks
MFDEV
 
Posts: 15
Joined: Mon Dec 01, 2014 3:25 pm
Has thanked: 2 times
Been thanked: 0 time

Re: DB2 QUERY

Postby NicC » Fri Oct 21, 2016 1:54 am

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?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: DB2 QUERY

Postby MFDEV » Fri Oct 21, 2016 3:37 pm

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
MFDEV
 
Posts: 15
Joined: Mon Dec 01, 2014 3:25 pm
Has thanked: 2 times
Been thanked: 0 time

Re: DB2 QUERY

Postby NicC » Sat Oct 22, 2016 1:47 am

Why not try it? And use the code tags when presenting code, data, control cards etc.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post