| View previous topic :: View next topic |
| Author |
Message |
rajeev5174
Beginner
Joined: 05 Jun 2007
Posts: 25
Topics: 10
|
 Posted: Wed May 16, 2012 10:00 am Post subject: Select PO_NBR from 1 table matching it against 3 tables |
 |
|
Hi,
I have 3 table tabe1,table2,table3, here i need to pick all of the purchase orders(po_nbr) column which are there in table1 and should be there in table3 and in table3 purchase orders(po_nbr) not existing in table2
Thanks
Raj |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Wed May 16, 2012 11:50 am Post subject: |
|
|
rajeev5174,
Try this untested sql
| Code: |
SELECT T1.PO_NBR
FROM TABLE1 T1
,TABLE3 T3
WHERE T1.PO_NBR = T3.PO_NBR
AND T3.PO_NBR NOT IN (SELECT PO_NBR
FROM TABLE2)
;
|
Kolusu
PS : Use a descriptive Title to explain your problem. Post detailed information on what you're trying to accomplish. Do not make people guess what you mean. This will give you a much better chance of getting a good answer to your question. |
|
| Back to top |
|
|
rajeev5174
Beginner
Joined: 05 Jun 2007
Posts: 25
Topics: 10
|
| Posted: Wed May 16, 2012 1:18 pm Post subject: |
|
|
Thanks Kolusu for the reply, just to confirm another thing the table T1 and T3 teh PO number are defined with a picture clause of s9(9) comp and the table T2 is defined as x(26), here the data is stored having leading zeros and traing spaces ie ta tha data is stotred for eg like "0012345789 " So in that case how could the comparison to be modified?
Thanks,
Raj |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Wed May 16, 2012 3:35 pm Post subject: |
|
|
| rajeev5174 wrote: | Thanks Kolusu for the reply, just to confirm another thing the table T1 and T3 teh PO number are defined with a picture clause of s9(9) comp and the table T2 is defined as x(26), here the data is stored having leading zeros and traing spaces ie ta tha data is stotred for eg like "0012345789 " So in that case how could the comparison to be modified?
Thanks,
Raj |
rajeev5174,
Any other rules that you forgot to mention? Anyway S9(9) comp occupies 4 bytes and can store a max value of '2,147,483,647' ie. 10 digits. Now you are comparing this to a 26 byte value. What happens if you have PO number greater than 10 bytes? how do you handle that? or do you always have spaces from 11th byte and the first 10 bytes have numeric values with leading zero's? Show us examples of data from all the tables and expected results from that. |
|
| Back to top |
|
|
rajeev5174
Beginner
Joined: 05 Jun 2007
Posts: 25
Topics: 10
|
| Posted: Thu May 17, 2012 1:24 pm Post subject: |
|
|
Yes kolusu, the PO Number will be spaces after the 10 bytes & only the 1'st 10 bytes will have data value and with leading zeros can have ..
T1- value 11111
T3 -Value "000011111 "
Now this should match and should be shown in output
Thanks,
Raj |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Thu May 17, 2012 2:39 pm Post subject: |
|
|
| rajeev5174 wrote: | Yes kolusu, the PO Number will be spaces after the 10 bytes & only the 1'st 10 bytes will have data value and with leading zeros can have ..
T1- value 11111
T3 -Value "000011111 "
Now this should match and should be shown in output
Thanks,
Raj |
What about Table2 PO_NBR? You need to a better job of explaining the rules. Do you even read my post completely ? Since you only mentioned about T1 and T3 matching, change the WHERE condition to the following.
| Code: |
WHERE CHAR(T1.PO_NBR,10) = SUBSTR(T3.PO_NBR,1,10)
|
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
