Execute SQL Query from TI processes

[dharav]

Hello All,

I have following queries in SQL which executes normally and gives desired output:

Select a.Actual,a.Periodyear,a.Monthname,a.Driver, a.TM1_PC_Data_BD6,a.TM1_LOC_Data_BD6,a.TM1_Var_Data_BD6,
b.TM1_PC_Data,b.TM1_LOC_Data,b.TM1_Var_Data,
(a.TM1_PC_Data_BD6-b.TM1_PC_Data) as Var_PC_Cube_BD6_Now,
(a.TM1_LOC_Data_BD6-b.TM1_LOC_Data) as Var_LOC_Cube_BD6_Now,
(a.TM1_Var_Data_BD6-b.TM1_Var_Data) as Var_Var_Cube_BD6_Now
into [dbtm1].[dbo].[BD6_LOCPCVar_Data_Recon]
from [dbtm1].[dbo].[Temp_BD6_LOCPCVAR_Driver_DataValidation] a
Inner Join [dbtm1].[dbo].[Temp_LOCPCVAR_Driver_DataValidation] b
On (a.actual=b.actual and a.periodyear=b.periodyear and a.monthname=b.monthname and a.driver=b.driver);

I copy paste the query into TM1 Prolog tab as follow:

ODBCOPEN();

Vsql=
'Select a.Actual,a.Periodyear,a.Monthname,a.Driver, a.TM1_PC_Data_BD6,a.TM1_LOC_Data_BD6,a.TM1_Var_Data_BD6,
b.TM1_PC_Data,b.TM1_LOC_Data,b.TM1_Var_Data,
(a.TM1_PC_Data_BD6-b.TM1_PC_Data) as Var_PC_Cube_BD6_Now,
(a.TM1_LOC_Data_BD6-b.TM1_LOC_Data) as Var_LOC_Cube_BD6_Now,
(a.TM1_Var_Data_BD6-b.TM1_Var_Data) as Var_Var_Cube_BD6_Now
into [dbtm1].[dbo].[BD6_LOCPCVar_Data_Recon]
from [dbtm1].[dbo].[Temp_BD6_LOCPCVAR_Driver_DataValidation] a
Inner Join [dbtm1].[dbo].[Temp_LOCPCVAR_Driver_DataValidation] b
On (a.actual=b.actual and a.periodyear=b.periodyear and a.monthname=b.monthname and a.driver=b.driver)';

ODBCOutput('development',vSql);

It throws following error:

error Prolog line 228 Sql query "..."

Could anyone please look in to it and throws the light on how to include same query in TI process to run?

I am executing more than 6 Sql scripts from a TI process and they are executing perfectly f9. I just found problem with this sql script which is working f9 in SQL but throws error during TM1 TI process execution

Please feel free if you require further information.

Thank You

[jim wood]

Looking your code you might want to check this out:

http://www.tm1forum.com/viewtopic.php?t=7362

[declanr]

Dharav,

I think you may be mistaking the ODBC functions which are primarily for output from TI to SQL with the data source requirements.
I guess that what you want is for your query to be executed at run time so that you can use the Metadata and/or Data tabs in TI against the results of the query - if that is the case then get rid of ODBCOpen, ODBCOutput and ODBCClose and look at replacing with DataSourceQuery etc.

[dharav]

Thank You guys for prompt response

@ Jim Wood:

I check the link that you pasted here. That is about having problem with ODBCOPEN (Database connection is successful).
i already have 7-9 queries in same prolog before this query mentioned as above.
All of them are executing normally. It just not readable in SQL from TM1 that it seems.
As i mentioned earlier, same query runs successfully in SQL and giving me desired output.

@ declanr:

Basically, I had exported 3 tables from TM1 with 3 different cubes data. I want to populate the data in to a table through SQL join.
I did it in past and it worked great and still using same concept which works great. We then use the output table for BI reporting purpose
so that Finance team can see their reports on various activities and data recon.

In short: I am exporting my data in to SQL and using SQL query inside the TI processes to insert the out of SQL Joins in to Table A.

@ all

It seems there is an issue with defining the same query in prolog section

E.g.:

In Sql: Select * from tbl_ABC Where Month='Mar';

In Tm1:

vSql='Select * from tbl_abc where Month=''Mar''';

I hope you got me. Let me know if further explaination require to throw some lights on my query.

Please feel free if you require further information.

Thank You all for your time.

[declanr]

Dharav,

I do apologise - I have now looked at your SQL Query properly and understand what you are doing.

One thing to check before further investigation would be spaces in the statement - It has caught me out before that when you put a SQL statement in the prolog you assume that a line break will be understood by the query engine to also indicate a space... however it does not (which makes perfect sense when you think about it.)

This could just be how you have copied the code in but it looks as if you have only put a carriage return between Now and into in the snippet below:

Code: Select all

(a.TM1_Var_Data_BD6-b.TM1_Var_Data) as Var_Var_Cube_BD6_Now
into [dbtm1].[dbo].[BD6_LOCPCVar_Data_Recon] 

TM1 would need it to show as:

Code: Select all

(a.TM1_Var_Data_BD6-b.TM1_Var_Data) as Var_Var_Cube_BD6_Now 
into [dbtm1].[dbo].[BD6_LOCPCVar_Data_Recon] 

Which incidentally looks identical but on close inspection you will notice that I have put a space after "Now"... you also appear to have a couple more of these in your code but as I say it could just be how you have pasted it in to the forum.


EDIT - Note the forum automatically puts a space in after you start a new row when enclosed in a code statement... hence the first appearing to have a space and the second appearing to have 2 - in reality the first has none and the second has one.

[dharav]

@declnar

I just figured it out same and tried to paste the solution but when open the forum to do so, i found great work from an agile man.

Thank You.