The desire is to extract data from 12 months of data from a ...

[robert fuschetto]

The desire is to extract data from 12 months of data from a table.

The user can only be prompted for a thru (end) Period. This report is being integrated onto an existing dashboard that already prompts for THRUPERIODGREAT! We do not we want to add a STARTPERIOD prompt thoso I must derive the start period in some fashion.

To aid in this, we have a calendar table. It shows each month(PERIOD) and how many months old it is(MO). I could derive the MOSTART:

Ex:

For PERIOD: 202110, MO=0, MOSTART = -12

202109, MO = -1, MOSTART = -13

202108, MO = -2, MOSTART = -14

etc.

I have a query that does this. The user is prompted and enter the thru period of 202105. The HOLD file results in an entry of:

PERIOD = 202105

MO = -5

MOSTART = -17

All I now need to do is get the HOLD file value for MOSTART into a parameter called &MOSTARTPARAM and I can set my subsequent query to filter on:

PERIOD <=&PERIOD AND

MO>=MOSTARTPARAM

How do I get the contents of HOLD.MOSTART into &MOSTARTPARAM

[Martin Yergeau]

Something such as this

SET ASNAMES = ON

TABLE FILE MyHoldFile

BY PERIOD AS 'PERIOD'

BY MO AS 'MO'

BY MOSTART AS 'MOSTARTPARAM'

ON TABLE HOLD AS EXTPARM

END

-RUN

-READFILE EXTPARM

-DEFAULTH &PARIOD = ''

-DEFAULTH &MO = ''

-DEFAULTH &MOSTARTPARAM = ''

 

-TYPE PERIOD: &PERIOD

-TYPE MO: &MO

-TYPE MOSTARTPARAM: &MOSTARTPARAM

[David Beagan]

Oops, you forgot the dash:

-READFILE EXTPARM

[robert fuschetto]

PossiblyI will try this!

[robert fuschetto]

Worked. ThanksVery cool!

[Martin Yergeau]

My pleasure

Change your title lebelling for SOLVED not CLOSED

CLOSED mean no resolution found