View previous topic :: View next topic
|
Author |
Message |
manikant pathak
New User
Joined: 09 May 2005 Posts: 37 Location: bangalore
|
|
|
|
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 |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006 Posts: 133 Location: India
|
|
|
|
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 |
|
|
manikant pathak
New User
Joined: 09 May 2005 Posts: 37 Location: bangalore
|
|
|
|
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 |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006 Posts: 133 Location: India
|
|
|
|
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 |
|
|
manikant pathak
New User
Joined: 09 May 2005 Posts: 37 Location: bangalore
|
|
|
|
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 |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006 Posts: 133 Location: India
|
|
|
|
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 , we will wait and see if expert's have any diffrent opinion. |
|
Back to top |
|
|
manikant pathak
New User
Joined: 09 May 2005 Posts: 37 Location: bangalore
|
|
|
|
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 |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008 Posts: 107 Location: New York
|
|
|
|
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 |
|
|
|