MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » December 2012

Re: Interesting SQL Question - Flatten Multiple Records into One Record



fixed

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







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

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact