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
 

 

Mutually exclusive condition in the where clause of a SQL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Mutually exclusive condition in the where clause of a SQL
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    Post subject:
Reply with quote

Hi Manikant,

Please see the below post:
http://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    Post subject: Reply to: Mutually exclusive condition in the where clause o
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    Post subject:
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    Post subject: Reply to: Mutually exclusive condition in the where clause o
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    Post subject:
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    Post subject: Reply to: Mutually exclusive condition in the where clause o
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    Post subject:
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    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
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts INCLUDE condition questions van bui DFSORT/ICETOOL 1 Thu Jun 30, 2016 9:52 am
No new posts 2 vsam file compare and update a fiel... ABINAYATHULASI DFSORT/ICETOOL 6 Sun May 29, 2016 11:15 am
No new posts File matching using specific condition mf_karthik DFSORT/ICETOOL 3 Fri Nov 20, 2015 11:04 pm


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