|
View previous topic :: View next topic
|
| Author |
Message |
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
I want to compare 2 fields in the same table through a db2 query.
1. when the TYPE = 'X' that had a DEST equal to that when the TYPE = 'Y' .
All the above required data available in one table.
If i write the query in WHERE statement to satisfy the above condition, then
SELECT * FROM CUSTOMER C
WHERE ( (C.TYPE = 'X') = (C.TYPE = 'Y') AND (C.DEST = C.DEST) )
it is not working. Anyone want to check this? Thanks in advance |
|
| Back to top |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Looks like you are trying to see if TYPE has both values 'X' and 'Y' at the same time. Also C.DEST will always = C.DEST. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
I interpret it as
DEST of the Y row is equal to the DEST of the X row
something like ... ( the syntax might be blatantly wrong, just hinting the logic )
select * from table a
where a.type = 'X"
union
select * from table b
where b.type = 'y'
and b.dest = a.dest
somebody with better DB2 reminiscences might provide the correct one
 |
|
| Back to top |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
| Want to fetch records based on where the 'X' TYPE record that had a DEST value equal to that with 'Y' TYPE record. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you show some sample rows (only the columns that matter for this question) and show what the quesy should select.
The sample rows should have some rows to be selected and some that will not be selected.
Your description may be clear to you, but it is not so clear to others. |
|
| Back to top |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008
Posts: 110
Location: india
|
|
|
|
for example: table A
| Code: |
Invoice ORIGIN DEST TYPE
02345 MUMBAI CHENNAI I
03456 PUNE HYD I
09999 KOLKATA DELHI E
08888 CHENNAI PUNE E |
The condition is:
I want to fetch the records based on: The ORIGIN of 'E' TYPE equal to the DEST of 'I' TYPE
in the above example, i think 2 records matching and want to fetch these records...
If you need more info, please let me know... Thanks |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| maxsubrat wrote: |
for example: table A
| Code: |
Invoice ORIGIN DEST TYPE
02345 MUMBAI CHENNAI I
03456 PUNE HYD I
09999 KOLKATA DELHI E
08888 CHENNAI PUNE E |
The condition is:
I want to fetch the records based on: The ORIGIN of 'E' TYPE equal to the DEST of 'I' TYPE
in the above example, i think 2 records matching and want to fetch these records...
If you need more info, please let me know... Thanks |
This isn't tested but I think it will do it
| Code: |
SELECT A2.INVOICE, A2.ORIGIN, A2.DEST, A2.TYPE
FROM TABLEA A1, TABLEA A2
WHERE A1.TYPE = 'I'
AND A2.TYPE = 'E'
AND A2.ORIGIN = A1.DEST; |
If the table is large and doesn't have the necessary indexes it will be slow. |
|
| Back to top |
|
|
|
|
