Operators in TI : SQL IN equivalent

[Emixam]

Hello,

What's the best way to do this SQL code in a TI ?

Code: Select all

WHERE nAmount > 0 AND sCode IN ('A', 'B', 'C')

I was thinking about something like this :

Code: Select all

IF(nAmount > 0 & (sCode @= 'A' % sCode @= 'B' % sCode @= 'C') );
	#Do Something
ENDIF;

But i'm not sure how TM1 handle the parenthesis.
This next code works but I wonder if there's a "better" way to do it ? Is there an equivalent of "IN" in TM1 ?

Code: Select all

IF(nAmount > 0);
	IF(sCode @= 'A' % sCode @= 'B' % sCode @= 'C');
		#Do Something
	ENDIF;
ENDIF;

Thanks and have a good day !

[PavoGa]

SCAN(sCode, 'ABC') > 0

[Emixam]

I always neglect this function !!

You reversed the parameter ..

SCAN( sFindMe, sScanMe );

Code: Select all

IF(nAmount > 0 & SCAN( 'ABC', sCode ) > 0 );
	#Do Something
ENDIF;

Thanks Ty !

EDIT: Here's the correct code:

Code: Select all

IF(nAmount > 0 & SCAN( sCode, 'ABC' ) > 0 );
	#Do Something
ENDIF;

[MarenC]

Hi,

am I being a bit slow here, but isn't the point to reverse the parameter? I.e. it should be SCAN(sCode, 'ABC') > 0.

Scan('ABC', sCode) does not make much sense to me?

regards, Maren

[Wim Gielis]

You’re right Maren

[Emixam]

You are right !! I really need to sleep!

Thanks guys !

[Andy Key]

As string comparison is not case sensitive ('b'='B'), but Scan is (Scan( 'b', 'B') = 0); and as you are matching on a specific term not a combination of terms (i.e. sCode = 'B' should pass but sCode = 'BC' should fail) I would go with:

Code: Select all

If( Scan( '<' | Upper( sCode) | '>', '<A><B><C>') > 0)

Include an Upper round the second part of the Scan if necessary.

[PavoGa]

As string comparison is not case sensitive ('b'='B'), but Scan is (Scan( 'b', 'B') = 0); and as you are matching on a specific term not a combination of terms (i.e. sCode = 'B' should pass but sCode = 'BC' should fail) I would go with:

Code: Select all

If( Scan( '<' | Upper( sCode) | '>', '<A><B><C>') > 0)

Include an Upper round the second part of the Scan if necessary.

There are a number of ways to manage this depending on overall context of the TI or rule. This:

Code: Select all

If( Scan( EXPAND('<%sCode%>'), '<A><B><C>') > 0);

Of course this does not deal with the fact that the <A><B><C> string has to be built if dynamic. And if the assumption is that sCode must be a single char then:

Code: Select all

If( Scan(UPPER(sCode), 'ABC') > 0 & LONG(sCode) = 1);