topic Re: How to use where clause with IN statement for two differnt columns in SAS Data Management

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

elan2799 — Fri, 08 Jul 2016 11:34:09 GMT

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.

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

Reeza — Fri, 08 Jul 2016 12:13:47 GMT

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

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

Reeza — Fri, 08 Jul 2016 12:13:48 GMT

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

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

elan2799 — Fri, 08 Jul 2016 13:47:28 GMT

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

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

Patrick — Sat, 09 Jul 2016 03:17:27 GMT

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
        )

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

Ksharp — Sat, 09 Jul 2016 04:09:13 GMT


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

----->>

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

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

Patrick — Sat, 09 Jul 2016 04:22:05 GMT

@Ksharp

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

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

Ksharp — Sat, 09 Jul 2016 06:18:46 GMT


Patrick,

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