Hi,

I'm try using test_exp1 IN(exp1,exp2) in SQL Calculator to do Field checking if in another field. so I try using SQL Operators and it gave me an error that I don't understand:

Error:
(FOC14069) SQL SYNTAX ERROR ON LINE 1 AT 'IN'
T1.ACCOUNT_ID IN ( T4.BIL_TYPE_ID='Account_Fee' )

My Expression:
T1.ACCOUNT_ID IN ( T4.BIL_TYPE_ID= "Account_Fee" )

my objective is to check Account_ID in T1 match "Account_Fee" in T4.

what is proper way to setup the expression?

thanks

This message has been edited. Last edited by: nox,

As the onscreen help shows there are two versions of IN. It returns true or false so it's mostly used in WHERE conditions

(1) "Determines if a specified value matches any value in a list" For example
ACNT IN ('0000','9999') returns true when ACNT is '9999' and false when it's '1111'

(2) "Determines whether a specified value matches any value in a subquery" for example
(ACNT) IN (SELECT account FROM accounts WHERE billtype = 'Account Fee').

Hi Clif,

Thanks for the reply, I have also try your second method as well, but similar Error appear.

Error:

(FOC14069) SQL SYNTAX ERROR ON LINE 1 AT 'IN'
( T1.ACCOUNT_ID ) IN ( SELECT T4.BIL_CODE_ID FROM T4.BIL_CODE WHERE T4.BIL_TYPE_ID='Account_Fee' )

My Expression:
( T1.ACCOUNT_ID ) IN ( SELECT T4.BIL_CODE_ID FROM T4.BIL_CODE WHERE T4.BIL_TYPE_ID= 'Account_Fee' )

Here's a complete working example of the generated SQL statement using DataMigrator standard tutorial tables. Please confirm this you are using your sub-select in a WHERE condition. If so please open an IRL case and provide source synonyms and flow.

SELECT 
   T1.STORE_CODE , --Company ID 
   T1.PROD_NUM , --Product Number 
   SUM(T1.QUANTITY )  AS QUANTITY , --Quantity 
   SUM(T1.LINEPRICE )  AS LINEPRICE  --Line Total 
FROM 
   dmord T1  
 WHERE 
   (T1.STORE_CODE ) IN (SELECT T1.STORE_CODE FROM 
      DMCOMP SQLSQ01  WHERE SQLSQ01.STATE = 'PA' )  
 GROUP BY  
   T1.STORE_CODE ,  
   T1.PROD_NUM  

Hi Clif,

Thank you for your reply, I'll give it a try again, but in my opinion, really weird Error message that keep saying syntax error at "IN" while "IN" suppose to be one of their SQL function.