On 09-Feb-2015 09:53 -0600, Rene van der Kraats wrote:
<<SNIP>>
I'm dabbling with Query/400 (just for entertainment, mind you) but,
never having used this program before, it's a bit hard to get into
the right 'mind-set'.
I have these database files with a layout similar to this:
DATE SYMBOL VALUE
01/01/2015 AAA 50
01/01/2015 BBB 40
01/01/2015 CCC 20
01/02/2015 AAA 60
01/02/2015 BBB 35
01/02/2015 CCC 25
01/03/2015 AAA 40
01/03/2015 BBB 25
01/03/2015 CCC 23
etc...
I'm trying to produce a query where it calculates the delta between
similar symbols, per day.
So:
01/01/2015 AAA = 50
01/02/2015 AAA = 60 -> delta compared to previous day = 10
01/03/2015 AAA = 40 -> delta compared to previous day = 20
...and so on, for each symbol.
I think I need to build some query result fields roughly like
delta = abs(VALUE(current date) - VALUE(current date - 1))
(I hope this makes sense), but I cannot figure out how to do this.
I've read a boat-load of PDF's, but none really shows me how to
approach this problem (or perhaps I just don't 'get it' :)
Can someone perhaps push me in the right direction? I know this
probably can be solved much easier and quicker with other tools, but
out of curiosity I'd still like to know how this is accomplished in
Query/400 :)
The Query/400 report writer does not support the capability for what
are often called _running totals_; there is no ability to summarize or
perform calculations using the data in prior row(s). For that reason
alone, deferring to other tooling is probably the best. Considering
that the Query/400 feature had been effectively shelved long ago and
long deprecated [with SQL interfaces being recommended as the
alternative], there is little value in the /exercise/ being attempted.
Finally, given that the Query/400 is an interactive feature with no
definitional source /language/, describing a query is not succinct;
rendering the Query Definition (QRYDFN) as the keystrokes to define or
as a Printed Definition are both far less desirable than using the SQL.
Setting up the given with the SQL:
create table dbd /* DayByDay */
( "DATE" DATE
, "SYMBOL" CHAR(3)
, "VALUE" DEC
)
;
insert into dbd values
( '01/01/2015' , 'AAA' , 50 )
, ( '01/01/2015' , 'BBB' , 40 )
, ( '01/01/2015' , 'CCC' , 20 )
, ( '01/02/2015' , 'AAA' , 60 )
, ( '01/02/2015' , 'BBB' , 35 )
, ( '01/02/2015' , 'CCC' , 25 )
, ( '01/03/2015' , 'AAA' , 40 )
, ( '01/03/2015' , 'BBB' , 25 )
, ( '01/03/2015' , 'CCC' , 23 )
;
In effect, the following SQL could be mimicked using the Query/400
[aka: Query for i5/OS or Query for IBM i]. A first Query Definition
would use the Output File feature to generate a physical output file
with the data instead of the VIEW. A second Query Definition would use
that previously created output file to perform the effectively
equivalent join using "2=Matched records with primary file" along with
the effectively equivalent expressions\column-list and ordering, as the
SELECT that follows the VIEW definition:
create view dbdp /* DayByDayPrev */ as
( select a.*, DATE("DATE" - 1 DAYS) as PREV_DAY
from dbd as a
)
;
select
p."DATE", p."SYMBOL", p."VALUE"
, ABS( d."VALUE" - p."VALUE" ) as DAYS_DIFF
from dbdp p
left outer join dbdp d
on p.PREV_DAY = d."DATE"
and p."SYMBOL" = d."SYMBOL"
order by p."SYMBOL", p."DATE"
Note: The Query/400 feature does not have the ABSolute value
function, but for reporting to display or printer, the Report Column
Formatting enables Numeric Editing capabilities to present results as
effectively unsigned.
midrange.com
