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.
midrange.com
