Posted Fri, 21 Dec 2012 16:06:00 GMT by Portal Admin

I have a script that emails out revenue reports by division. The active divisions will change over time.
Here is what I am wanting to do, but not sure if sequel scripts would be able to handle this.
---
Loop through DIVISION file getting division # and division name
Check for orders for that division# for the date range
If there are orders, send revenue report passing to the report the division# as a parameter
---
I am unsure on how to
1) open up a cursor or fetch next to go through all the records in the DIVISION file
2) put a value into a variable and do a conditional statement on it, ie: &cnt = fetch('select count(*) from orders where div = '&&div'')

Any help would be appreciated
Thanks
Derek

Posted Thu, 16 Jun 2016 05:00:00 GMT by Portal Admin

 

Derek,
  Here is what I did.  I pulled all of the records from the base table that I needed with an ACCUM field.  This gave me a distinct value that I could reference for each line.  I then did a while loop that incremented each time to allow me to reference the next value.
 
DCL VAR(&OUTPUT) TYPE(*CHAR) LEN(30)
CHGVAR VAR(&OUTPUT) VALUE("QTEMP/CHILDREN") /* CHANGE this to the Output File name */
DCL VAR(&PARTLVL) TYPE(*DEC) LEN(3 0)
DCL VAR(&CHILDCNT) TYPE(*DEC) LEN(5 0)
DCL VAR(&CURRCNT) TYPE(*DEC) LEN(5 0)
DCL VAR(&PRNTSEQ) TYPE(*CHAR) LEN(30)
CHGVAR VAR(&CHILDCNT) VALUESQL('SELECT char(Count(1)) FROM rmsfiles#/pspsp100(m015) where parnt ="&&UPLVL"')
CHGVAR VAR(&PARTLVL) VALUE(1)
IF COND(&childcnt >0)
EXECUTE SQL('SELECT "&&UPLVL" name(UPLVL),parnt, digits(cseqn) len(20) name(seqno), accum(1,parnt) len(5,0) name(ord), child, psreq FROM rmsfiles#/pspsp100(m015) where parnt ="&&UPLVL"') OUTFILE(&OUTPUT) REPLACE(*YES)
DOWHILE COND(&CHILDCNT>=&partlvl)
/* Enter your commands here */
CHGVAR VAR(&PARTLVL) VALUE(&partlvl+1)
ENDDO
ENDIF
 
I hope this helps.  Please contact me with questions.
jpyatt at flowserve.com

Originally Posted By jpyatt

You must be signed in to post in this forum.