Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CLOSE] how to use SQL Operators: test_exp1 IN(exp1,exp2) ?
Go
New
Search
Notify
Tools
Reply
  
[CLOSE] how to use SQL Operators: test_exp1 IN(exp1,exp2) ?
 Login/Join
 
Silver Member
posted
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,
 
Posts: 34 | Registered: August 29, 2019Reply With QuoteReport This Post
Guru
posted Hide Post
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').
 
Posts: 386 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
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' )
 
Posts: 34 | Registered: August 29, 2019Reply With QuoteReport This Post
Guru
posted Hide Post
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  
 
Posts: 386 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
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.
 
Posts: 34 | Registered: August 29, 2019Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [CLOSE] how to use SQL Operators: test_exp1 IN(exp1,exp2) ?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.