Automating Date Changes in Oracle Data Pull

[oleviasharbaugh]

I am pulling data from Oracle tables using the Eviews/SQL code below, but would like a way to make the process a bit more user friendly. I run the program once a month, and need the end date to change each month to reflect a new month of data. Normally my team creates %date = "2023:11" at the top of the program, and then uses the %date throughout, so that you only need to change the value once at the very top to change the date of the entire program. However, with the SQL code, this isn't as straightforward. Is there some similar way for me to automate the date change in the SQL code below?

Code: Select all

pageload(type=odbc, timeout=900) FilePath\OracleConnection.dsn "SELECT TRIM( D."FSTGMN")||'_A'||TRIM( C."VALCLS")||'_'||TRIM( B."CREREG")  AS "COMBOFIELD",F."YEARMO" AS "YEARMO",SUM( A."AREA"/1000 )  AS "AREAM" FROM TableName A,LU.STC D,LU.FIPS E,CMFS.CHR_CHR_CHRREG B,LU.MDB F,CMFS.CHR_CHRNEW_FSTHVCLX C WHERE   (D."FSTGMN" IN ('STORES', 'WARENM', 'OFFICE', 'AUTO', 'MFG', 'EDUC', 'HEALTH', 'PUB', 'REL', 'AMUSE', 'MISCNR', 'ONEFAM', 'TWOFAM', 'APTMT', 'HOTEL', 'DORM')) AND (A.MDB Between 1237 and 1486)  AND  (A."STC"=D."STC") AND (A."FIPS"=E."FIPS") AND (A."MDB"=F."MDB")  AND  (A."VALUE" > C.MIN AND A."VALUE" <= C.MAX) AND (D.STHMN=C."STHMN") AND (F.YEAR=C."YEAR") AND (E.FREGMN=B."REGMN")  GROUP BY TRIM( D."FSTGMN")||'_A'||TRIM( C."VALCLS")||'_'||TRIM( B."CREREG") , F."YEARMO", A."MDB", C."VALCLS"" @keep *
pageunstack(page = pagename, namepat=?_*) COMBOFIELD yearmo @ ?*

Using the code above as a reference, the MDB is the date variable and I would need the 1486 to become a 1487 next month to pull in the new month of data. Is there a way to somewhat automate this? Some of the programs will have 30+ different pulls from Oracle, so the idea of going in and manually changing the SQL code for each is a bit daunting.

Thanks in advance for any advice!

[EViews Steve]

I don't know what your Oracle date values are based on, but wouldn't something like this work?

This example also just calculates the "2023.11" date based on the current date. The only thing hard-coded is the oracle base date values since I don't know where this value comes from:

Code: Select all

logmode l

!lastmonth = @dateadd(@datefloor(@now, "mm"), -1, "mm")
logmsg lastmonth is: !lastmonth '738824
%lastmonthstr = @datestr(!lastmonth, "yyyy:mm")
logmsg lastmonthstr is: %lastmonthstr '2023:11

!oracle_base = 1486
!oracle_base_dt = @makedate(2023.10, "yyyy.mm")
logmsg oracle_base_dt is: !oracle_base_dt '738793

'count how many months since oracle_base_dt
!offset = @datediff(!lastmonth, !oracle_base_dt, "mm")
logmsg offset is: !offset '1

!oracle_dt = !oracle_base + !offset
logmsg oracle_dt is: !oracle_dt

%sql = "SELECT * FROM table WHERE mdb BETWEEN 1237 AND " + @str(!oracle_dt)
logmsg SQL is: %sql

pageload(type=odbc, timeout=900) c:\files\test.dsn %sql


My log window after running:

2023-12-08_13h25_56.png (5.82 KiB) Viewed 3105 times

Steve