Using Parameters in Data Source SQL

[mattgoff]

I've been reading through this old forum thread on using a parameter to allow run-time modification of the SQL in a data source tab. I think I'm following everything in that thread, but I've been unable to get it to return any results.

I'm just running the process manually now, not as part of a chore. The Prolog tab is correctly clearing the period, and the parameter used in the SQL is getting set to the correct value. If I run the SQL in an interactive client, I'm getting rows returned. I've added ASCIIOutput to every tab, and I'm only getting files for the Prolog and Epilog tabs-- nothing from Data nor Metadata.

Anyone see what I'm missing?

Thanks,
Matt

Data Source Tab / Query:

Code: Select all

SELECT SET_OF_BOOKS,PERIOD_NAME,DEPTCODE,SPENDINGACCOUNTCODE,PROJECT_CODE,AMOUNT,CURRENCY_CODE
FROM appsr.xxdsc_TM1_IS_v
WHERE (PERIOD_NAME = '?pFirstOpenPeriodSQL?' ) AND
      (TRANSLATED_FLAG IS NULL OR TRANSLATED_FLAG <> 'R') AND
       CURRENCY_CODE <> 'STAT';

Advanced Tab / Parameter:

Parameter: pFirstOpenPeriodSQL
Type: String
Default Value: ADJ-08
Prompt Question: (blank)

Advanced Tab / Prolog

Code: Select all

#####
# set some globals for diagnostics
#####

     vOutputDir = CellGetS('sys_Global_Parameters', 'Export_File_Path', 'Value');
     vOutputFile = vOutputDir | 'Oracle_GL Cube - Reload Recent P&L Data DIAGNOSTIC.csv';

#####
# calculate periods to clear and reload
#####

     vFirstOpenPeriodYear = ATTRS( 'Scenario' , 'Current Forecast' , 'Forecast Start Period' );
     vFirstOpenPeriod = SUBST ( vFirstOpenPeriodYear , 1 , 3 );
     vFirstOpenYear = SUBST ( vFirstOpenPeriodYear , 5 , 4 );
     pFirstOpenPeriodSQL = vFirstOpenPeriod | '-' | SUBST ( vFirstOpenYear , 3 , 2 );

ASCIIOUTPUT ( vOutputFile , pFirstOpenPeriodSQL );

