Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 26 Sep 2011 @ 09:04:58 GMT


     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 


Subj:   Converting Scalar subqeries from Oracle to
 
From:   Anomy Anom

<-- Anonymously Posted: Thursday, September 22, 2011 21:50 -->

Hi,

I recently recieved a sql written in Oracle to be transformed into Teradata syntax. The query used scalar subqueries which are not supported in the version of Teradata (v 12) that we are using.

However, I did some research and re-wrote the sql in TD by converting the scalar subquery into derived tables and connected to the existing(main/outer) query by Left Outer Joins. The situation got complicated since the scalar subquery consisted of aggregate functions. In the TD version, I had to perform group by operations in the dervied tables and then join the columns back to the main/outer query. Is this the right approach to convert scalar subqueries to work in TD ?

I am posting the original version of the query written in Oracle below:


Oracle :

     SELECT
     "PRIORITY_DEP"."SPECIALTY_DEP_C",
     "USR_ST_USR"."METRIC_ID",
     "PRIORITY_SER"."PROV_NAME",
     "AC_CFG"."METRIC_DESC",
     "USR_ST_USR"."LAST_UPDATE_DATE",
     (

     select (
      (

          sum(
             case
               when ac.FORMAT_TYPE_C = 1 then
                 case
                  when us.METRIC_TOT_POS=0 then 0
                  when (CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC))
                           * (1/(.01*ac.GOAL)) < 0 then 0
                  when (CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC))
                           * (1/(.01*ac.GOAL)) > 1 then 100*m.METRIC_WEIGHT
                  else 100*m.METRIC_WEIGHT*(CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC))
                           * (1/(.01*ac.GOAL))
                 end
               when ac.FORMAT_TYPE_C = 2 then
                 case
                  when us.METRIC_VALUE IS NOT NULL and (1-(CAST(us.METRIC_VALUE as NUMERIC)
                           - (ac.GOAL))/(ac.GOAL)) <0 then 0
                  when us.METRIC_VALUE IS NOT NULL and (1-(CAST(us.METRIC_VALUE as NUMERIC)
                           - (ac.GOAL))/(ac.GOAL)) >1 then 100*m.METRIC_WEIGHT
                  when us.METRIC_VALUE IS NOT NULL then (1-(CAST(us.METRIC_VALUE as NUMERIC)
                           - (ac.GOAL))/(ac.GOAL))
                  when us.METRIC_TOT_POS=0 then 0
                  when 1-(CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC)
                           - (ac.GOAL))/(ac.GOAL) <0 then 0
                  when 1-(CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC)
                           - (ac.GOAL))/(ac.GOAL) >1 then 100*m.METRIC_WEIGHT
                  else 100*m.METRIC_WEIGHT*(1-(CAST(us.METRIC_TOT_ACHVD as NUMERIC)
                           / CAST(us.METRIC_TOT_POS as NUMERIC)-(ac.GOAL))/(ac.GOAL))
                 end
               else
                 case
         when us.METRIC_VALUE IS NOT NULL and us.METRIC_VALUE <0 then 0
         when us.METRIC_VALUE IS NOT NULL and us.METRIC_VALUE >100 then 100*m.METRIC_WEIGHT
         when us.METRIC_VALUE IS NOT NULL then m.METRIC_WEIGHT*us.METRIC_VALUE/(.01*ac.GOAL)
         when us.METRIC_TOT_POS=0 then 0
         when (CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC))
                   * (1/(.01*ac.GOAL)) < 0 then 0
         when (CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC))
                   * (1/(.01*ac.GOAL)) > 1 then 100*m.METRIC_WEIGHT
                  else 100*m.METRIC_WEIGHT*(CAST(us.METRIC_TOT_ACHVD as NUMERIC)
                            / CAST(us.METRIC_TOT_POS as NUMERIC))*(1/(.01*ac.GOAL))
        end
                end
             )
          )
         /(sum(m.METRIC_WEIGHT))

      )
     from USR_STAT us INNER JOIN METR_ENBL m
      on us.METRIC_ID = m.HDA_ID
      INNER JOIN AC_CFG ac
       on us.METRIC_ID = ac.ACUITY_SYSTEM_ID
     where us.STATISTIC_ID = "USER_ST_USR"."STATISTIC_ID"
     and us.CONTACT_DATE_REAL="USER_ST_USR"."CONTACT_DATE_REAL"
     and m.hide_in_pulse_yn ='N'
     )
      FROM
     "PUBLIC"."USR_STAT" "USR_STAT_usr" INNER JOIN "PUBLIC"."USR_STAT_DATA"
     "USR_STAT_DATA_user"
     ON "USR_STAT_usr"."STATISTIC_ID"="USR_STAT_DATA_user"."STATISTIC_ID" AND
     "USR_STAT_usr"."CONTACT_DATE"="USR_STAT_DATA_user"."LAST_UPDATE_DATE"
     INNER JOIN "PUBLIC"."AC_CFG" "AC_CFG"
     ON "USR_STAT_usr"."METRIC_ID"="AC_CFG"."ACUITY_SYSTEM_ID"
     INNER JOIN "PUBLIC"."METR_ENBL" "METR_ENBL" ON
     "AC_CFG"."ACUITY_SYSTEM_ID"="METR_ENBL"."HDA_ID"
     INNER JOIN "PUBLIC"."PRIORITY_EMP" "PRIORITY_EMP" ON
     "USR_STAT_DATA_user"."USER_REC_ID"="PRIORITY_EMP"."USER_ID"
     INNER JOIN "PUBLIC"."PRIORITY_SER" "PRIORITY_SER" ON
     "PRIORITY_EMP"."PROV_ID"="PRIORITY_SER"."PROV_ID"
     LEFT OUTER JOIN "PUBLIC"."PRIORITY_SER_DEPT" "PRIORITY_SER_DEPT"
     ON "PRIORITY_SER"."PROV_ID"="PRIORITY_SER_DEPT"."PROV_ID"
     LEFT OUTER JOIN "PUBLIC"."PRIORITY_DEP" "PRIORITY_DEP"
     ON "PRIORITY_SER_DEPT"."DEPARTMENT_ID"="PRIORITY_DEP"."DEPARTMENT_ID"
     LEFT OUTER JOIN "PUBLIC"."USR_STAT_DATA" "USR_STAT_DATA_dept"
     ON "PRIORITY_DEP"."DEPARTMENT_ID"="USR_STAT_DATA_dept"."DEPT_RECORD_ID"
     LEFT OUTER JOIN "PUBLIC"."USR_STAT_DATA" "USR_STAT_DATA_spec"
     ON "PRIORITY_DEP"."SPECIALTY_DEP_C"="USR_STAT_DATA_spec"."DEPT_SPEC_C"
     LEFT OUTER JOIN "PUBLIC"."ZC_PRIORITY_DEP" "ZC_PRIORITY_DEP"
     ON "PRIORITY_DEP"."SPECIALTY_DEP_C"="ZC_PRIORITY_DEP"."SPECIALTY_DEP_C"
     LEFT OUTER JOIN "PUBLIC"."USR_STAT" "USR_STAT_dept"
     ON "USR_STAT_DATA_dept"."STATISTIC_ID"="USR_STAT_dept"."STATISTIC_ID" AND
     "USR_STAT_DATA_dept"."LAST_UPDATE_DATE"="USR_STAT_dept"."CONTACT_DATE"
     LEFT OUTER JOIN "PUBLIC"."USR_STAT" "USR_STAT_spec"
     ON "USR_STAT_DATA_spec"."STATISTIC_ID"="USR_STAT_spec"."STATISTIC_ID" AND
     "USR_STAT_DATA_spec"."LAST_UPDATE_DATE"="USR_STAT_spec"."CONTACT_DATE"


