0

I'm converting this query from Netezza to run with my RedShift dw. I'm continuously getting this error.

Amazon Invalid operation: This type of IN/NOT IN query is not supported yet;

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?

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 > 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) > 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 )<>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 > 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) > 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)))
);
Joe Harris
  • 13,671
  • 4
  • 47
  • 54
Aneela Saleem Ramzan
  • 127
  • 1
  • 8
  • 15
  • I think you need to convert your `ANY` and `ALL` clauses to ANSI SQL joins (probably LEFT OUTER JOINs). I can't readily convert the SQL for you without understanding the domain. You also have a number of syntax issues with trailing commas. – Joe Harris Feb 25 '19 at 14:56
  • Hi Joe, Thanks for the response. I found the problem. The problem is with ANY operator. If i apply it with one key i.e., E.EMPLY_SRRGT_ID, then it works fine. But since the comparison is made with three keys, it gives error because INS_EDW_CP.EMPLY_PRMRY_BRND_PRDCT_DPRTMNT table is empty and nested query also gives empty result. So how should i handle this EMPTY stuff? – Aneela Saleem Ramzan Feb 26 '19 at 06:09
  • The problem is with HAVING ((COUNT(DISTINCT A.EMPLY_PRMRY_PRDCT_DPRTMNT_GRP_SRRGT_ID) > 1) AND (COUNT(DISTINCT A.EMPLY_PRMRY_BRND_SRRGT_ID) = 1)) part, i don't know what's going on. When i comment this out, it works fine, otherwise gives the error "This type of IN/NOT IN query is not supported yet;" – Aneela Saleem Ramzan Feb 26 '19 at 11:49

1 Answers1

0

You have constant values in where clause, you don't need exists clause.

Can you try below -

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 > 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) > 1)
                   AND (COUNT(DISTINCT A.EMPLY_PRMRY_BRND_SRRGT_ID) = 1))))
                  or ((E.EMPLY_MRCHNDS_GRP_NBR , E.EMPLY_PRMRY_BRND_SRRGT_ID)  in  ( 947,-1)
                   or  (E.EMPLY_MRCHNDS_GRP_NBR , E.EMPLY_PRMRY_BRND_SRRGT_ID) in  ( 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 )<>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 > 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) > 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  ( 947,-1)
                   and (E.EMPLY_MRCHNDS_GRP_NBR,E.EMPLY_PRMRY_BRND_SRRGT_ID) not in  ( 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)))
);
Vijiy
  • 1,187
  • 6
  • 21