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>>

Attachments

Library

Quick Reference