| View previous topic :: View next topic |
| Author |
Message |
batu544
Beginner
Joined: 02 Aug 2005
Posts: 75
Topics: 27
|
 Posted: Sun Mar 29, 2009 4:34 pm Post subject: Need Help is writing one query.. |
 |
|
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
_________________
Regards,
batu |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Sun Mar 29, 2009 8:04 pm Post subject: |
|
|
batu544,
I have a difficult time visualizing the final output, so why don't you show us the final output you are expecting from the above data.
Also the combination of class, subcl and PNUM field results in duplicates in both tables. How do you map the duplicates to duplicates in tab2? Does WHSE make the combination unique?
It would be better if you can explain each combo key from the tables and explain as to why it is dropped/kept
Kolusu |
|
| Back to top |
|
|
batu544
Beginner
Joined: 02 Aug 2005
Posts: 75
Topics: 27
|
| Posted: Mon Mar 30, 2009 1:31 am Post subject: |
|
|
Hi,
the combination of WHSE,CLASS,SUBCL and PNUM is the prImary key. and in TAB2 these columns are foreign keys.
| Quote: |
Also the combination of class, subcl and PNUM field results in duplicates in both tables. How do you map the duplicates to duplicates in tab2? Does WHSE make the combination unique?
|
Yes.
| Quote: |
It would be better if you can explain each combo key from the tables and explain as to why it is dropped/kept |
MMS # 01111111 : It should be selected. because for this MMS # the PURGE_YN = 'p' for all the WHSE and TEMP_YN = 'I' for 1 WHSE and there is no PURGE_YN = ' ' for this MMS # in TAB1.
MMS# 022222222 : Should be dropped. because both PURGE_YN = ' ' and TEMP_YN = ' '.
MMS# 03333333 : should be dropped becasue : PURGE_YN = 'P' for both the cases and TEMP_YN not = 'I' for a single WHSE also.
MMS# 04444444: should be dropped because.. for this one PURGE_YN = ' ' though there is on TEMP_YN = 'I'.
Finally .. the result table will contain ..
| Code: |
WHSE1 CLASS1 SUBCL1 PNUM1
--------- ---------- ---------- ----------
0855 01 1 11111
|
_________________
Regards,
batu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
