Datasource - Variables in SQL Query

[RJ!]

Hi All,

I had a search on the site & couldn't find anything that explains why my Datasource Query is not returning a value.

What I'm basically doing is querying the SQL table to confirm if the current System date exists in the table. I've added the Variable "SysDailyDate" to the SQL per below:

Code: Select all

SELECT        TM1_DAILY AS Period
FROM            TRANSACTION_DETAIL
GROUP BY TM1_DAILY, TYPE
HAVING        (TM1_DAILY = N'?SysDailyDate?') AND (TYPE = N'FMS')

I then have in my Prolog the GetCellS to get the "Current GL Daily Date" from our control cube & open the ODBC connection:

Code: Select all

#========= P R O L O G =========

MyCube = 'z_ctrl_measures';

# define ODBC datasource for process from z_ctrl_measures
ODBCPath = CellGetS(MyCube,'ODBCPath Trans','String1');
DatasourceNameForServer=ODBCPath;
DatasourceNameForClient=ODBCPath;

# Define Variables
SysDailyDate = CellGetS(MyCube,'Current GL Daily Date','String 1') ;
ReportPath = CellGetS (MyCube , 'FilePath Trans Report', 'String 1');

# Open the ODBC Connection
ODBCOpen(ODBCPath ,'','') ;

I've insterted this ASCIIOUTPUT into my Epilog to see what as come through:

Code: Select all

Asciioutput ( ReportPath | '\sql_DlyPeriods.txt', 'SQL Period: ' | Period |  'System Period: ' | SysDailyDate );

Per that output file SysDailyDate is able to be looked up but the "Period" is blank (and not being fed the SQL query).

If i insert an actual date (i.e "14 Apr 2013") instead of the variable, it works perfectly...

I can only assume I've gotten the syntax wrong somewhere? Or am I going about this the wrong way?

[RJ!]

Found my issue...

Just found out that you can't reference to your Datasource within the Prolog or Epilog tab...

I moved the code that checked result from the SQL Datasource Date to the System Date to the Data Tab & now it all works fine!

[jim wood]

It's any datasource to be clear. Any datasource is mapped to the variables. These variables only apply to Meta and Data tabs.

[rmackenzie]

Just found out that you can't reference to your Datasource within the Prolog or Epilog tab...

But you can reference parameters in the TI in the query. E.g. if you set up a parameter in your TI called pDate then your SQL would read:

Code: Select all

SELECT        TM1_DAILY AS Period
FROM            TRANSACTION_DETAIL
GROUP BY TM1_DAILY, TYPE
HAVING        (TM1_DAILY = N'?pDate?') AND (TYPE = N'FMS')

And the Prolog would read:

Code: Select all

#========= P R O L O G =========

MyCube = 'z_ctrl_measures';

# define ODBC datasource for process from z_ctrl_measures
ODBCPath = CellGetS(MyCube,'ODBCPath Trans','String1');
DatasourceNameForServer=ODBCPath;
DatasourceNameForClient=ODBCPath;

# Define Variables
pDate = CellGetS(MyCube,'Current GL Daily Date','String 1') ;
#
# etc.....
#

[ingdmr]

I have this sql statement works fine in architect but modeler no, the question is what parameters are used as in modeller 10.1.1?

select 1 FROM [FACT_FINANCIAL] WHERE PERIOD_SK =CAST('?pp?' as int)