Re: SQL question

Someone else mentioned doing the totals with some reporting tool. Otherwise, you have to use some "interesting" value like 9,999,999 to get the sort right when doing a UNION SELECT - and in this case, you couldn't use a true date data type.

Query management queries and forms are a good tool for this and are included on any box that has STRSQL. Otherwise, you can look at products like SEQUEL and a few others. Of course, good old Query for IBM i can do it, too. But you don't get the nice SQL SELECT statement capability.

----- Original Message -----

Thanks Buck, that worked good;

Display Data
Data width . . . . . . : 58
Position to line . . . . . Shift to column . . . . . .
....+....1....+....2....+....3....+....4....+....5....+...
Object Change Change TOTAL
Date Time
W88PLCHP 90,813 92,249 0
W88PLTRP 90,813 92,250 0
W88PTYPP 90,813 92,251 0
W88REIMP 90,813 92,252 0
W88SMPCP 90,813 92,253 0
W88SMPLP 90,813 92,253 0
W88SMPPP 90,813 92,254 0
W88SMPTP 90,813 92,255 0
W88SPDLP 90,813 92,256 0
W88WORKP 90,813 92,256 0
W88WORKT 90,813 92,257 0
W89BHSTP 90,813 92,258 0
W89PCNPP 90,813 92,259 0
W90RSKCP 90,813 92,300 0
W92TXTBP 90,813 92,300 0
W99PTYPP 90,813 92,301 0
TOTAL 9,999,999 0 1,671
******** End of data ********






SELECT odobnm, odldat, odltim, 0 as total FROM myfile
where odldat = 090713 or odldat = 090813
nion
elect 'TOTAL', 9999999, 0, count(*) as total from myfile
here odldat = 090713 or odldat = 090813
rder by 2





-----Original Message-----
From: Buck Calabro <kc2hiz@xxxxxxxxx>
To: midrange-l <midrange-l@xxxxxxxxxxxx>
Sent: Mon, Sep 9, 2013 12:19 pm
Subject: Re: SQL question


On 9/9/2013 11:47 AM, fbocch2595@xxxxxxx wrote:

Hi Buck, that statement gave me;
Sorry Frank, paste problem on my end :-(
SELECT odobnm, odldat, odltim, 0 as total FROM myfile
where odldat = 090713 or odldat = 090813
nion
elect 'TOTAL', 9999999, 0, count(*) as total from myfile
here odldat = 090713 or odldat = 090813
rder by 2
--buck