All Forums Teradata Debugger
jhsun3 7 posts Joined 09/13
04 Apr 2016
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;
);

 

dnoeth 4628 posts Joined 11/04
05 Apr 2016

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)
 

Dieter

jhsun3 7 posts Joined 09/13
05 Apr 2016

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

You must sign in to leave a comment.