incorrect use of subquery error

Database
N/A

incorrect use of subquery error

Hi,

The below query is giving error while running in Teradat SQL Assistant.

Error- Incorrect use of subquery TGT_UPD_CNT

Query-

SELECT DERV2.LND_TBL_NM AS PERS_LND_TBL, DERV2.TGT_TBL_NM AS PERS_TGT_TBL,DERV2.TGT_INS_CNT AS PERS_TGT_CNT,DERV2.TGT_UPD_CNT AS PERS_UPD_CNT,DERV2.REJ_CNT AS PERS_REJ_CNT FROM

(

SELECT 

 CAST('L_SPR_SPCLT_HBS_SHPMT_EXTR' AS VARCHAR(50)) AS LND_TBL_NM

,CAST('T_SPR_SPCLT_HBS_SHPMT_EXTR' AS VARCHAR(50)) AS TGT_TBL_NM

,(SELECT COUNT(1) AS S_CNT FROM DWV_LD.V_SPR_SPCLT_HBS_SHPMT_EXTR) AS LND_CNT

 ,(SELECT COUNT(1)

  FROM

    (

        SELECT 'L_GD_CNT' AS COL1 FROM DWV_LD.V_SPR_SPCLT_HBS_SHPMT_EXTR

        WHERE ERR_IND='N'

        QUALIFY ROW_NUMBER() OVER ( PARTITION BY SRC_CD ,SHP_NO ,SHP_PATNO  ORDER BY COALESCE(UPD_DT,CRTE_DT) DESC ) = 1

    ) SUB) AS LND_GUD_CNT

,(SELECT COUNT(1) AS T_CNT FROM DWV_EDW.V_SPR_SPCLT_HBS_SHPMT_EXTR WHERE REC_HIST_GID 

  IN (SELECT DISTINCT REC_HIST_GID FROM DWV_LD.V_SPR_SPCLT_HBS_SHPMT_EXTR)) AS TGT_INS_CNT

,(SELECT COUNT(1) AS T_CNT FROM DWV_EDW.V_SPR_SPCLT_HBS_SHPMT_EXTR WHERE UPD_REC_HIST_GID 

  IN (SELECT DISTINCT REC_HIST_GID FROM DWV_LD.V_SPR_SPCLT_HBS_SHPMT_EXTR) AND UPD_REC_HIST_GID<>REC_HIST_GID) AS TGT_UPD_CNT

,CASE WHEN (TGT_INS_CNT+TGT_UPD_CNT) > 1 THEN (LND_GUD_CNT - (TGT_INS_CNT+TGT_UPD_CNT))

      ELSE 'TARGET TABLE NOT LOADED' END AS REJ_CNT

) DERV2

Pankaj

1 REPLY
Teradata Employee

Re: incorrect use of subquery error

Move the calculation of REJ_CNT from the DERV2 subquery to the outer query.

Also note that a CASE expression must return the same data type for all conditions (e.g. the numeric value will be implicitly converted to VARCHAR).