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>>
Select FFORMID,
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 ?
select
FFORMID
,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
