Trying to match Purchace order VS Item pricing to verify our margins.....
This works fine:
Now I wanted to bring in the contract pricing for this as follows:
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...??
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;
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...??