BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
elan2799
Calcite | Level 5

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

----->>

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


View solution in original post

7 REPLIES 7
Reeza
Super User

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). 

elan2799
Calcite | Level 5
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
Reeza
Super User

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). 

Patrick
Opal | Level 21

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
        )
Ksharp
Super User

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

----->>

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


Patrick
Opal | Level 21

@Ksharp

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

Ksharp
Super User

Patrick,

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


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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