Page 1 of 1
Operators in TI : SQL IN equivalent
Posted: Thu Jun 27, 2019 1:15 pm
by 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 !
Re: Operators in TI : SQL IN equivalent
Posted: Thu Jun 27, 2019 2:33 pm
by PavoGa
SCAN(sCode, 'ABC') > 0
Re: Operators in TI : SQL IN equivalent
Posted: Thu Jun 27, 2019 2:48 pm
by 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;
Re: Operators in TI : SQL IN equivalent
Posted: Thu Jun 27, 2019 3:53 pm
by 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
Re: Operators in TI : SQL IN equivalent
Posted: Thu Jun 27, 2019 4:09 pm
by Wim Gielis
You’re right Maren
Re: Operators in TI : SQL IN equivalent
Posted: Thu Jun 27, 2019 4:40 pm
by Emixam
You are right !! I really need to sleep!
Thanks guys !
Re: Operators in TI : SQL IN equivalent
Posted: Fri Jun 28, 2019 4:14 am
by 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.
Re: Operators in TI : SQL IN equivalent
Posted: Fri Jun 28, 2019 5:06 pm
by PavoGa
Andy Key wrote: ↑Fri Jun 28, 2019 4:14 am
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);