title: [Amazon](500310) Invalid operation: This type of IN/NOT IN query is not supported yet;
body: <p>I'm converting this query from Netezza to run with my RedShift dw. I'm continuously getting this error.</p>
  
  <p>Amazon Invalid operation: This type of IN/NOT IN query is not supported yet;</p>
  
  <p>I have tried converting it using 'EXISTS/NOT EXISTS' condition but still not successful. Can someone give his/her input how should i convert that IN, NOT IN part?</p>
  
  <pre><code>CREATE TEMP TABLE TMP_EMPLY_BRND AS 
  (
  Select EMPLY_SRRGT_ID,
             EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_STRT_DT_KEY,
             EMPLY_PRMRY_PRDCT_DPRTMNT_GRP_SRRGT_ID,
  
  From           
  ((
   (SELECT E.EMPLY_SRRGT_ID,
             E.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_STRT_DT_KEY,
      FROM INS_EDW_CP.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT E
      where (((E.EMPLY_SRRGT_ID, E.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_STRT_DT_KEY, E.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_END_DT_KEY )=ANY
            (SELECT distinct A.EMPLY_SRRGT_ID , A.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_STRT_DT_KEY, A.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_END_DT_KEY
             FROM INS_EDW_CP.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT A
             WHERE (A.EMPLY_PRMRY_BRND_SRRGT_ID &gt; 0 )
             GROUP BY A.EMPLY_SRRGT_ID , A.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_STRT_DT_KEY, A.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_END_DT_KEY
             HAVING ((COUNT(DISTINCT A.EMPLY_PRMRY_PRDCT_DPRTMNT_GRP_SRRGT_ID) &gt; 1)
                     AND (COUNT(DISTINCT A.EMPLY_PRMRY_BRND_SRRGT_ID) = 1))))
                    or ((E.EMPLY_MRCHNDS_GRP_NBR , E.EMPLY_PRMRY_BRND_SRRGT_ID)  in  (select 947,-1)
                     or  (E.EMPLY_MRCHNDS_GRP_NBR , E.EMPLY_PRMRY_BRND_SRRGT_ID) in  (select 952,-1) ))
      Group BY
      E.EMPLY_SRRGT_ID, 
  UNION
  SELECT E.EMPLY_SRRGT_ID,
             E.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_STRT_DT_KEY,
      FROM INS_EDW_CP.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT E
      where    (
      ((E.EMPLY_SRRGT_ID, E.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_STRT_DT_KEY, E.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_END_DT_KEY )&lt;&gt;ALL
            (SELECT distinct A.EMPLY_SRRGT_ID , A.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_STRT_DT_KEY, A.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_END_DT_KEY
             FROM INS_EDW_CP.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT A
             WHERE (A.EMPLY_PRMRY_BRND_SRRGT_ID &gt; 0 )
             GROUP BY A.EMPLY_SRRGT_ID , A.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_STRT_DT_KEY, A.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_END_DT_KEY
             HAVING ((COUNT(DISTINCT A.EMPLY_PRMRY_PRDCT_DPRTMNT_GRP_SRRGT_ID) &gt; 1)
                     AND (COUNT(DISTINCT A.EMPLY_PRMRY_BRND_SRRGT_ID) = 1)))
                     )
                     and ((E.EMPLY_MRCHNDS_GRP_NBR,E.EMPLY_PRMRY_BRND_SRRGT_ID) not in  (select 947,-1)
                     and (E.EMPLY_MRCHNDS_GRP_NBR,E.EMPLY_PRMRY_BRND_SRRGT_ID) not in  (select 952,-1)))
  ) X
  JOIN INS_EDW_CP.DT D1 
  ON D1.DT_KEY between X.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT_EFF_STRT_DT_KEY and X.EFF_END_DT_KEY
  JOIN INS_EDW_CP.DT_CLNDR_TYPE D2
  on ((D1.DT_KEY = D2.DT_KEY) AND (D2.CLNDR_TYPE = 1)))
  );
  </code></pre>