table is not specified in the FROM clause or already aliased by another name error msg when using a variable from VT

Teradata Debugger
Enthusiast

table is not specified in the FROM clause or already aliased by another name error msg when using a variable from VT

Hi there,

           I was developing VBA codes b4 being dragged in to do TeraData now (well, unfortunately no training provided). I have following code, if I run the single SELECT statement with Rpt_Dt.Dt_1 variable from VT table, it is running smoothly. However, when run the whole code with multiple join statements, it gives me "Table Rpt_Dt is not specified in the FROM clause or already aliased by another name." error msg.. I can't work out exactly what happened, anyone can help me??? Many thanx.

CREATE VOLATILE TABLE Rpt_Dt AS
(
SELECT '10/04/2016' AS Dt_1
, '2016/04/10' AS Dt_2
, '2016-04-10' AS Dt_3
, '160410' AS Dt_4
)
WITH DATA PRIMARY INDEX (Dt_1)
ON
COMMIT PRESERVE ROWS;

DROP TABLE Rpt_Dt;

EXEC Statement_001('10/04/2016' );

SELECT
A.TFN,
A.CAS_AUDT_ID,
A.INCM_YR,
A.updt_num,
-- from ICP
ZEROIFNULL(Penalty.Amt) AS ChangeinPenalty, --A.CHANGEINPENALTY,
ZEROIFNULL(Interest.Amt) AS ChangeinInterest,--A.CHANGEININTEREST,
A.PRODUCTIVE,
A.NATCODE,
A.FINALISED_DT,
A.Asmt_Prcsd_Dt,
A.Asmt_Iss_Dt
-- sel *
FROM
PTPROD.CASPAC_Results AS A
LEFT OUTER JOIN
(
SELECT
ZEROIFNULL(Clnt_Id.Clnt_Id_Val_Id) AS TFN
, EXTRACT(YEAR FROM F_Tx.Acnt_Perd_End_Dt) AS Incm_Yr
, SUM(F_Tx.Totl_Lblty_Efct_Amt) AS Amt
, F_Tx.Asmt_Num

FROM
EPGnrlDMV.Fact_Fincl_Tx AS F_Tx
INNER JOIN
(
SELECT Tx_Typ_Cd, Tx_Typ_Dcd
FROM
EPIntractnV.Tx_Typ AS T
WHERE
T.Tx_Typ_TVD_Crnt_Ind = 'Y'
AND
DATE BETWEEN T.Tx_Typ_TVD_Efctv_Strt_Dt
AND T.Tx_Typ_TVD_Efctv_End_Dt
) AS Tx_Dcd
ON F_Tx.Fincl_Tx_Typ_Cd = Tx_Dcd.Tx_Typ_Cd

-- to get TFN
INNER JOIN EPCLntV.Clnt_Id AS Clnt_Id
ON F_Tx.Clnt_Intrnl_Id = Clnt_Id.Clnt_Intrnl_Id
AND Clnt_Id.Clnt_Id_End_Dt > CURRENT_DATE
AND Clnt_Id.Clnt_Id_Sts_Cd = 5 /*active*/
AND Clnt_Id.Clnt_Id_Cmprmsd_Ind = 'N'
AND Clnt_Id.Clnt_Id_Typ_Cd = 5 /*TFN*/

WHERE
F_TX.Rl_Typ_Cd IN (5) --Income Tax - Individual
AND (TFN, Incm_Yr, F_Tx.Asmt_Num) IN
(
SELECT TFN, Incm_Yr, Updt_Num
FROM PTPROD.CASPAC_Results
WHERE
Finalised_Dt >= DATE '2015-07-01'
AND Finalised_Dt LE Rpt_Dt.Dt_1 -- at EOM/EOY, when we might need an extra qualification for the CASPAC refresh Manual Update
AND Productive = 1
-- and natcode like '%IMS%'
)
AND F_Tx.Tx_Descn LIKE '%interest%'
GROUP BY 1, 2, 4
) AS Interest

ON A.TFN = Interest.TFN
AND A.Incm_Yr = Interest.Incm_Yr
AND A.Updt_num = Interest.Asmt_Num;
);
2 REPLIES
Senior Apprentice

Re: table is not specified in the FROM clause or already aliased by another name error msg when using a variable from VT

There seem to be different scoping/parsing rules for Subqueries, you need to add it to the FROM

            FROM PTPROD.CASPAC_Results CROSS JOIN Rpt_Dt
WHERE

Btw, have a look at Global and session level parameters in SQL (besides the QueryBand part)

Enthusiast

Re: table is not specified in the FROM clause or already aliased by another name error msg when using a variable from VT

thanx Dieter.. seems working, even though, it seems taking extra running time to cross join two tables. but works.. :)