Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

change where clause based on condition

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vijigobi

New User


Joined: 13 Feb 2007
Posts: 5
Location: Bangalore

PostPosted: Thu Dec 04, 2008 10:48 pm    Post subject: change where clause based on condition
Reply with quote

I have to compare table A and B based on A_C1 & B_C1, if B_C1 is NULL in table B then I have to compare with A_C2 & B_C2

My result table should have all rows from Table A

Can we use Left outer join and case statement in where clause ?
If so Please provide the query or some other alternative.

Thanks in advance

Data removed - reposted below
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Dec 05, 2008 3:04 am    Post subject:
Reply with quote

Hello,

Please practice using the "Code" tag. It will make this type of post more readable and will preserve alignment. You tried to align using the Reply editor and (as you've seen icon_wink.gif ) and that does not do what you'd prefer. If you use the code tag, you won't need to work hard to get the presentation you want. Use Preview to see your post as the forum will see it and then Submit whan you are satisfied with the appearance.

What have you coded so far? If you post what you have, someone here may have suggestions. It is more likely to get help than to get someone to write the code for you.

One way to get what you need it the "old-fashioned" way - write a bit of code and not try to do it in one sql query. . .
Back to top
View user's profile Send private message
vijigobi

New User


Joined: 13 Feb 2007
Posts: 5
Location: Bangalore

PostPosted: Fri Dec 05, 2008 3:39 am    Post subject:
Reply with quote

Hi
Sorry I didn't aware of the code tag till now..

here is the better pic.

Code:

TABLE A 
---------
A_C1    A_C2
-----  ------
1        A
2         
3        C
4        D
5         
6        E
7        G
8
9
 
TABLE B
-----------
B_C1    B_C2     B_C3
-----   -----    ----
1        A        Z1
2        B        Z2
4        D        Z3
5        Y        Z4
--       E        Z7
--       G        Z5
10       M        Z6
11       N        Z8


I have to compare table A and B based on A_C1 & B_C1, if B_C1 is NULL in table B then I have to compare with A_C2 & B_C2

My result table should have all rows from Table A

Code:

A_C1    A_C2      B_C3
-----  -----      -----   
1        A          Z1
2        B          Z2
3        C          --
4        D          Z3
5        Y          Z4
6        E          Z7
7        G          Z5
8                   --
9                   --


PS: I am not writing any program; trying to acheive in a single (or multiple) query if possible
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Dec 05, 2008 4:12 am    Post subject:
Reply with quote

Hello,

Quote:
Sorry I didn't aware of the code tag till now..
Not a problem icon_smile.gif I'll edit the previous post to remove what you re-aligned.

Quote:
PS: I am not writing any program; trying to acheive in a single (or multiple) query if possible
I understand that you prefer using only a query or 2, but depending on how soon you need the output, you may want to reconsider.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Dec 05, 2008 7:16 am    Post subject:
Reply with quote

I don't have away to test this right now, it may not get all the rows from table a.

Code:
SELECT A_C1, A_C2, B_C3
FROM A, B
WHERE A_C1 = B_C1
      OR (A_C2 = B_C2 AND B_C1 IS NULL);
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm
No new posts Joinkeys with condition scdinesh DFSORT/ICETOOL 10 Tue Feb 14, 2017 12:20 am
No new posts Fail to change physical VSAM filename... jacobdng CICS 7 Fri Jan 20, 2017 12:36 pm
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us