>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.

Firstly, pure LEFT join will bring up all records from ccharges regardless matching records from lcdm. If you want to get matches only then use INNER join.
If you still need in OUTER join, then I would try:
1. Implement parenthesis
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))
2. If it still doesn't work, then I would try old-fashioned syntax:
SELECT chgitem, units, amount, lcdm.cost ;
FROM ccharges,icdm
WHERE ccharges.chgitem = lcdm.chgitem ;
AND (ccharges.chgdate BETWEEN lcdm.effdate AND lcdm.enddate)
UNION ALL SELECT chgitem, units, amount, 0 as cost ;
FROM ccharges WHERE chgitem NOT IN (SELECT chgitem FROM lcdm)