Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] how to use SQL Operators: test_exp1 IN(exp1,exp2) ?
 Login/Join
 
nox
Platinum 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,


WebFOCUS v8.2.06 , Windows
 
Posts: 137 | Registered: August 29, 2019Report This Post
Clif
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').


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
nox
Platinum 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' )


WebFOCUS v8.2.06 , Windows
 
Posts: 137 | Registered: August 29, 2019Report This Post
Clif
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


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
nox
Platinum 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.


WebFOCUS v8.2.06 , Windows
 
Posts: 137 | Registered: August 29, 2019Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders