SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to use where clause with IN statement for two differnt columns

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

How to use where clause with IN statement for two differnt columns

I am using a statement to using where clause but it is returing error. Below is the statment i am using

 

select * from (select
a.QST_ID as qID,
a.QST_VER_NO as ver_no,
a.MI_OPTNS_ID as index1,
b.MI_OPTNS_TXT as value
from QST_MI_OPTNS_LINK_TBL a INNER JOIN MI_OPTNS_LIST_TBL b on
a.MI_OPTNS_ID = b.ID)
where (qID, ver_no) in ( (2973,3), (2983,4))

 

I am receiving syntax error near where statement.

 


Accepted Solutions
Solution
‎07-11-2016 06:21 AM
Super User
Posts: 10,020

Re: How to use where clause with IN statement for two differnt columns


where (qID, ver_no) in ( (2973,3), (2983,4))

----->>

where  catx(' ',qID, ver_no) in ( '2973 3', '2983 4')


View solution in original post


All Replies
Super User
Posts: 19,770

Re: How to use where clause with IN statement for two differnt columns

You can't specify the where clause like that. 

 

You meed to do so,etching like the following. This assumes that you don't need specific combinations. 

 

where qid in (value list) and/or ver_no in (value list). 

New Contributor
Posts: 2

Re: How to use where clause with IN statement for two differnt columns

Hi,

Each qid column has 1 to 7 version ids. I want to extract the Qid 2973 for
version 3 & 2983 for version 4 alone but this 2973 ,2983 matches multiple
version. If u want I can send sample datasets.

CHEERS,
ELA
Super User
Posts: 19,770

Re: How to use where clause with IN statement for two differnt columns

You can't specify the where clause like that. 

 

You meed to do so,etching like the following. This assumes that you don't need specific combinations. 

 

where qid in (value list) and/or ver_no in (value list). 

Respected Advisor
Posts: 4,173

Re: How to use where clause with IN statement for two differnt columns

[ Edited ]

I believe below is the valid SQL logic for what you're after

  select
    a.QST_ID as qID,
    a.QST_VER_NO as ver_no,
    a.MI_OPTNS_ID as index1,
    b.MI_OPTNS_TXT as value
  from 
    QST_MI_OPTNS_LINK_TBL a 
    INNER JOIN 
    MI_OPTNS_LIST_TBL b 
    on
      a.MI_OPTNS_ID = b.ID
      and 
        (
          a.QST_ID=2973 and a.QST_VER_NO=3
          or
          a.QST_ID=2983 and a.QST_VER_NO=4
        )
Solution
‎07-11-2016 06:21 AM
Super User
Posts: 10,020

Re: How to use where clause with IN statement for two differnt columns


where (qID, ver_no) in ( (2973,3), (2983,4))

----->>

where  catx(' ',qID, ver_no) in ( '2973 3', '2983 4')


Respected Advisor
Posts: 4,173

Re: How to use where clause with IN statement for two differnt columns

@Ksharp

Concatenation works of course but it's also certainly the least performant option. 

Super User
Posts: 10,020

Re: How to use where clause with IN statement for two differnt columns


Patrick,

Agree. But it is more readable, isn't it ?


☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 459 views
  • 0 likes
  • 4 in conversation