Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Join

Status
Not open for further replies.

johnny45

Technical User
Nov 8, 2006
136
CA
Trying to match Purchace order VS Item pricing to verify our margins.....

This works fine:
Code:
SELECT POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORL.OQORDERED, POPORL.UNITCOST
FROM (POPORL INNER JOIN POPORH1 ON POPORL.PORHSEQ = POPORH1.PORHSEQ) INNER JOIN ICITEM ON POPORL.ITEMNO = ICITEM.ITEMNO
WHERE (((POPORH1.PONUMBER)="003786") AND ((ICITEM.STOCKITEM)<>0))
ORDER BY POPORH1.PONUMBER, POPORL.ITEMNO;
Now I wanted to bring in the contract pricing for this as follows:
Code:
SELECT POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORL.OQORDERED, POPORL.UNITCOST, ICCUPR.FIXPRICE, Format(ICCUPR.FIXPRICE/POPORL.UNITCOST/100,"#0.00 %") AS ['%'], ICCUPR.CUSTNO
FROM ((POPORL INNER JOIN POPORH1 ON POPORL.PORHSEQ = POPORH1.PORHSEQ) INNER JOIN ICITEM ON POPORL.ITEMNO = ICITEM.ITEMNO) LEFT JOIN ICCUPR ON POPORL.ITEMNO = ICCUPR.ITEMNO
WHERE (((POPORH1.PONUMBER)="003786") AND ((ICCUPR.CUSTNO)="25013") AND ((ICITEM.STOCKITEM)<>0))
ORDER BY POPORH1.PONUMBER, POPORL.ITEMNO;

this does NOt return all items on or PO if there is NO contract pricing...I though a left join would return ALL items on PO and put NULLS were there is NO contyract pricing...??
 
Probably because you've got a function that references a field in ICCUPR.
 
Thanks Tuba,
I tried removing the function and still got the same results....
 
Woudl I need to use a HAVING clause to get this to work ?
 
Thank you tuba2007.

Now I get back more rows but NOT all rows on PO.

I figured out that the omited rows are rows where there IS a contract price but with a different customer number, so this would be the tweak needed to get all PO rows with the contract pricing by a specific customer number or blank pricing if there is no contract pricing for that customer number.....
I tried this but I think there is error with my syntax:
Code:
SELECT POPORH1.PONUMBER, POPORH1.VDNAME, POPORL.ITEMNO, POPORL.ITEMDESC, POPORL.OQORDERED, POPORL.UNITCOST, ICCUPR.FIXPRICE, Format(ICCUPR.FIXPRICE/POPORL.UNITCOST/100,"#0.00 %") AS ['%'], ICCUPR.CUSTNO
FROM ((POPORL INNER JOIN POPORH1 ON POPORL.PORHSEQ = POPORH1.PORHSEQ) INNER JOIN ICITEM ON POPORL.ITEMNO = ICITEM.ITEMNO) LEFT JOIN ICCUPR ON POPORL.ITEMNO = ICCUPR.ITEMNO
WHERE (((POPORH1.PONUMBER)="003786") AND ((ICCUPR.CUSTNO)="25013" Or (ICCUPR.CUSTNO) Is Null OR( ICCUPR.FIXPRICE is null AND ((ICCUPR.CUSTNO)="25013")) AND ((ICITEM.STOCKITEM)<>0))
ORDER BY POPORH1.PONUMBER, POPORL.ITEMNO;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top