RE: Cool stuff that you may not have known was in i

["John Arnold \(MFS\)" <jarnold@xxxxxxxxxxxxxxxxxxxxxxxxxx>]

The ability to use CASE, CAST and SUBSTR functions in an SQL statement.
For example, the statement below takes a string value with hard plus and
minus signs and sums the values as numbers and interprets a code value
for client into a description. Then groups and sorts with the CASE
statement.

SELECT CHECKNO,SUM(Case when substr(CHECKAMT,1,1) = '+' then            
               cast(substr(CHECKAMT,2) as dec(13,2))/100 else       
               cast(substr(CHECKAMT,2) as dec(13,2))/-100 end) as
Amount, 
Case when FRACTNNO3 = 'AET' then 'Aaron Brothers  '

     when FRACTNNO3 = 'FED' then 'Franks Hardware '

     when FRACTNNO3 = 'HAR' then 'Harry and Sons  '

     else                  FRACTNNO3     end as CLIENT  FROM mbf0007p

GROUP BY CHECKNO, 
Case when FRACTNNO3 = 'AET' then 'Aaron Brothers  '

     when FRACTNNO3 = 'FED' then 'Franks Hardware '

     when FRACTNNO3 = 'HAR' then 'Harry and Sons  '

     else                  FRACTNNO3     end
ORDER BY CHECKNO, 
Case when FRACTNNO3 = 'AET' then 'Aaron Brothers  '

     when FRACTNNO3 = 'FED' then 'Franks Hardware '

     when FRACTNNO3 = 'HAR' then 'Harry and Sons  '

     else                  FRACTNNO3     end

John Arnold
 

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, April 03, 2008 8:07 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Cool stuff that you may not have known was in i

For a LUG meeting I am looking for "Cool stuff that you may not have
known was in i".  Target audience will have a mix of developers,
administrators and 1 or 2 who make their living in sales.  I am not
looking so much for programming tips and techniques.  I am looking more
for stuff like the job scheduler (WRKJOBSCDE).

Links to related articles would also be appreciated.

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.