>All you SELECT gurus out there - Help me!!!
>
>I've a SELECT that used to work, but now appears to not be working. It's returning records that match on chgitem, but is ignoring the effdate criteria. How do I get this to work?
>
>SELECT chgitem, units, amount, NVL(lcdm.cost, 0.0000) AS cost ;
> FROM ccharges LEFT OUTER JOIN lcdm ON ccharges.chgitem = lcdm.chgitem ;
> AND ccharges.chgdate BETWEEN lcdm.effdate AND lcdm.enddate
>
>The return set is giving me multiple records for a chgitem regardless of the effdate match. I've tried putting the chgdate clause in the WHERE clause as well as a HAVING clause all to no avail.
>
>Thanks in advance.
Curtis,

Here's an adjustment that takes the non join criteria out of the join clause.

SELECT chgitem, units, amount, NVL(lcdm.cost, 0.0000) AS cost ;
  FROM ccharges LEFT OUTER JOIN lcdm ON ccharges.chgitem = lcdm.chgitem ;
 WHERE ccharges.chgdate BETWEEN lcdm.effdate AND lcdm.enddate