Replacing subquery in Snowflake

Question

I am new to Snowflake SQL and trying to migrate a code from Oracle SQL to Snowflake SQL.

I am using a subquery in Oracle which is not supported in Snowflake and it gives the following err-r

SQL compilation error: Unsupported subquery type cannot be evaluated.

Original SQL Query -

select p."EMPLOYEE#" as Employee_ID,
 (select o."POSITION_NO" from ah_occupancy o where o."EMPLOYEE_NO" = p."EMPLOYEE#" and o."JOB_NO" = p."JOB#" and p."WORKDATE" between o."PORTION_START" and o."PORTION_END") as Position_ID,
    j."COMPANY_CODE",
    date(p."WORKDATE") as Work_Date,
    p."UNIT" as Calculated_Quantity,
    p."PAYCODE"
from
    "ODS"."HRIS"."PEPAYTRAN" p, job j
where p."EMPLOYEE#" =j."EMPLOYEE#"
    and p."JOB#" = j."JOB#"
    and date( p."WORKDATE") between '2021-02-04' and '2021-02-10'-->='11-FEB-2021'
    and p."ORIGIN" not in ('RC101','FC801','WK8276')
    and p."PAYCODE" not in ('GPPL', 'CLAMS')
    and p."PAYCODE" not like 'JK%'
    and p."TP" >= '01-JAN-2021'
    and nvl(p."BATCH#",' ') not in ('MUHTSL','MUHTS','ICWTS','RDOAC','NU011','1')) ;

I reframed the query and it compiles and gives the same number of records, can anyone review the code and comment if its the same or if its in correct, kindly comment on same.

select p."EMPLOYEE#" as Employee_ID,
    o.position_no as Position_ID,
    j."COMPANY_CODE",
    date(p."WORKDATE") as Work_Date,
    p."UNIT" as Calculated_Quantity,
    p."PAYCODE"
from
    "ODS"."HRIS"."PEPAYTRAN" p,  "ODS"."HRIS"."JOB" j, "ODS"."HRIS"."AH_OCCUPANCY" o
where p."EMPLOYEE#" =j."EMPLOYEE#"
    and p."JOB#" = j."JOB#"
    and date( p."WORKDATE") between '2021-02-04' and '2021-02-10'-->='11-FEB-2021'
    and p."ORIGIN" not in ('RC101','FC801','WK8276')
    and p."PAYCODE" not in ('GPPL', 'CLAMS')
    and p."PAYCODE" not like 'JK%'
    and p."TP" >= '01-JAN-2021'
    and nvl(p."BATCH#",' ') not in ('MUHTSL','MUHTS','ICWTS','RDOAC','NU011','1')
    and o."EMPLOYEE_NO" = p."EMPLOYEE#" and o."JOB_NO" = p."JOB#" and p."WORKDATE" between o."PORTION_START" and o."PORTION_END"

If your new version of the query runs and produces the correct result, what problem are you facing and question are you asking? — NickW
I want someone to review both the codes and check whether technically the same. — Vijay
If they both produce the same result then they are technically the same — NickW

lmartinez lmartinez · Accepted Answer · 2021-05-28T05:36:44

Both queries are equivalent. You can check that both return the same information by running the following query:

--original query
select 
   p."EMPLOYEE#" as Employee_ID,
   (select 
      o."POSITION_NO" 
   from 
      ah_occupancy o 
   where 
      o."EMPLOYEE_NO" = p."EMPLOYEE#" 
      and o."JOB_NO" = p."JOB#" 
      and p."WORKDATE" between o."PORTION_START" and o."PORTION_END"
   ) as Position_ID,
   j."COMPANY_CODE",
   date(p."WORKDATE") as Work_Date,
   p."UNIT" as Calculated_Quantity,
   p."PAYCODE"
from
    "ODS"."HRIS"."PEPAYTRAN" p, 
    job j
where 
   p."EMPLOYEE#" =j."EMPLOYEE#"
   and p."JOB#" = j."JOB#"
   and date( p."WORKDATE") between '2021-02-04' and '2021-02-10'-->='11-FEB-2021'
   and p."ORIGIN" not in ('RC101','FC801','WK8276')
   and p."PAYCODE" not in ('GPPL', 'CLAMS')
   and p."PAYCODE" not like 'JK%'
   and p."TP" >= '01-JAN-2021'
   and nvl(p."BATCH#",' ') not in ('MUHTSL','MUHTS','ICWTS','RDOAC','NU011','1')) ;
minus
--new query
select 
   p."EMPLOYEE#" as Employee_ID,
   o.position_no as Position_ID,
   j."COMPANY_CODE",
   date(p."WORKDATE") as Work_Date,
   p."UNIT" as Calculated_Quantity,
   p."PAYCODE"
from
    "ODS"."HRIS"."PEPAYTRAN" p,  
    "ODS"."HRIS"."JOB" j, 
    "ODS"."HRIS"."AH_OCCUPANCY" o
where 
   p."EMPLOYEE#" =j."EMPLOYEE#"
   and p."JOB#" = j."JOB#"
   and date( p."WORKDATE") between '2021-02-04' and '2021-02-10'-->='11-FEB-2021'
   and p."ORIGIN" not in ('RC101','FC801','WK8276')
   and p."PAYCODE" not in ('GPPL', 'CLAMS')
   and p."PAYCODE" not like 'JK%'
   and p."TP" >= '01-JAN-2021'
   and nvl(p."BATCH#",' ') not in ('MUHTSL','MUHTS','ICWTS','RDOAC','NU011','1')
   and o."EMPLOYEE_NO" = p."EMPLOYEE#" 
   and o."JOB_NO" = p."JOB#" 
   and p."WORKDATE" between o."PORTION_START" and o."PORTION_END"

You can also try the following query which is also equivalent:

select 
   p."EMPLOYEE#" as Employee_ID,
   o.position_no as Position_ID,
   j."COMPANY_CODE",
   date(p."WORKDATE") as Work_Date,
   p."UNIT" as Calculated_Quantity,
   p."PAYCODE"
from
    "ODS"."HRIS"."PEPAYTRAN" p
    inner join  "ODS"."HRIS"."JOB" j
       on (p."EMPLOYEE#" =j."EMPLOYEE#"
           and p."JOB#" = j."JOB#")
    inner join "ODS"."HRIS"."AH_OCCUPANCY" o
       on (o."EMPLOYEE_NO" = p."EMPLOYEE#" 
           and o."JOB_NO" = p."JOB#" 
           and p."WORKDATE" between o."PORTION_START" and o."PORTION_END"  )
where 
   date( p."WORKDATE") between '2021-02-04' and '2021-02-10'-->='11-FEB-2021'
   and p."ORIGIN" not in ('RC101','FC801','WK8276')
   and p."PAYCODE" not in ('GPPL', 'CLAMS')
   and p."PAYCODE" not like 'JK%'
   and p."TP" >= '01-JAN-2021'
   and nvl(p."BATCH#",' ') not in ('MUHTSL','MUHTS','ICWTS','RDOAC','NU011','1')

Replacing subquery in Snowflake

1 Answers