Teradata:

SELECT "PRIORITY_DEP"."SPECIALTY_DEP_C", "USR_ST_USR"."METRIC_ID", "PRIORITY_SER"."PROV_NAME", "AC_CFG"."METRIC_DESC", "USR_ST_USR"."LAST_UPDATE_DATE", myquery.calc_val FROM "PUBLIC"."USR_STAT" "USR_STAT_usr" INNER JOIN "PUBLIC"."USR_STAT_DATA" "USR_STAT_DATA_user" ON "USR_STAT_usr"."STATISTIC_ID"="USR_STAT_DATA_user"."STATISTIC_ID" AND "USR_STAT_usr"."CONTACT_DATE"="USR_STAT_DATA_user"."LAST_UPDATE_DATE" INNER JOIN "PUBLIC"."AC_CFG" "AC_CFG" ON "USR_STAT_usr"."METRIC_ID"="AC_CFG"."ACUITY_SYSTEM_ID" INNER JOIN "PUBLIC"."METR_ENBL" "METR_ENBL" ON "AC_CFG"."ACUITY_SYSTEM_ID"="METR_ENBL"."HDA_ID" INNER JOIN "PUBLIC"."PRIORITY_EMP" "PRIORITY_EMP" ON "USR_STAT_DATA_user"."USER_REC_ID"="PRIORITY_EMP"."USER_ID" INNER JOIN "PUBLIC"."PRIORITY_SER" "PRIORITY_SER" ON "PRIORITY_EMP"."PROV_ID"="PRIORITY_SER"."PROV_ID" LEFT OUTER JOIN "PUBLIC"."PRIORITY_SER_DEPT" "PRIORITY_SER_DEPT" ON "PRIORITY_SER"."PROV_ID"="PRIORITY_SER_DEPT"."PROV_ID" LEFT OUTER JOIN "PUBLIC"."PRIORITY_DEP" "PRIORITY_DEP" ON "PRIORITY_SER_DEPT"."DEPARTMENT_ID"="PRIORITY_DEP"."DEPARTMENT_ID" LEFT OUTER JOIN "PUBLIC"."USR_STAT_DATA" "USR_STAT_DATA_dept" ON "PRIORITY_DEP"."DEPARTMENT_ID"="USR_STAT_DATA_dept"."DEPT_RECORD_ID" LEFT OUTER JOIN "PUBLIC"."USR_STAT_DATA" "USR_STAT_DATA_spec" ON "PRIORITY_DEP"."SPECIALTY_DEP_C"="USR_STAT_DATA_spec"."DEPT_SPEC_C" LEFT OUTER JOIN "PUBLIC"."ZC_PRIORITY_DEP" "ZC_PRIORITY_DEP" ON "PRIORITY_DEP"."SPECIALTY_DEP_C"="ZC_PRIORITY_DEP"."SPECIALTY_DEP_C" LEFT OUTER JOIN "PUBLIC"."USR_STAT" "USR_STAT_dept" ON "USR_STAT_DATA_dept"."STATISTIC_ID"="USR_STAT_dept"."STATISTIC_ID" AND "USR_STAT_DATA_dept"."LAST_UPDATE_DATE"="USR_STAT_dept"."CONTACT_DATE" LEFT OUTER JOIN "PUBLIC"."USR_STAT" "USR_STAT_spec" ON "USR_STAT_DATA_spec"."STATISTIC_ID"="USR_STAT_spec"."STATISTIC_ID" AND "USR_STAT_DATA_spec"."LAST_UPDATE_DATE"="USR_STAT_spec"."CONTACT_DATE" LEFT OUTER JOIN ( select us.STATISTIC_ID, us.contact_date_real, ( sum( case when ac.FORMAT_TYPE_C = 1 then case when us.METRIC_TOT_POS=0 then 0 when (CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC))*(1/(.01*ac.GOAL)) < 0 then 0 when (CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC))*(1/(.01*ac.GOAL)) > 1 then 100*m.METRIC_WEIGHT else 100*m.METRIC_WEIGHT*(CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC))*(1/(.01*ac.GOAL)) end when ac.FORMAT_TYPE_C = 2 then case when us.METRIC_VALUE IS NOT NULL and (1-(CAST(us.METRIC_VALUE as NUMERIC)-(ac.GOAL))/(ac.GOAL)) <0 then 0 when us.METRIC_VALUE IS NOT NULL and (1- (CAST(us.METRIC_VALUE as NUMERIC)-(ac.GOAL))/(ac.GOAL)) >1 then 100*m.METRIC_WEIGHT when us.METRIC_VALUE IS NOT NULL then (1-(CAST(us.METRIC_VALUE as NUMERIC)-(ac.GOAL))/(ac.GOAL)) when us.METRIC_TOT_POS=0 then 0 when 1-(CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC)-(ac.GOAL))/(ac.GOAL) <0 then 0 when 1-(CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC)-(ac.GOAL))/(ac.GOAL) >1 then 100*m.METRIC_WEIGHT else 100*m.METRIC_WEIGHT*(1- (CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC)- (ac.GOAL))/(ac.GOAL)) end else case when us.METRIC_VALUE IS NOT NULL and us.METRIC_VALUE <0 then 0 when us.METRIC_VALUE IS NOT NULL and us.METRIC_VALUE >100 then 100*m.METRIC_WEIGHT when us.METRIC_VALUE IS NOT NULL then m.METRIC_WEIGHT*us.METRIC_VALUE/(.01*ac.GOAL) when us.METRIC_TOT_POS=0 then 0 when (CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC))*(1/(.01*ac.GOAL)) < 0 then 0 when (CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC))*(1/(.01*ac.GOAL)) > 1 then 100*m.METRIC_WEIGHT else 100*m.METRIC_WEIGHT*(CAST(us.METRIC_TOT_ACHVD as NUMERIC)/CAST(us.METRIC_TOT_POS as NUMERIC))*(1/(.01*ac.GOAL)) end end ) ) /(sum(m.METRIC_WEIGHT)) as calc_val from USR_STAT us INNER JOIN METR_ENBL m on us.METRIC_ID = m.HDA_ID INNER JOIN AC_CFG ac on us.METRIC_ID = ac.ACUITY_SYSTEM_ID where m.hide_in_pulse_yn ='N' group by 1,2 ) myquery ON myquery.STATISTIC_ID = "USER_ST_USR"."STATISTIC_ID" and myquery.CONTACT_DATE_REAL="USER_ST_USR"."CONTACT_DATE_REAL"

Can anyone please help me validate the sql.


Thanks much.



     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016