Assuming a prior statement of the requirement that "I need to pick FieldName based on a LIKE ends with condition (like '%ield1'), not an equal condition..." is accurate, then again, beware the possibility of accidentally selecting more than one record when using the statement using the MAX aggregate to force reduction of the results to just the one row. The LOCATE scalar is the equivalent of the LIKE predicate whereby the literal\constant is between the zero-to-many replacement character; i.e. the % [percent sign]. Thus the following two predicates are effectively identical:

LOCATE('Fld1',FFIELDNAM)>0 /* or: LOCATE('ield1',FFIELDNAM)>0 */

FFIELDNAM LIKE '%Fld1%' /* or: FFIELDNAM LIKE '%ield1%' */

A simple example of a conundrum is FFIELDNAM having one row with 'Fld11' and another [that ends] with 'Fld1'.

Regards, Chuck

On 03 Dec 2012 12:18, Richard Schoen wrote:
OK it works. <<SNIP>>

Char(max(case when LOCATE('Fld1',FFIELDNAM)> 0
Then trim(ffieldval) end),30) as fld1 , ...

Richard Schoen on Monday, December 03, 2012 10:43 AM wrote:

How would LOCATE be used in my sample SQL ?

,char(max(Case when FFIELDNAM like '%Fld1'
then trim(ffieldval) end),30) as fld1
,char(max(Case when FFIELDNAM like '%Fld2'
then trim(ffieldval) end),30) as fld2
from formfld
where FFORMID = 'cb3c0801-93a8-1940-ad63-0004ac10'
group by fformid

This thread ...

Return to Archive home page | Return to MIDRANGE.COM home page