I've had issues like this in both RPG and .Net before.
Always need to insure field uniqueness.
RJS Software Systems Inc.
Where Information Meets Innovation
Document Management, Workflow, Report Delivery, Forms and Business Intelligence
Web Site: http://www.rjssoftware.com
Tel: (952) 736-5800
Fax: (952) 736-5801
Toll Free: (888) RJSSOFT
date: Mon, 03 Dec 2012 12:55:23 -0600
from: CRPence <CRPbottle@xxxxxxxxx>
subject: Re: Interesting SQL Question - Flatten Multiple Records into
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'.
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