Dixon,

I will reformat your query, since I think will be easier to read it...

SELECT
WH_STCK, WH_BC, SUM(WH_QTY), PR_NAME ||' '||PR_DESC,
PR_PARLVL, PD_QTY, PO_ETA
FROM
WAREHOUSE JOIN
PRODUCT ON (WH_STCK=PR_STOCKNO) LEFT JOIN
PODETAIL ON (PD_STCK LIKE WH_STCK ||'%') LEFT JOIN
POTABLE ON (PD_NUM=PO_NUM)
WHERE
WH_LOCID='000011' AND
WH_LOCSEQ=100 AND
PO_LOCID=WH_LOCID AND
PO_LOCSEQ=WH_LOCSEQ AND
PO_INV=''
GROUP BY
WH_STCK, WH_BC, PR_NAME, PR_DESC, PR_PARLVL, PD_QTY, PO_ETA
HAVING
SUM(WH_QTY) < PR_PARLVL *1.10

First, I good thing to do is to alias the tables and use full qualified
column names.

The problem is that in your WHERE clause will filter records using PO_LOCID
= WH_LOCID (if you do not have a PO for the product the PO_LOCID will be
null and NULL is not equal to anything), so the where clause is resticting
the result set to return only the products that has PO. use the filter
condition on the join clause, since you specify left join even the products
that does not have PO will be retrivied.

try something like

SELECT
WH_STCK, WH_BC, SUM(WH_QTY), PR_NAME ||' '||PR_DESC,
PR_PARLVL, PD_QTY, PO_ETA
FROM
WAREHOUSE JOIN
PRODUCT ON (WH_STCK=PR_STOCKNO) LEFT JOIN
PODETAIL ON (PD_STCK LIKE WH_STCK ||'%') LEFT JOIN
POTABLE ON (PD_NUM=PO_NUM and PO_LOCID = '000011' and PO_LOCSEQ = 100
and PO_INV = '')
WHERE
WH_LOCID='000011' AND
WH_LOCSEQ=100
GROUP BY
WH_STCK, WH_BC, PR_NAME, PR_DESC, PR_PARLVL, PD_QTY, PO_ETA
HAVING
SUM(WH_QTY) < PR_PARLVL *1.10

HTH


Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br


At 18:58 23/09/2003 +0000, you wrote:

>I have the following query:
>
>SELECT WH_STCK, WH_BC, SUM(WH_QTY), PR_NAME ||' '||PR_DESC,
>PR_PARLVL, PD_QTY, PO_ETA
>FROM WAREHOUSE JOIN PRODUCT ON (WH_STCK=PR_STOCKNO)
>LEFT JOIN PODETAIL ON (PD_STCK LIKE WH_STCK ||'%')
>LEFT JOIN POTABLE ON (PD_NUM=PO_NUM)
>WHERE WH_LOCID='000011' AND WH_LOCSEQ=100 AND PO_LOCID=WH_LOCID AND
>PO_LOCSEQ=WH_LOCSEQ AND PO_INV=''
>GROUP BY WH_STCK, WH_BC, PR_NAME, PR_DESC, PR_PARLVL, PD_QTY, PO_ETA
>HAVING SUM(WH_QTY) < PR_PARLVL *1.10
>
>what it is supposed to do is get the product and sum of product in
>warehouse that is less than 110% of the par-level plus return
>quantities on a purchase order and the ETA of PO.
>
>The query only works on the products that have a PO on them. Any
>product in the warehouse which is less than parlevel, but doesn't
>have a PO issued on it fails to show in this query.
>
>I thought left join on this would take what is in the warehouse and
>optionally add the PO data to it if it was there. What am I doing
>wrong?
>
>Dixon Epperson

----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.514 / Virus Database: 312 - Release Date: 28/08/2003


[Non-text portions of this message have been removed]