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

[Richard Schoen <richard@xxxxxxxxxxxxxxx>]

Good example. 

I've had issues like this in both RPG and .Net before. 

Always need to insure field uniqueness. 

Regards,
Richard Schoen
RJS Software Systems Inc.
Where Information Meets Innovation
Document Management, Workflow, Report Delivery, Forms and Business Intelligence
Email: richard@xxxxxxxxxxxxxxx
Web Site: http://www.rjssoftware.com
Tel: (952) 736-5800
Fax: (952) 736-5801
Toll Free: (888) RJSSOFT
------------------------------

message: 4
date: Mon, 03 Dec 2012 12:55:23 -0600
from: CRPence <CRPbottle@xxxxxxxxx>
subject: Re: Interesting SQL Question - Flatten Multiple Records into
	One	Record

   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