IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Mutually exclusive condition in the where clause of a SQL


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
manikant pathak

New User


Joined: 09 May 2005
Posts: 37
Location: bangalore

PostPosted: Mon Mar 24, 2008 3:19 pm
Reply with quote

Hi,

Is there any way to give mutually exclusive condition in the where clause of a SQL query? I am having five input fields but they may all not be given at the same time. F-1,F-2,F-3 (forming the primary key of 1 table), F-4 (Primary key of 2 table), F-4 and F-5 (primary key in the third table). F-4 is the only common attributes in all 3 tables.Now either of these 3 primary keys can be given and then we have to extract a set of values which includes data from all the three tables with the help of table joins based on F-4.

One way is to have 3 separate cursor declarations and the check for the values in primary key and accordingly open the related cursor.

But instead of having 3 separate cursor i just want to have a separate cursor and give these 3 mutually exclusive condition in SQL query.
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Tue Mar 25, 2008 10:57 am
Reply with quote

Hi Manikant,

Please see the below post:
ibmmainframes.com/viewtopic.php?t=29154

You can code all these conditions in the where clause using a 'between' clause and then populate the higher and lower comparison fields for 'between' with values conditiolly depending on whatever input's u have to open the cursor.

Hope this helps.

Thanks,
Prajesh
Back to top
View user's profile Send private message
manikant pathak

New User


Joined: 09 May 2005
Posts: 37
Location: bangalore

PostPosted: Tue Mar 25, 2008 4:41 pm
Reply with quote

Prajesh,

Thanks for your response. But this was not what I am looking for. I will rephrase my question and then probably i may get few more responses.

My question is that I need to check 3 conditions in the where clause but when one condition is true it should not check for the next two and use the data given in the first condition only for accessing the databases.

Eg:

Select * from <Table-name_1 A,Table-name_2 B,Table-Name_3 C> where (A.col_1=:data and A.col_2=data) or A.col_1:data
AND A.col_1=B.col_1
AND A.col_1=C.col_1;

Now here user can give only column 1 (In this case it will give all the results matching with the col_1) or user can refine his/her search by giving both Col_1 and Col_2(They expect rows with Col-1=:data and col_2=:data only), but if I am joining the two conditions using 'OR' clause it is not refining the search and is giving the same result as when we give only col_1.

Can anyone guide me how to have just one SQL query for this purpose?

Any help on this will be greatly appreciated.

Regards,
MKP
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Tue Mar 25, 2008 5:07 pm
Reply with quote

Select * from <Table-name_1 A,Table-name_2 B,Table-Name_3 C>
where (A.col_1 betwen :hi-data1 and :lo-data1 and A.col_2 between :hi-data2 and :lo-data2)
AND A.col_1=B.col_1
AND A.col_1=C.col_1;


Now the program logic should have the following:

If Col1 is provided, move the value to both hi and lo HV variables
If Col2 is provided, move the value to both hi and lo HV variables
If not Col2 is provided, move the high values to hi and low values to low HV variables

Now try opening the cursor.. Hope you have got it now?
Back to top
View user's profile Send private message
manikant pathak

New User


Joined: 09 May 2005
Posts: 37
Location: bangalore

PostPosted: Tue Mar 25, 2008 5:31 pm
Reply with quote

Prajesh,

This I had already tried and logically it seems to be correct also but the trouble here is that it is not yielding me any result when i specified what you told for col2 not given.

Thnks,
MKP
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Tue Mar 25, 2008 5:40 pm
Reply with quote

Hope u have confirmed that col2 data_type matches with the values. In previous post, I assumed that datatype is CHAR.

If thats also fine icon_rolleyes.gif , we will wait and see if expert's have any diffrent opinion.
Back to top
View user's profile Send private message
manikant pathak

New User


Joined: 09 May 2005
Posts: 37
Location: bangalore

PostPosted: Tue Mar 25, 2008 5:46 pm
Reply with quote

Here the data type is 'int' but i gave the high and low values accordingly. I am also waiting for some of the experts to give their opinion. We can make the work easier by just having 3 different cursors declared and then open the related cursor after checking the input values but that is somethin I want to avoid.

Anyways thanks for all your time and effort.

Regds,
MKP
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Tue Mar 25, 2008 8:35 pm
Reply with quote

I think the one suggested by Prajesh_v_p would work.

If it is numeric field then
move 0 to lo-data2 and
move all 9 to Hi-data2

The inner query would fetch all the records between 0 and all 9 values and join with the outer query. whenever there are values returned in the inner query, if those values are available in the B and C table, this query would return the expected value.

Get back to us in case of any more clarification
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To search DB2 table based on Conditio... DB2 1
No new posts How to give complex condition in JCL . CLIST & REXX 30
No new posts selectively copy based on condition DFSORT/ICETOOL 3
This topic is locked: you cannot edit posts or make replies. Control-m JOB executing even when the... Compuware & Other Tools 6
No new posts Dynamic condition checks COBOL Programming 5
Search our Forums:

Back to Top