#####
# clear first period
#####

     IF ( SubsetExists ( 'Period_Accounting' , 'tmp_Clear for Oracle Refresh' ) = 1 );
          SubsetDestroy ( 'Period_Accounting' , 'tmp_Clear for Oracle Refresh' );
     ENDIF;

     SubsetCreate ( 'Period_Accounting' , 'tmp_Clear for Oracle Refresh' );
     SubsetElementInsert ( 'Period_Accounting' , 'tmp_Clear for Oracle Refresh' , vFirstOpenPeriod , 1 );

     IF ( SubsetExists ( 'Year' , 'tmp_Clear for Oracle Refresh' ) = 1 );
          SubsetDestroy ( 'Year' , 'tmp_Clear for Oracle Refresh' );
     ENDIF;

     SubsetCreate ( 'Year' , 'tmp_Clear for Oracle Refresh' );
     SubsetElementInsert ( 'Year' , 'tmp_Clear for Oracle Refresh' , vFirstOpenYear , 1 );

     IF ( ViewExists ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' ) = 1 );
          ViewDestroy ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' );
     ENDIF;

     ViewCreate ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' );
     ViewExtractSkipCalcsSet ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' , 1 );
     ViewExtractSkipRuleValuesSet ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' , 1 );
     ViewExtractSkipZeroesSet ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' , 1 );
     ViewSubsetAssign( ' Oracle_GL', 'tmp_Clear for Oracle Refresh' , 'Period_Accounting' , 'tmp_Clear for Oracle Refresh' );
     ViewSubsetAssign( ' Oracle_GL', 'tmp_Clear for Oracle Refresh' , 'Year' , 'tmp_Clear for Oracle Refresh' );

     ViewZeroOut( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' );

     ViewDestroy ( 'Oracle_GL' , 'tmp_Clear for Oracle Refresh' );
     SubsetDestroy ( 'Period_Accounting' , 'tmp_Clear for Oracle Refresh' );
     SubsetDestroy ( 'Year' , 'tmp_Clear for Oracle Refresh' );

Advanced Tab: Metadata

code to create new elements if encountered

Advanced Tab: Data

code to load data, worked fine in non-dynamic SQL process

Advanced Tab: Epilog

(blank)

[Steve Rowe]

From memory I think this

WHERE (PERIOD_NAME = '?pFirstOpenPeriodSQL?' ) AND

should read

WHERE (PERIOD_NAME = '%pFirstOpenPeriodSQL%' ) AND

or maybe without the quotes, or maybe

WHERE (PERIOD_NAME = Expand('%pFirstOpenPeriodSQL%') ) AND

You definatly want a percentage not a question mark, unless thats some strange thing the forum has done to your post.

If you still dont have any luck, I suggest ASCIIOutputing your SQL statment in the prolog and running that directly in a SQL client. Maybe youhave a format issue with the date or something?

Cheers,

[Ivan Cepero]

Did you set all your variables to type 'Other' not 'Ignore'?

[mattgoff]

WHERE (PERIOD_NAME = '%pFirstOpenPeriodSQL%' )

Really? It looks like ? is the correct notation per the TM1 Developers Guide page 3-60. Maybe something else needs to be set to have the variables interpolated?

If you still dont have any luck, I suggest ASCIIOutputing your SQL statment in the prolog and running that directly in a SQL client. Maybe youhave a format issue with the date or something?

OK, DatasourceQuery is blank, but there's clearly SQL in the Query field of the Data Source tab. I tried reverting back to the static SQL but DatasourceQuery is still blank....

Did you set all your variables to type 'Other' not 'Ignore'?

Yep, all set to Other. This was an existing process with static SQL that worked fine. I just added a parameter and some code in the Prolog to set the parameter based on an element's attribute, and inserted a WHILE with the parameter into my SQL.

Thanks for the ideas so far,
Matt

[Steve Rowe]

I'm pretty sure I'm right on using %, see the Expand TI keyword. I think that when you use this in the datasource you just omit the Expand bit. Can't see your reference as I don't have a doc called the developers guide

Anyhow if your static SQL statement (before you make the period dynamic) is not producing any results then the query isn't "right" yet or maybe you have some ODBC issues...

EDIT : Normally TI throws an exception at runtime if the SQL has the wrong syntax. If it's running without errors then it's likely that the data source doesn't recognise the date in the format you are supplying it or your trying to load data for a period that does not exist in the source yet...
EDIT2 : Are you sure you need the trailing semi colon in the query, that doesn't look quite right to me?


I'd test your static SQL in a proper SQL client if you have one. Once it's working there migrate the query to the TI process and re test.

If it's still working then make it dynamic.

HTH a bit...

[mattgoff]

Problem solved.

This project was an attempt to modify an existing process to replace static with dynamic SQL. I edited the existing process to replace the static bit of my SQL with a parameter. After editing the SQL, it asked me if I wanted to keep only derived variables or all-- since I had all my variables renamed (different than the column headers), I chose to keep all. This was the wrong choice. All the debugging described above ensued.

Just now, I tried building a very simple test process with dynamic SQL, and it worked fine. I kept adding bits from my original process and got it all the way rebuilt with everything working. I took a shot in the dark (for completeness' sake, and for the sake of people searching this forum down the line) and went back to my original process, adjusted the SQL, selected keep derived variables only instead, and it worked. Apparently there's something going on behind the scenes that is not set correctly if you don't allow it to replace the original variables. Yay TM1.

Matt