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!