Re: SQL grouping

I understand, and I think Lim does also.  What we CAN do and what we WANT 
are two different things.  Just a last shot at our attempts to see if 
anyone had some last magic technique.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Tony Carolla <carolla@xxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/14/2005 01:27 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Re: SQL grouping






Hi Lim

<snip>
> SELECT Phone#, PhoneExt#, (%trim(featsString) concat feature)  as 
featsString,
</snip>

You can't use recursion in SQL... What I was talking about would go
something like this:

Select 

Phone, 
PhoneExt, 

Substring(
(
Case When Feature='ABC' then 'AAA' Else '' End ||
Case When Feature='DEF' then 'DEF' Else '' End ||
Case When Feature='HIJ' then 'HIJ' Else '' End ||
Case When Feature='XXX' then 'XXX' Else '' End ||
etc.
),1,3) As Feature1, 

Case When 
Len(
Case When Feature='ABC' then 'AAA' Else '' End ||
Case When Feature='DEF' then 'DEF' Else '' End ||
Case When Feature='HIJ' then 'HIJ' Else '' End ||
Case When Feature='XXX' then 'XXX' Else '' End ||
etc.
) > 3, 
Substring(
(
Case When Feature='ABC' then 'AAA' Else '' End ||
Case When Feature='DEF' then 'DEF' Else '' End ||
Case When Feature='HIJ' then 'HIJ' Else '' End ||
Case When Feature='XXX' then 'XXX' Else '' End ||
etc.
),4,3), ' ' End As Feature2, 

Case When 
Len(
Case When Feature='ABC' then 'AAA' Else '' End ||
Case When Feature='DEF' then 'DEF' Else '' End ||
Case When Feature='HIJ' then 'HIJ' Else '' End ||
Case When Feature='XXX' then 'XXX' Else '' End ||
etc.
) > 6, 
Substring(
(
Case When Feature='ABC' then 'AAA' Else '' End ||
Case When Feature='DEF' then 'DEF' Else '' End ||
Case When Feature='HIJ' then 'HIJ' Else '' End ||
Case When Feature='XXX' then 'XXX' Else '' End ||
etc.
),7,3), ' ' End As Feature3, 
etc.

>From lib/file

You would need to include one of the substring clauses, incrementing
the name FeatureX, the length check parameter, and the starting
position of the substring, for each feature a customer might have (ex.
if no customer has more than 5 different features, create five
clauses).  And within each clause, you would have to include a '||"
and a case statement for each possible feature code that is available.

I must warn you, your brain will hurt afterwards, and you will need a
safe ride home from work tonight ;-)


-- 
"Enter any 11-digit prime number to continue..."
-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.