| View previous topic :: View next topic |
| Author |
Message |
vivek1983
Intermediate
Joined: 20 Apr 2006
Posts: 222
Topics: 24
|
 Posted: Mon Jul 10, 2006 9:01 am Post subject: Query for value checking in both or either of the tables. |
 |
|
Hi,
My requirement is to find if a value V1 is there in either of the tables T1, T2 or in both the tables.
I tried giving the following query:
| Quote: |
SELECT T1.Field2,
T2.Field2
FROM Table1 T1,
Table2 T2
WHERE ((T1.field1 = Given-value
OR T2.field1 = Given-value)
AND T1.field1 = T2.Field1)
OR
((T1.field1 = Given-value
and T2.field1 <> Given-value)
AND T1.field1 <> T2.Field1)
OR
((T1.field1 <> Given-value
and T2.field1 = Given-value)
AND T1.field1 <> T2.Field1)
FOR FETCH ONLY
|
This returns many rows contradictory to my expectations. When i browsed the table, i could find only few rows that is either present in both, or in either of the tables T1, T2.
As i have not extensively used DB2 before, this seems to be tricky for me. I am sure there must be some very easy solution.
Please let me know if you require any additional details. (Regrets if i am a bit confusing).
Thanks and Regards,
Vivek.G |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Jul 10, 2006 11:40 am Post subject: |
|
|
Vivek,
Why do you need a join ? Can't you just do simple query ? First do a select on table1 and
If you get a sql code of or or 100 then check if the row is present in table2.
| Code: |
SELECT Field2,
FROM Table1
WHERE field1 = Given-value
;
EVALUATE SQLCODE
WHEN 0
WHEN +100
PERFORM SELECT-ON-TABLE2
END-EVALUATE
SELECT Field2,
FROM Table2
WHERE field1 = Given-value
;
|
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vivek1983
Intermediate
Joined: 20 Apr 2006
Posts: 222
Topics: 24
|
| Posted: Tue Jul 11, 2006 1:59 am Post subject: |
|
|
Kolusu,
| Quote: |
Why do you need a join ?
|
The reason for using a join is that i want to put that query inside a cursor so that i can handle multiple fetch from the cursor.
Please advise.
Thanks,
Vivek.G
_________________
Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay) |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Jul 11, 2006 4:45 am Post subject: |
|
|
| Quote: |
The reason for using a join is that i want to put that query inside a cursor so that i can handle multiple fetch from the cursor.
|
Vivek,
You don't need a JOIN to put the query in a cursor. You can use the same query as I have shown. Also if your intention is to just find out if you have any value that corresponds to the given value. You can use COUNT(*) or use FETCH FIRST clause on the select statement.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
