Re: Constructing an SQL WHERE clause for a composite key

I would think there's a couple of ways to skin that cat.

One is slightly easier on the developer but may have some performance
impact because it probably eschews indexes.

The other is more index friendly.


Field File Type Length
CCUST RCM DECIMAL 8
CNME RCM CHARACTER-O 50
SELECT CCUST, CNME, ccust concat cnme as idxcol FROM erplxf/rcm
WHERE CCUST CONCAT CNME <= '12345678RIGHTEOUS'
ORDER BY CCUST, CNME

SELECT CCUST, CNME, ccust concat cnme as idxcol
FROM erplxf/rcm
WHERE CCUST <12345678 OR (CCUST=12345678 AND CNME='RIGHTEOUS')
ORDER BY CCUST, CNME
*** subset two
SELECT CCUST, CNME, ccust concat cnme as idxcol
FROM erplxf/rcm
WHERE CCUST >12345678 OR (CCUST=12345678 AND CNME>'RIGHTEOUS')
ORDER BY CCUST, CNME

Rob Berendt