1

I have the following SQL expression:

SELECT SS_ITEM_SK AS POP_ITEM_SK 
FROM (SELECT SS_ITEM_SK 
FROM (SELECT SS_ITEM_SK,(ITEM_SOLD-ITEM_RETURNED) AS TOT_SOLD_QTY FROM (SELECT SS_ITEM_SK,COUNT(SS_ITEM_SK) AS ITEM_SOLD,COUNT(SR_ITEM_SK) AS ITEM_RETURNED FROM STORE_SALES1 right outer join STORE_RETURNS1 on SS_TICKET_NUMBER = SR_TICKET_NUMBER AND SS_ITEM_SK = SR_ITEM_SK GROUP BY SS_ITEM_SK)))  

INTERSECT 

SELECT CS_ITEM_SK AS POP_ITEM_SK FROM (SELECT CS_ITEM_SK
FROM (SELECT CS_ITEM_SK,(ITEM_SOLD-ITEM_RETURNED) AS TOT_SOLD_QTY FROM (SELECT CS_ITEM_SK,COUNT(CS_ITEM_SK) AS ITEM_SOLD,COUNT(CR_ITEM_SK) AS ITEM_RETURNED FROM CATALOG_SALES1 right outer join CATALOG_RETURNS1 on CS_ORDER_NUMBER = CR_ORDER_NUMBER and CS_ITEM_SK = CR_ITEM_SK  GROUP BY CS_ITEM_SK))) 

INTERSECT 

SELECT WS_ITEM_SK AS POP_ITEM_SK FROM (SELECT WS_ITEM_SK
FROM (SELECT WS_ITEM_SK,(ITEM_SOLD-ITEM_RETURNED) AS TOT_SOLD_QTY FROM (SELECT WS_ITEM_SK,COUNT(WS_ITEM_SK) AS ITEM_SOLD,COUNT(WR_ITEM_SK) AS ITEM_RETURNED FROM WEB_SALES1 right outer join WEB_RETURNS1 on WS_ORDER_NUMBER = WR_ORDER_NUMBER AND WS_ITEM_SK = WR_ITEM_SK GROUP BY WS_ITEM_SK)))

Apache phoenix is not supporting the keyword INTERSECT. Can somebody please help me to correct above query without using INTERSECT?

Anish Nair
  • 79
  • 2
  • 11
Khush
  • 13
  • 1
  • 4

1 Answers1

0

I think there are multiple ways you can do this:

  1. Join Method

    select * from ((query1 inner join query2 on column_names) inner join query3 on column_names)

  2. Exists Method

    (query1 where exists (query2 where exists (query3)) )

  3. In Method

    (query1 where column_name in (query2 where column_name in (query3)) )

References: https://blog.jooq.org/2015/10/06/you-probably-dont-use-sql-intersect-or-except-often-enough/ and http://phoenix.apache.org/subqueries.html

Although I would use the exists/in over the join since if these queries return huge data then you might have to optimize your queries using this: https://phoenix.apache.org/joins.html

Anish Nair
  • 79
  • 2
  • 11
  • Thanks a lot got immediate response , I tried second option using exists but getting NullPointerException in phoenix. Can u please help on this. – Khush Jan 30 '20 at 05:49
  • hmmmm, are we sure that the third query actually ran? Why don't you split out all the three queries and make sure that the third query ran. Also if possible could you give me your create table statements so I can test the exact thing you are running and give you the exact command you would need – Anish Nair Jan 30 '20 at 20:51
  • Please let me know your mail id ? i will send u scripts directly. – Khush Feb 03 '20 at 08:46
  • nishair05@gmail.com – Anish Nair Feb 03 '20 at 15:46