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!