|
View previous topic :: View next topic
|
| Author |
Message |
batu544
New User
Joined: 21 Jul 2005
Posts: 17
Location: bangalore
|
|
|
|
Hi All,
I have a requirement with following 2 tables. Here is the tables.
TAB1.
| Code: |
WHSE CLASS SUBCL PNUM PURGE_YN
--------- -------- -------- ----------- ------
0755 01 1 11111 P
0755 02 2 22222
0755 03 3 33333 P
0800 01 1 11111 P
0800 03 3 33333 P
0800 04 4 44444 P
0855 01 1 11111 P
0855 04 4 44444 P
0900 04 4 44444
|
TAB2
-------
| Code: |
WHSE1 CLASS1 SUBCL1 PNUM1 TEMP_YN
--------- -------- -------- ----------- ------
0755 01 1 11111 P
0755 02 2 22222
0755 03 3 33333 P
0800 01 1 11111 P
0800 03 3 33333 T
0800 04 4 44444 I
0855 01 1 11111 I
0855 04 4 44444 P
0900 04 4 44444 P
|
Now, need one query which will select only select the MMS # ( combination of class, subcl and PNUM field or combination of class1, subcl1 and PNUM1 field) where ....
1. PURGE_YN = 'P' and the corresponding TEMP_YN = 'I" and there should not be a PURGE_YN = ' ' for the same MMS # in any other row.
2. If for all the MMS # the PURGE_YN = 'P" and TEMP_YN = 'P' then exlude MMS number.
3. exclude those MMS # where both PURGE_YN = ' ' and TEMP_YN = ' '.
Thanks in advance
batu |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
It appears that the sample data does not contain examples of all of the rules (or i've misunderstood something  ). . .
Please review the sample input from the 2 tables and add/change anything as you determine necessary. Please practice with Preview before clicking Submit - Preview lets you see your post as it will appear to the forum rather than how it appears in the Reply editor.
Once you have verified the input is as you want, please post the expected output that would be selected by this query from the sample input posted. |
|
| Back to top |
|
|
batu544
New User
Joined: 21 Jul 2005
Posts: 17
Location: bangalore
|
|
|
|
Hi,
yes, the sample data contains all the example of all the conditions..
the result table will be ..
| Code: |
WHSE1 CLASS1 SUBCL1 PNUM1
--------- ---------- ---------- ----------
0855 01 1 11111
|
|
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
what happened to ??
|
|
| Back to top |
|
|
|
|
