ODBC SQL in the TI Prolog advanced tab

[MattDavis]

Hello the board..

am attempting to (for the first time) used the advanced tabs to open, output and close an SQL query via ODBC. I have rtfm but have a couple of hopefully quick questions on specifics of coding in the TI prolog:

1. my SQL query is longer than 255 characters so I need to break it up, as per manuals instructions. Can anyone confirm I don't need to semicolon the end of each statement, just the last one (as it is a concatenation)?

2. what is the syntax when introducing single quote characters into the code? from the manual....

ODBCOutput('Accounting', ‘INSERT [CategoryID], [CategoryName]FROM Categories;');

but in my query, part of the SQL code contains the following statements...

and bal.actual_flag = 'A'
and bal.period_name = 'DEC-09'

My question is how to cope with the single quotes surrounding the specific items 'A' and 'DEC-09' - they need to be included in the query itself, but won't the single quote will break the SQLQuery in the TI ODBCOutput formula? I've come across similar problems in VBA in introducing double-quotes into the code, and you have to double them up thus "", but haven't encountered this in TI before.

3. and lastly within the SQL code itself, I'd like to be able to utilise run-time parameters from the TI parameters in the code. I'm not an SQL programmer - so first... is it possible? and if so what's the syntax please?

using the example again of:

and bal.period_name = 'DEC-09'

I'd like to be able to say........

and bal.period_name = pPeriod

where pPeriod is a TI parameter that users can input on run time

Many many thanks in advance for any assistance you can provide!

Matt

[John Hammond]

Hi Michael

Use the expand function to help you write your SQL easily. Single Quotes need to be doubled up to appear within single quotes.

Code: Select all

query = expand('INSERT INTO [dmcuser].[tbl_cbcMMRABF]
(
[dimYear],
[dimPeriod],
[dimOrganisation],
[dimCounterParty],
[dimChartOfAccounts],
[dimFunction],
[dimProject],
[dimTier],
[dimMeasures],
[Value]
) VALUES (
''%dimYear%'',
''%dimPeriod%'',
''%OrganisationCode%'',
''%dimCounterParty%'',
''%AccountCode%'',
''%CostCentreCode%'',
''%ProjectCode%'',
''%dimTier%'',
''%dimMeasures%'',
%Value%
)') ;

# comment out items if not needed

odbcoutput('TM1 Data Manager',query);

[MattDavis]

Thanks Jon!