Good example.

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

Always need to insure field uniqueness.

Richard Schoen
RJS Software Systems Inc.
Where Information Meets Innovation
Document Management, Workflow, Report Delivery, Forms and Business Intelligence
Email: richard@xxxxxxxxxxxxxxx
Web Site:
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>>

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

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

This mailing list archive is Copyright 1997-2015 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