TERADATA, CASE INTO WHERE CLAUSE

Question

I'd need help on this matter. my where condition does not get accepted ... I know that is tricky the case in the where clause, so i think you could let me out... I created a field into a subquery with a over partition by, which then i bring into the main select ... then I'd need to apply the filter you see below... it returns me an error saying that B does not exist even tough if I just write B.CC = 1 then it gives me result... any ideas? Thanks in advance

SELECT 
    B.*

FROM

    (
    SELECT 
        A.*,
        (Count(A.COD_ABI) Over (PARTITION BY A.COD_ABI, A.COD_KTO)) AS CC   

    FROM (
    SELECT DISTINCT
        T2.COD_PRODT_SALDO, 
        T2.COD_RESID_NPE, 
        T2.COD_DIVISA_UIC, 
        T2.COD_ABI, 
        T2.COD_NDG, 
        T2.COD_KTO, 
        T2.COD_PAESE_UIC_NPE, 
        T2.DAT_SCA, 
        T2.DAT_ACC, 
        T2.DAT_EST
    FROM
        (
        SELECT 
            T1.COD_PRODT_SALDO
           ,T1.COD_RESID_NPE
           ,T1.COD_DIVISA_UIC
           ,T1.COD_ABI
           ,T1.COD_NDG
           ,'00753' ||T1.COD_PRODT_SALDO||T1.COD_CNTRT_SALDO AS COD_KTO
           ,T1.COD_CONTRATTO_SAL
           ,T1.COD_RIFER_ANNO
           ,T1.COD_RIFER_MESE
           ,T1.COD_RIFER_ANNO || T1.COD_RIFER_MESE AS COD_RIFER
           ,T1.COD_CONTB_ETR
           ,T1.DAT_EST
           ,T1.DAT_ACC
           ,T1.DAT_SCA
           ,T1.COD_PAESE_UIC_NPE

        FROM ES777A.VA_ES_DB_ANAGR_CONTO AS T1,
             ES777A.VE_BFD_PDC AS T2
            WHERE  T1.TMS_INIZIO_VALIDITA <= T2.TMS_PDC
            AND T1.TMS_FINE_VALIDITA > T2.TMS_PDC
          AND T1.TMS_CANC_FISICA IS NULL 
          AND T1.FLG_RIFACIMENTO = 0
          AND T1.COD_ABI = T2.COD_ABI
          AND T2.NOM_VISTA='VA_ES_DB_ANAGR_CONTO'
          AND T2.NUM_PERIO_RIF = 20200131
          AND T2.COD_PERIODICITA = 'G'
          AND T1.COD_PRODT_SALDO NOT IN ('1398' , '1698') 
          AND T1.COD_PRODT_SALDO IN ('1801', '1803', '1901', '1903', '3301', '3304', '3311', '3401', '3411', '3421')
        )
        AS T2

    INNER JOIN 
        (
        SELECT
            T1.COD_ABI,
            '00753'||T1.COD_PRODT_SALDO||T1.COD_CNTRT_SALDO AS COD_KTO,
             Max(T1.COD_RIFER_ANNO || COD_RIFER_MESE) AS MAX_COD_RIFER

        FROM ES777A.VA_ES_DB_ANAGR_CONTO AS T1,
             ES777A.VE_BFD_PDC AS T2
        WHERE  
            T1.TMS_INIZIO_VALIDITA <= T2.TMS_PDC
            AND T1.TMS_FINE_VALIDITA > T2.TMS_PDC
            AND T1.TMS_CANC_FISICA IS NULL 
            AND T1.FLG_RIFACIMENTO = 0
            AND T1.COD_ABI = T2.COD_ABI
            AND T2.NOM_VISTA = 'VA_ES_DB_ANAGR_CONTO'
            AND T2.NUM_PERIO_RIF = 20200131
            AND T2.COD_PERIODICITA = 'G'
            AND T1.COD_PRODT_SALDO NOT IN ('1398' , '1698')
            AND T1.COD_PRODT_SALDO IN ('1801', '1803', '1901', '1903', '3301', '3304', '3311', '3401', '3411', '3421') 
        GROUP BY T1.COD_PRODT_SALDO,T1.COD_ABI,T1.COD_CNTRT_SALDO 
        )

        AS T1
    ON ( T2.COD_ABI = T1.COD_ABI AND T2.COD_KTO = T1.COD_KTO AND T2.COD_RIFER = T1.MAX_COD_RIFER )
    WHERE
          ( T2.DAT_EST > '2019-11-02' OR T2.DAT_EST IS NULL ) -- -90GG 
      AND ( T2.DAT_SCA > '2019-11-02' OR T2.DAT_SCA IS NULL ) -- -90GG

      )
      A

    )
    B

    WHERE b.cc = 

                WHEN (B.CC > 1 AND B.DAT_EST IS NOT NULL) THEN 1
                WHEN (B.CC > 1 AND B.DAT_EST IS NULL) THEN 0
                WHEN (B.CC = 1) THEN 1 ELSE 0
            END 

Answer 1

This answers the original version of the question.

In Teradata, you can simplify the logic to:

SELECT ...,
       Count(A.COD_ABI) Over (PARTITION BY A.COD_ABI, A.COD_KTO) AS CC  
FROM A 
QUALIFY CC > 1 AND DATE_EST IS NOT NULL;

All the subqueries are unnecessary