Outer Join on Multiple LIKE

[frasernm]

Hi,

This is driving me crazy...

I have a query which returnsthe number of records where a text string is like one of two patterns. But I'd like to include all of the patterns that have no records in the listing for grahing purposes.

The query which returns patterns and the number of them is below.

SELECT Code, Count(Code) as NumCodes
FROM CodeRecords, CodeTypes
WHERE trunc(odate) >= to_datestartDate,'DD-MON-YYYY') AND trunc(odate) <= to_dateendDate,'DD-MON-YYYY')
AND (lower(text) like lower (new_Pattern) or lower(text) like lower(old_Pattern))
GROUP BY Code ORDER BY Code ASC;

Thanks in advance,

Fraser

 

[THoey]

Not sure if this is what you are asking, but could you use a UNION query to get your results?

Terry M. Hoey

 

[rcurva]

or, you could try...

SELECT Code, Count(Code) as NumCodes
FROM CodeRecords, CodeTypes
WHERE trunc(odate) >= to_datestartDate,'DD-MON-YYYY') AND trunc(odate) <= to_dateendDate,'DD-MON-YYYY')
AND (lower(NVL(text,'dummy'))) IN (lower (new_Pattern), lower (old_Pattern), 'dummy')
GROUP BY Code ORDER BY Code ASC;

this is, if i got your requirements right.