SQL Statement Creation Question for Drill Through

[shopkins]

Hello,

I have a situation where a single value in TM1 could possibly relate to 2 different values back in the relational database where I need to get the data in a drill through.

For example:
Market is set to UNAL in TM1.
In the Prolog, I was thinking of adding the following if-then-else statement:
IF (Market @= 'UNAL');
MarketSQL = "AND BUF.BU_CODE_MKT in (' ', 'UNAL')";
ELSE;
MarketSQL = "AND BUF.BU_CODE_MKT = '?Market?'";
ENDIF;

I am getting the following error:
Syntax error on or before:
"AND BUF.BU_CODE_MK
invalid expression

Once I get the MarketSQL set, I will add it to the query for the drill through with ?MarketSQL?

Thoughts??

Thank you!

[tomok]

Look at the code where you are assigning a string value to the variable Market_SQL. Strings have to be encapsulated inside an apostrophe ('), not a quotation mark ("), in TM1. You'll also have to play around with your string building to get the syntax correct to get the apostrophe into the SQL string as well. This will be a good exercise for you to learn how to build strings in a TI process.

[shopkins]

I was hopeful that I could use double quotes and realized quickly that I needed to use single quotes.

I was able to create the SQL that I needed by using | & char(39):
IF (Market @= 'UNAL');
MarketSQL = 'AND BUF.BU_CODE_MKT in (' | char(39) | ' ' | char(39) | ', ' | char(39) | '?Market?' | char(39) | ')';
ELSE;
MarketSQL = 'AND BUF.BU_CODE_MKT = ' | char(39) | '?Market?' | char(39);
ENDIF;

I was able to send MarketSQL out with ASCIIOUTPUT and it helped me debug what I was doing. I was able to create the proper lines.