topic Re: Create Violate Tera table from SAS in SAS Programming

Create Volatile Teradata table from SAS

Ronein — Mon, 08 Jan 2024 14:35:01 GMT

Hello

I run query in SAS that work on TeraData tables.

The query has two parts:

Step1- Create table t_dates (Based on data from Tera table VBM5732_AGR_DELAYED_PAYMENTS)

Step2-Create table Want (Based on data from Tera table VBM5732_AGR_DELAYED_PAYMENTS and t_dates table)

The current code create SAS data set t_dates in Step1 and then get error in step 2 because it mixed merge of SAS data set with Tera tables.

My question-

How should I run the code in order to create Tera Violate table in step 1 and then use this table in step2.

Please see the libname code for teradata.

May anyone show the full code that perform the desired task?

libname RON teradata server=dwprod schema=DWP_vall authdomain=TeraDataAuth CONNECTION=GLOBAL  dbmstemp=yes ;
 
proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table t_dates   as 
select * from connection to teradata
(
SELECT (CAST('01/01/2024' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE ,
        Max(PROCESS_DATE) as End_Date 
FROM   V797_PROCESS_DATE
);
disconnect from teradata;
quit ;

proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table want  as 
select * from connection to teradata
(
SELECT  a.*
FROM VBM5732_AGR_DELAYED_PAYMENTS  AS a 
Where AS52_TAR_DHIA BETWEEN(SEL START_DATE FROM t_dates)  AND (SEL END_DATE FROM t_dates)
);
disconnect from teradata;
quit ;

Re: Create Violate Tera table from SAS

Kurt_Bremser — Mon, 08 Jan 2024 11:49:22 GMT

This cannot work. A volatile table is automatically removed when a session ends (similar to WORK in SAS), and each CONNECT represents a separate session. You must create and use the table while the same connection within a single PROC SQL is active.

Re: Create Violate Tera table from SAS

Patrick — Mon, 08 Jan 2024 12:12:21 GMT

Based on one of your other posts there is already a pre-assigned library teradata.

Now looking into the code you shared here why bother with creating a volatile table? The untested code below should do the same job.

proc sql;
  connect using TeraData;  

  create table want  as 
  select * 
  from connection to teradata
  (
    SELECT  a.*
    FROM 
    VBM5732_AGR_DELAYED_PAYMENTS AS a 
    inner join 
    (
      SELECT (CAST('01/01/2024' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE ,
      Max(PROCESS_DATE) as End_Date 
      FROM V797_PROCESS_DATE
    ) as b
    on a.AS52_TAR_DHIA between b.START_DATE and b.End_Date
  )
  ;
quit;

Re: Create Violate Tera table from SAS

Ronein — Mon, 08 Jan 2024 12:14:24 GMT

Can you please show the full code and I will run it and try if it works.

Here is the code I try to run now and got error

 proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);


create table t_dates   as 
select * from connection to teradata
(
SELECT (CAST('01/01/2024' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE ,
        Max(PROCESS_DATE) as End_Date 
FROM   V797_PROCESS_DATE
);


create table want  as 
select * from connection to teradata
(
SELECT  a.*
FROM VBM5732_AGR_DELAYED_PAYMENTS  AS a 
Where AS52_TAR_DHIA BETWEEN(SEL START_DATE FROM t_dates)  AND (SEL END_DATE FROM t_dates)
);

disconnect from teradata;
quit ;

Error in Log

ERROR: Teradata prepare: Object 't_dates' does not exist. SQL statement was: SELECT a.* FROM VBM5732_AGR_DELAYED_PAYMENTS AS a 
       Where AS52_TAR_DHIA BETWEEN(SEL START_DATE FROM t_dates) AND (SEL END_DATE FROM t_dates).

Re: Create Violate Tera table from SAS

Patrick — Mon, 08 Jan 2024 12:21:01 GMT

@Ronein Forget about the volatile table for your use case. You can have this easier.

I already posted one option, here a 2nd one.

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc sql;
  select put(max(process_date),date9.) into :max_process_date trimmed
  from teradata.V797_PROCESS_DATE
  ;
quit;

proc sql;
  create table want as
  select *
  from teradata.VBM5732_AGR_DELAYED_PAYMENTS
  where AS52_TAR_DHIA between "01jan2024"d and "&max_process_date"d
  ;
quit;

Re: Create Violate Tera table from SAS

Ronein — Mon, 08 Jan 2024 12:22:52 GMT

Your code is working 100% , however I want to learn to do it in the following way:

Create violate table t_dates and then create violate table VBM376_INTERNAL_ORGANIZATION.

In real word there is a query that run on tera and I want to run it on SAS.

This query is long and during this process there are 3 violate tables that are created.

My question- How to run this long query in SAS?




CREATE VOLATILE TABLE  VT5727_DATES_M  AS (
SELECT Max(PROCESS_DATE )   AS END_DATE  
,(CAST('01/10/2023' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE 
,END_DATE/ 100 + 190000 AS MONTH_REPORTED        
FROM   V797_PROCESS_DATE  
)WITH DATA ON COMMIT PRESERVE ROWS; 
 


CREATE VOLATILE TABLE VT5727_POP_LOANS_M AS (
SELECT DISTINCT
DATE AS Run_Date
,VBM374.first_Branch_Cust_IP as  Numerator_DWH 
,VBM374.Branch_Cust_IP
,VBM374.Current_Branch_Cust_IP
,VBM374.Branch_Cust_Nbr
,VBM374.Branch_Nbr
,AS52_SHIUCH_CHAT AS division_nbr    

,DHIA_TASH.AS52_MIS_CHODASHIM 
,DHIA_TASH.AS52_TEDIRUT_KEREN  
,DHIA_TASH.AS52_SUG_DHIA 

,CASE WHEN AS52_SUG_DHIA IN (0,1) THEN  Cast ( DHIA_TASH.AS52_SCHUM_DHIA AS DECIMAL(18,0)) *(V00X613.Exchange_Rate / V00X613.Exchange_Rate_Calc_Currency_Am) ELSE  ZeroIfNull(LOAN_BAL_PREV.Next_Payment_Amt_Nis )   END AS  SCHUM_DHIA 
,DHIA_TASH.AS52_TAR_DHIA 
,CASE WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V1' THEN SCHUM_DHIA* DHIA_TASH.AS52_MIS_CHODASHIM 
	WHEN DHIA_TASH.AS52_TEDIRUT_KEREN IN ('T1037V0','T1037V12') THEN SCHUM_DHIA 
	 WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V3' THEN SCHUM_DHIA* DHIA_TASH.AS52_MIS_CHODASHIM/3 
	 WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V6' THEN SCHUM_DHIA* DHIA_TASH.AS52_MIS_CHODASHIM/6 
	 WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V2' AND DHIA_TASH.AS52_MIS_CHODASHIM BETWEEN 3 AND 4 THEN SCHUM_DHIA * 2 
	  ELSE SCHUM_DHIA * DHIA_TASH.AS52_MIS_CHODASHIM/2  
	  END AS SCHUM_TASHLOMIM_NIDHO 
  
,DHIA_TASH.Curr_Agr_Id AS Agreement_Account_Id 
,VBM442.agreement_open_date 
,VBM442_CURR.Agreement_Due_Close_Date 
,VBM448_ACT_LOAN_AGR_RATE.Interest_Pct
,CASE WHEN DHIA_TASH.AS52_SUG_DHIA = '2' THEN  1 ELSE 0 end AS ind_mitveh_israel    
,VBM442.Loan_Model_Code 
,VBM442.Loan_Serial_Nbr 
,VBM442.loan_type_code 
,CASE WHEN Substr(V02X17_PRODUCT_CREDITS.Type_Of_Sector_Code,6,3)= '45' THEN 1 ELSE 0 END AS ind_house_loan 
,DHIA_TASH.AS52_TAR_KEREN_AFT
,DHIA_TASH.AS52_TAR_RIBIT_AFT

,Greatest(DHIA_TASH.AS52_TAR_KEREN_AFT,VBM452_ACT_LOAN_AGR_TERM.Fund_Next_Pmt_Date) AS END_DHIA_DATE
,CASE WHEN (DHIA_TASH.AS52_TAR_KEREN_AFT/ 100 + 190000) = (DHIA_TASH.AS52_TAR_RIBIT_AFT/ 100 + 190000)  THEN 1 ELSE 0 END AS FULL_DHIA
,DHIA_TASH.AS52_ITRA_MSHRCHT_ACHARON 
,CASE WHEN END_DHIA_DATE > (SEL END_DATE FROM VT5727_DATES_M) THEN 1 ELSE 0 end AS ind_still_dhia
,AS52_ITRA_MSHRCHT   
,CASE WHEN VBM451_ACT_LOAN_AGR_SYMBOL.Delayed_Deal_Code= 'T460V1' AND VBM410.Total_Delay_Bal_Amt <> 0 THEN 1 ELSE 0 end AS ind_Delayed_Deal

,Sum(ZeroIfNull(VBM410.Daily_Bal_Amt)+ZeroIfNull(VBM410.Fund_Linkage_Diff_Bal_Amt)+ZeroIfNull(VBM410.Temporary_Interest_Bal_Amt)+ZeroIfNull(VBM410.Temp_Int_Link_Diff_Bal_Amt)) AS ITRA_AHRONA
,Sum(ZeroIfNull(LOAN_BAL_DHIA.Next_Payment_Amt_Nis )) AS Payment_Amt_DHIA
,Sum(ZeroIfNull(LOAN_BAL_PREV.Next_Payment_Amt_Nis )) AS Payment_Amt_PREV 
,CASE WHEN Payment_Amt_DHIA = 0 AND Payment_Amt_PREV = 0 THEN 0 ELSE  Abs(CASE WHEN FULL_DHIA = 1 OR (Payment_Amt_PREV = 0 AND Payment_Amt_DHIA <> 0 ) THEN 100 ELSE (Payment_Amt_DHIA/Payment_Amt_PREV -1)*100 END) end AS reduce_rate
FROM 
VBM5732_AGR_DELAYED_PAYMENTS AS DHIA_TASH 
INNER JOIN VBM374_USED_BRANCH_CUSTOMER AS  VBM374
ON DHIA_TASH.Prev_Branch_Cust_IP = VBM374.Branch_Cust_IP
LEFT JOIN V01X997_IP_REGULATORY_ACTIVITY_SEGMENT  AS V01X997
ON (VBM374.Branch_Cust_IP = V01X997.Inv_Party_Id OR VBM374.Current_Branch_Cust_IP= V01X997.Inv_Party_Id) AND V01X997.Validity_Date = (SELECT Max(Validity_Date) FROM V01X997_IP_REGULATORY_ACTIVITY_SEGMENT) 
AND V01X997.Customer_Segment IN  (1000,2000,3110,3120,3200,3300)
INNER JOIN VBM442_AGREEMENTS AS VBM442
  ON (VBM442.Branch_Cust_IP=VBM374.Branch_Cust_IP AND DHIA_TASH.Prev_Agr_Id=VBM442.Agreement_Account_Id )
 LEFT JOIN V00X613_EXCHANGE_RATE AS V00X613
        ON V00X613.Exchange_Rate_Date = (SELECT Max(PROCESS_DATE ) FROM V797_PROCESS_DATE)
        AND AS52_MATBEA_ISKA =V00X613.Currency_Id
LEFT JOIN VBM451_ACT_LOAN_AGR_SYMBOL 
ON (VBM451_ACT_LOAN_AGR_SYMBOL.Agreement_Account_Id=VBM442.Agreement_Account_Id)
 LEFT JOIN VBM452_ACT_LOAN_AGR_TERM 
 ON (VBM452_ACT_LOAN_AGR_TERM.Agreement_Account_Id=VBM442.Agreement_Account_Id)
INNER JOIN VBM321_PRODUCTS
ON (VBM321_PRODUCTS.Product_Id=VBM442.Product_Id)
LEFT JOIN V02X17_PRODUCT_CREDITS
ON (V02X17_PRODUCT_CREDITS.Product_Id=VBM321_PRODUCTS.Product_Id AND VBM321_PRODUCTS.Subject_Code='AS')
LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS VBM410
ON VBM410.Agreement_Account_Id = DHIA_TASH.Curr_Agr_Id AND VBM410.Agreement_Summary_Date =(SEL END_DATE FROM VT5727_DATES_M)
INNER JOIN VBM442_AGREEMENTS AS VBM442_CURR 
  ON (VBM442_CURR.Branch_Cust_IP=VBM374.Current_Branch_Cust_IP AND DHIA_TASH.Curr_Agr_Id=VBM442_CURR.Agreement_Account_Id )
LEFT JOIN VBM448_ACT_LOAN_AGR_RATE 
ON VBM448_ACT_LOAN_AGR_RATE.Agreement_Account_Id=VBM442_CURR.Agreement_Account_Id 
LEFT JOIN 
		 (SELECT
		LOAN_BAL_PREV.Agreement_Account_Id,
		Max(LOAN_BAL_PREV.Agreement_Summary_Date ) AS max_date
		FROM 
		VBM5732_AGR_DELAYED_PAYMENTS AS DHIA_TASH
		INNER JOIN VBM442_AGREEMENTS AS VBM442
		  ON (DHIA_TASH.Prev_Agr_Id=VBM442.Agreement_Account_Id )
		LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS LOAN_BAL_PREV
		ON (VBM442.Agreement_Account_Id=LOAN_BAL_PREV.Agreement_Account_Id  )
		WHERE LOAN_BAL_PREV.Agreement_Summary_Date  < DHIA_TASH.AS52_TAR_DHIA
		AND DHIA_TASH.Validity_Date = (SELECT Max(Validity_Date) FROM VBM5732_AGR_DELAYED_PAYMENTS)
		AND AS52_TAR_DHIA BETWEEN 1231001 AND  (SEL END_DATE FROM VT5727_DATES_M)
		AND ((AS52_SHIUCH_CHAT NOT IN (833,835) AND AS52_MIS_CHODASHIM  BETWEEN 2 AND 24) OR (AS52_SHIUCH_CHAT IN (833,835) AND AS52_MIS_CHODASHIM>0))
		AND AS52_TAR_KEREN_AFT - AS52_TAR_KEREN_KDM  >0 
		GROUP BY 1) AS LOAN_max_date 
ON (VBM442.Agreement_Account_Id=LOAN_max_date.Agreement_Account_Id)
LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS LOAN_BAL_PREV
ON (LOAN_max_date.Agreement_Account_Id=LOAN_BAL_PREV.Agreement_Account_Id AND LOAN_BAL_PREV.Agreement_Summary_Date  = LOAN_max_date.max_date ) 
LEFT JOIN VA7836_HARAVOT_BARZEL_POP AS VA7836 
ON VA7836.Branch_Cust_IP = VBM374.Branch_Cust_IP
LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS LOAN_BAL_DHIA 
ON (VBM442.Agreement_Account_Id=LOAN_BAL_DHIA.Agreement_Account_Id AND LOAN_BAL_DHIA.Agreement_Summary_Date = DHIA_TASH.AS52_TAR_DHIA)
WHERE 
AS52_TAR_DHIA BETWEEN 1231001 AND  (SEL END_DATE FROM VT5727_DATES_M)
AND DHIA_TASH.Validity_Date = (SELECT Max(Validity_Date) FROM VBM5732_AGR_DELAYED_PAYMENTS)
AND ((AS52_SHIUCH_CHAT NOT IN (833,835)  AND AS52_MIS_CHODASHIM  BETWEEN 2 AND 24) OR (AS52_SHIUCH_CHAT IN (833,835) AND AS52_MIS_CHODASHIM>0))
AND AS52_TAR_KEREN_AFT - AS52_TAR_KEREN_KDM  >0 
AND VBM442.Agreement_Category_Code  =  3 
AND VBM374.Status_Code<> 'T295V51'
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
) WITH DATA PRIMARY INDEX (Current_Branch_Cust_IP,Branch_Cust_IP,Agreement_Account_Id,AS52_TAR_DHIA) ON COMMIT PRESERVE ROWS ;



CREATE VOLATILE TABLE VT5727_LOANS_PIGUR AS (
SELECT
  LOANS_M.Branch_Cust_IP,
  LOANS_M.Agreement_Account_Id,
  VBM442_AGREEMENTS.Agreement_Account_Id AS Agreement_Account_Id_pigur,
  VBM442_AGREEMENTS.Loan_Model_Code,
  VBM442_AGREEMENTS.Loan_Serial_Nbr,
  VBM442_AGREEMENTS.Loan_Type_Code,
  V03X30_LOAN_AGREEMENT.Loan_Value_Date AS pigur_date_ForInPigurToday
FROM
VT5727_POP_LOANS_M AS LOANS_M
INNER JOIN VBM442_AGREEMENTS
ON  (VBM442_AGREEMENTS.Branch_Cust_IP=LOANS_M.Branch_Cust_IP AND VBM442_AGREEMENTS.Loan_Model_Code = LOANS_M.Loan_Model_Code
     AND VBM442_AGREEMENTS.Loan_Serial_Nbr=LOANS_M.Loan_Serial_Nbr )
INNER JOIN V03X30_LOAN_AGREEMENT 
ON (V03X30_LOAN_AGREEMENT.Agreement_Account_Id=VBM442_AGREEMENTS.Agreement_Account_Id AND V03X30_LOAN_AGREEMENT.Loan_Value_Date>= LOANS_M.agreement_open_date)
WHERE
VBM442_AGREEMENTS.Agreement_Category_Code  =  3
AND VBM442_AGREEMENTS.Loan_Type_Code  IN  ( 11, 21, 22  )  
AND LOANS_M.ind_Delayed_Deal = 1  
) WITH DATA PRIMARY INDEX (Branch_Cust_IP,Agreement_Account_Id,Agreement_Account_Id_pigur) ON COMMIT PRESERVE ROWS ;

Re: Create Violate Tera table from SAS

Ronein — Mon, 08 Jan 2024 12:27:30 GMT

It is perfect but It cannot help me to solve my real problem.

My real problem is that I need to move code that run in Tera to run in SAS and this query have multiple steps where in each step create violtate table.

Here is the code that I need to run in SAS




CREATE VOLATILE TABLE  VT5727_DATES_M  AS (
SELECT Max(PROCESS_DATE )   AS END_DATE  
,(CAST('01/10/2023' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE 
,END_DATE/ 100 + 190000 AS MONTH_REPORTED        
FROM   V797_PROCESS_DATE  
)WITH DATA ON COMMIT PRESERVE ROWS; 
 


CREATE VOLATILE TABLE VT5727_POP_LOANS_M AS (
SELECT DISTINCT
DATE AS Run_Date
,VBM374.first_Branch_Cust_IP as  Numerator_DWH 
,VBM374.Branch_Cust_IP
,VBM374.Current_Branch_Cust_IP
,VBM374.Branch_Cust_Nbr
,VBM374.Branch_Nbr
,AS52_SHIUCH_CHAT AS division_nbr    

,DHIA_TASH.AS52_MIS_CHODASHIM 
,DHIA_TASH.AS52_TEDIRUT_KEREN  
,DHIA_TASH.AS52_SUG_DHIA 

,CASE WHEN AS52_SUG_DHIA IN (0,1) THEN  Cast ( DHIA_TASH.AS52_SCHUM_DHIA AS DECIMAL(18,0)) *(V00X613.Exchange_Rate / V00X613.Exchange_Rate_Calc_Currency_Am) ELSE  ZeroIfNull(LOAN_BAL_PREV.Next_Payment_Amt_Nis )   END AS  SCHUM_DHIA 
,DHIA_TASH.AS52_TAR_DHIA 
,CASE WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V1' THEN SCHUM_DHIA* DHIA_TASH.AS52_MIS_CHODASHIM 
	WHEN DHIA_TASH.AS52_TEDIRUT_KEREN IN ('T1037V0','T1037V12') THEN SCHUM_DHIA 
	 WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V3' THEN SCHUM_DHIA* DHIA_TASH.AS52_MIS_CHODASHIM/3 
	 WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V6' THEN SCHUM_DHIA* DHIA_TASH.AS52_MIS_CHODASHIM/6 
	 WHEN DHIA_TASH.AS52_TEDIRUT_KEREN = 'T1037V2' AND DHIA_TASH.AS52_MIS_CHODASHIM BETWEEN 3 AND 4 THEN SCHUM_DHIA * 2 
	  ELSE SCHUM_DHIA * DHIA_TASH.AS52_MIS_CHODASHIM/2  
	  END AS SCHUM_TASHLOMIM_NIDHO 
  
,DHIA_TASH.Curr_Agr_Id AS Agreement_Account_Id 
,VBM442.agreement_open_date 
,VBM442_CURR.Agreement_Due_Close_Date 
,VBM448_ACT_LOAN_AGR_RATE.Interest_Pct
,CASE WHEN DHIA_TASH.AS52_SUG_DHIA = '2' THEN  1 ELSE 0 end AS ind_mitveh_israel    
,VBM442.Loan_Model_Code 
,VBM442.Loan_Serial_Nbr 
,VBM442.loan_type_code 
,CASE WHEN Substr(V02X17_PRODUCT_CREDITS.Type_Of_Sector_Code,6,3)= '45' THEN 1 ELSE 0 END AS ind_house_loan 
,DHIA_TASH.AS52_TAR_KEREN_AFT
,DHIA_TASH.AS52_TAR_RIBIT_AFT

,Greatest(DHIA_TASH.AS52_TAR_KEREN_AFT,VBM452_ACT_LOAN_AGR_TERM.Fund_Next_Pmt_Date) AS END_DHIA_DATE
,CASE WHEN (DHIA_TASH.AS52_TAR_KEREN_AFT/ 100 + 190000) = (DHIA_TASH.AS52_TAR_RIBIT_AFT/ 100 + 190000)  THEN 1 ELSE 0 END AS FULL_DHIA
,DHIA_TASH.AS52_ITRA_MSHRCHT_ACHARON 
,CASE WHEN END_DHIA_DATE > (SEL END_DATE FROM VT5727_DATES_M) THEN 1 ELSE 0 end AS ind_still_dhia
,AS52_ITRA_MSHRCHT   
,CASE WHEN VBM451_ACT_LOAN_AGR_SYMBOL.Delayed_Deal_Code= 'T460V1' AND VBM410.Total_Delay_Bal_Amt <> 0 THEN 1 ELSE 0 end AS ind_Delayed_Deal

,Sum(ZeroIfNull(VBM410.Daily_Bal_Amt)+ZeroIfNull(VBM410.Fund_Linkage_Diff_Bal_Amt)+ZeroIfNull(VBM410.Temporary_Interest_Bal_Amt)+ZeroIfNull(VBM410.Temp_Int_Link_Diff_Bal_Amt)) AS ITRA_AHRONA
,Sum(ZeroIfNull(LOAN_BAL_DHIA.Next_Payment_Amt_Nis )) AS Payment_Amt_DHIA
,Sum(ZeroIfNull(LOAN_BAL_PREV.Next_Payment_Amt_Nis )) AS Payment_Amt_PREV 
,CASE WHEN Payment_Amt_DHIA = 0 AND Payment_Amt_PREV = 0 THEN 0 ELSE  Abs(CASE WHEN FULL_DHIA = 1 OR (Payment_Amt_PREV = 0 AND Payment_Amt_DHIA <> 0 ) THEN 100 ELSE (Payment_Amt_DHIA/Payment_Amt_PREV -1)*100 END) end AS reduce_rate
FROM 
VBM5732_AGR_DELAYED_PAYMENTS AS DHIA_TASH 
INNER JOIN VBM374_USED_BRANCH_CUSTOMER AS  VBM374
ON DHIA_TASH.Prev_Branch_Cust_IP = VBM374.Branch_Cust_IP
LEFT JOIN V01X997_IP_REGULATORY_ACTIVITY_SEGMENT  AS V01X997
ON (VBM374.Branch_Cust_IP = V01X997.Inv_Party_Id OR VBM374.Current_Branch_Cust_IP= V01X997.Inv_Party_Id) AND V01X997.Validity_Date = (SELECT Max(Validity_Date) FROM V01X997_IP_REGULATORY_ACTIVITY_SEGMENT) 
AND V01X997.Customer_Segment IN  (1000,2000,3110,3120,3200,3300)
INNER JOIN VBM442_AGREEMENTS AS VBM442
  ON (VBM442.Branch_Cust_IP=VBM374.Branch_Cust_IP AND DHIA_TASH.Prev_Agr_Id=VBM442.Agreement_Account_Id )
 LEFT JOIN V00X613_EXCHANGE_RATE AS V00X613
        ON V00X613.Exchange_Rate_Date = (SELECT Max(PROCESS_DATE ) FROM V797_PROCESS_DATE)
        AND AS52_MATBEA_ISKA =V00X613.Currency_Id
LEFT JOIN VBM451_ACT_LOAN_AGR_SYMBOL 
ON (VBM451_ACT_LOAN_AGR_SYMBOL.Agreement_Account_Id=VBM442.Agreement_Account_Id)
 LEFT JOIN VBM452_ACT_LOAN_AGR_TERM 
 ON (VBM452_ACT_LOAN_AGR_TERM.Agreement_Account_Id=VBM442.Agreement_Account_Id)
INNER JOIN VBM321_PRODUCTS
ON (VBM321_PRODUCTS.Product_Id=VBM442.Product_Id)
LEFT JOIN V02X17_PRODUCT_CREDITS
ON (V02X17_PRODUCT_CREDITS.Product_Id=VBM321_PRODUCTS.Product_Id AND VBM321_PRODUCTS.Subject_Code='AS')
LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS VBM410
ON VBM410.Agreement_Account_Id = DHIA_TASH.Curr_Agr_Id AND VBM410.Agreement_Summary_Date =(SEL END_DATE FROM VT5727_DATES_M)
INNER JOIN VBM442_AGREEMENTS AS VBM442_CURR 
  ON (VBM442_CURR.Branch_Cust_IP=VBM374.Current_Branch_Cust_IP AND DHIA_TASH.Curr_Agr_Id=VBM442_CURR.Agreement_Account_Id )
LEFT JOIN VBM448_ACT_LOAN_AGR_RATE 
ON VBM448_ACT_LOAN_AGR_RATE.Agreement_Account_Id=VBM442_CURR.Agreement_Account_Id 
LEFT JOIN 
		 (SELECT
		LOAN_BAL_PREV.Agreement_Account_Id,
		Max(LOAN_BAL_PREV.Agreement_Summary_Date ) AS max_date
		FROM 
		VBM5732_AGR_DELAYED_PAYMENTS AS DHIA_TASH
		INNER JOIN VBM442_AGREEMENTS AS VBM442
		  ON (DHIA_TASH.Prev_Agr_Id=VBM442.Agreement_Account_Id )
		LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS LOAN_BAL_PREV
		ON (VBM442.Agreement_Account_Id=LOAN_BAL_PREV.Agreement_Account_Id  )
		WHERE LOAN_BAL_PREV.Agreement_Summary_Date  < DHIA_TASH.AS52_TAR_DHIA
		AND DHIA_TASH.Validity_Date = (SELECT Max(Validity_Date) FROM VBM5732_AGR_DELAYED_PAYMENTS)
		AND AS52_TAR_DHIA BETWEEN 1231001 AND  (SEL END_DATE FROM VT5727_DATES_M)
		AND ((AS52_SHIUCH_CHAT NOT IN (833,835) AND AS52_MIS_CHODASHIM  BETWEEN 2 AND 24) OR (AS52_SHIUCH_CHAT IN (833,835) AND AS52_MIS_CHODASHIM>0))
		AND AS52_TAR_KEREN_AFT - AS52_TAR_KEREN_KDM  >0 
		GROUP BY 1) AS LOAN_max_date 
ON (VBM442.Agreement_Account_Id=LOAN_max_date.Agreement_Account_Id)
LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS LOAN_BAL_PREV
ON (LOAN_max_date.Agreement_Account_Id=LOAN_BAL_PREV.Agreement_Account_Id AND LOAN_BAL_PREV.Agreement_Summary_Date  = LOAN_max_date.max_date ) 
LEFT JOIN VA7836_HARAVOT_BARZEL_POP AS VA7836 
ON VA7836.Branch_Cust_IP = VBM374.Branch_Cust_IP
LEFT JOIN VBM410_ACTIVE_LOAN_BAL AS LOAN_BAL_DHIA 
ON (VBM442.Agreement_Account_Id=LOAN_BAL_DHIA.Agreement_Account_Id AND LOAN_BAL_DHIA.Agreement_Summary_Date = DHIA_TASH.AS52_TAR_DHIA)
WHERE 
AS52_TAR_DHIA BETWEEN 1231001 AND  (SEL END_DATE FROM VT5727_DATES_M)
AND DHIA_TASH.Validity_Date = (SELECT Max(Validity_Date) FROM VBM5732_AGR_DELAYED_PAYMENTS)
AND ((AS52_SHIUCH_CHAT NOT IN (833,835)  AND AS52_MIS_CHODASHIM  BETWEEN 2 AND 24) OR (AS52_SHIUCH_CHAT IN (833,835) AND AS52_MIS_CHODASHIM>0))
AND AS52_TAR_KEREN_AFT - AS52_TAR_KEREN_KDM  >0 
AND VBM442.Agreement_Category_Code  =  3 
AND VBM374.Status_Code<> 'T295V51'
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30
) WITH DATA PRIMARY INDEX (Current_Branch_Cust_IP,Branch_Cust_IP,Agreement_Account_Id,AS52_TAR_DHIA) ON COMMIT PRESERVE ROWS ;



CREATE VOLATILE TABLE VT5727_LOANS_PIGUR AS (
SELECT
  LOANS_M.Branch_Cust_IP,
  LOANS_M.Agreement_Account_Id,
  VBM442_AGREEMENTS.Agreement_Account_Id AS Agreement_Account_Id_pigur,
  VBM442_AGREEMENTS.Loan_Model_Code,
  VBM442_AGREEMENTS.Loan_Serial_Nbr,
  VBM442_AGREEMENTS.Loan_Type_Code,
  V03X30_LOAN_AGREEMENT.Loan_Value_Date AS pigur_date_ForInPigurToday
FROM
VT5727_POP_LOANS_M AS LOANS_M
INNER JOIN VBM442_AGREEMENTS
ON  (VBM442_AGREEMENTS.Branch_Cust_IP=LOANS_M.Branch_Cust_IP AND VBM442_AGREEMENTS.Loan_Model_Code = LOANS_M.Loan_Model_Code
     AND VBM442_AGREEMENTS.Loan_Serial_Nbr=LOANS_M.Loan_Serial_Nbr )
INNER JOIN V03X30_LOAN_AGREEMENT 
ON (V03X30_LOAN_AGREEMENT.Agreement_Account_Id=VBM442_AGREEMENTS.Agreement_Account_Id AND V03X30_LOAN_AGREEMENT.Loan_Value_Date>= LOANS_M.agreement_open_date)
WHERE
VBM442_AGREEMENTS.Agreement_Category_Code  =  3
AND VBM442_AGREEMENTS.Loan_Type_Code  IN  ( 11, 21, 22  )  
AND LOANS_M.ind_Delayed_Deal = 1  
) WITH DATA PRIMARY INDEX (Branch_Cust_IP,Agreement_Account_Id,Agreement_Account_Id_pigur) ON COMMIT PRESERVE ROWS ;

Re: Create Violate Tera table from SAS

Ronein — Mon, 08 Jan 2024 12:29:19 GMT

There is warning

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc sql noprint;
select put(max(process_date),date9.) into :max_process_date trimmed
from teradata.V797_PROCESS_DATE
;
quit;
/*WARNING: The format DATE was not located on the database.  In-database processing will proceed without it.*/
/*WARNING: Syntax error or access violation Syntax error: expected something between '(' and the string 'DATE'.*/

proc sql;
  create table want as
  select *
  from teradata.VBM5732_AGR_DELAYED_PAYMENTS
  where AS52_TAR_DHIA between "01jan2024"d and "&max_process_date"d
  ;
quit;

Re: Create Violate Tera table from SAS

Patrick — Mon, 08 Jan 2024 12:38:15 GMT

For running the code out of a SAS session just copy/paste it into one or multiple execute blocks.

Using a single execute block has the advantage that you can be sure that everything runs on a single connection however the libname is defined (= connection type not relevant, also no need for dbmstemp).

proc sql;
  connect using TeraData;  

  execute(
      CREATE VOLATILE TABLE  VT5727_DATES_M  AS (
      SELECT Max(PROCESS_DATE )   AS END_DATE  
      ,(CAST('01/10/2023' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE 
      ,END_DATE/ 100 + 190000 AS MONTH_REPORTED        
      FROM   V797_PROCESS_DATE  
      )WITH DATA ON COMMIT PRESERVE ROWS
      ; 

      CREATE VOLATILE TABLE VT5727_POP_LOANS_M AS (
      ...... and so on. Just copy/past of all your Teradata code.....    
      ;
    ) by teradata;
  ;
quit;

Re: Create Violate Tera table from SAS

Patrick — Mon, 08 Jan 2024 12:35:30 GMT

@Ronein wrote:

There is warning

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc sql noprint;
select put(max(process_date),date9.) into :max_process_date trimmed
from teradata.V797_PROCESS_DATE
;
quit;
/*WARNING: The format DATE was not located on the database.  In-database processing will proceed without it.*/
/*WARNING: Syntax error or access violation Syntax error: expected something between '(' and the string 'DATE'.*/

Then just amend the SQL. I don't have access to Teradata so can't test the code.

Sometimes you actually first trying to fix things on your own would be appreciated. It's often not that hard.

proc sql noprint;
  select put(max_process_date,date9.) into :max_process_date trimmed
  from
  (
    select max(process_date) as max_process_date
    from teradata.V797_PROCESS_DATE
  )
  ;
quit;

Re: Create Violate Tera table from SAS

Ronein — Mon, 08 Jan 2024 12:41:18 GMT

Thank you!

I applied your code in the following structure

proc sql;
connect using TeraData;
execute(

CREATE VOLATILE TABLE VT5727_DATES_M AS (
SELECT
FROM
)WITH DATA ON COMMIT PRESERVE ROWS
;

CREATE VOLATILE TABLE VT5727_POP_LOANS_M AS (
SELECT
FROM
)WITH DATA ON COMMIT PRESERVE ROWS
;

) by teradata;
;
quit;

The code I run is

proc sql;
connect using TeraData;  
  execute(

      CREATE VOLATILE TABLE  t_dates  AS (
		SELECT (CAST('01/01/2024' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE ,
        Max(PROCESS_DATE) as End_Date 
		FROM   V797_PROCESS_DATE
      )WITH DATA ON COMMIT PRESERVE ROWS
      ; 



      CREATE VOLATILE TABLE want AS (
		SELECT  DHIA_TASH.*
		FROM VBM5732_AGR_DELAYED_PAYMENTS  AS DHIA_TASH 
		Where AS52_TAR_DHIA BETWEEN(SEL START_DATE FROM t_dates)  AND (SEL END_DATE FROM t_dates)
	   )WITH DATA ON COMMIT PRESERVE ROWS
      ;


    ) by teradata;
  ;
quit;

I have error

ERROR: Teradata execute: Only a COMMIT WORK or null statement is legal after a DDL Statement.

Re: Create Violate Tera table from SAS

Amir — Mon, 08 Jan 2024 12:43:35 GMT

Hi,

For future reference, please see volatile and violate .

Thanks & kind regards,

Amir.

Re: Create Violate Tera table from SAS

Ronein — Mon, 08 Jan 2024 12:47:42 GMT

I am allowed to create only volatile

Re: Create Violate Tera table from SAS

Patrick — Mon, 08 Jan 2024 13:01:21 GMT

Just copy/pasting this error into Google would return links that then can give you some insights of what's happening here and why the copy/pasted code doesn't just work as-is.

For now just try and add a commit; between the other commands.

proc sql;
connect using TeraData;  
  execute(
    CREATE VOLATILE TABLE  t_dates  AS (
		SELECT (CAST('01/01/2024' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE ,
        Max(PROCESS_DATE) as End_Date 
		FROM   V797_PROCESS_DATE
      )WITH DATA ON COMMIT PRESERVE ROWS
      ; 
    commit;

    CREATE VOLATILE TABLE want AS (
		SELECT  DHIA_TASH.*
		FROM VBM5732_AGR_DELAYED_PAYMENTS  AS DHIA_TASH 
		Where AS52_TAR_DHIA BETWEEN(SEL START_DATE FROM t_dates)  AND (SEL END_DATE FROM t_dates)
	   )WITH DATA ON COMMIT PRESERVE ROWS
      ;
    commit;

    ) by teradata;
  ;
quit;

Re: Create Violate Tera table from SAS

Ronein — Mon, 08 Jan 2024 20:25:28 GMT

No Work :

ERROR: Teradata connection: MTDP: EM_NOHOST(224): name not in HOSTS file or names database. .


libname td teradata server=dwprod schema=DWP_vall authdomain=TeraDataAuth CONNECTION=GLOBAL  dbmstemp=yes ;
proc sql;
connect using td;  
  execute(
    CREATE VOLATILE TABLE  t_dates  AS (
		SELECT (CAST('01/01/2024' AS DATE FORMAT 'DD/MM/YYYY')) as START_DATE ,
        Max(PROCESS_DATE) as End_Date 
		FROM   V797_PROCESS_DATE
      )WITH DATA ON COMMIT PRESERVE ROWS
      ; 
    commit;
    CREATE VOLATILE TABLE want AS (
		SELECT  DHIA_TASH.*
		FROM VBM5732_AGR_DELAYED_PAYMENTS  AS DHIA_TASH 
		Where AS52_TAR_DHIA BETWEEN(SEL START_DATE FROM t_dates)  AND (SEL END_DATE FROM t_dates)
	   )WITH DATA ON COMMIT PRESERVE ROWS
      ;
    commit;
    ) by teradata;
  ;
quit;

Re: Create Violate Tera table from SAS

Tom — Mon, 08 Jan 2024 20:32:35 GMT

@Ronein wrote:

I am allowed to create only volatile

That will make it harder to work with Teradata, unless things have changed in the past 5 years.

We used to create small permanent individual or team databases for users so that they could store things temporarily.

It was especially important when needing to upload some data into Teradata so it could be used to drive the Teradata queries. For example to upload a set of IDS to use to subset from the large database. With a permanent table you could use BULKLOAD (fastload?) feature and so jobs would run much faster than when trying to copy the same amount of data into a volatile table.

Re: Create Violate Tera table from SAS

Ronein — Mon, 08 Jan 2024 20:45:56 GMT

Can you please show the code and also how can I verify that the two volatile tables were created?

Re: Create Violate Tera table from SAS

Ronein — Mon, 08 Jan 2024 20:53:32 GMT

This code work with no warning

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc sql noprint;
  select CAT("'",put(max_process_date,date9.),"'d") into :max_process_date trimmed
  from
  (
    select max(process_date) as max_process_date
    from teradata.V797_PROCESS_DATE
  )
  ;
quit;
%put &max_process_date;/***'07JAN2024'd***/

proc sql;
  create table want as
  select *
  from teradata.VBM5732_AGR_DELAYED_PAYMENTS
  where AS52_TAR_DHIA between "01jan2024"d and &max_process_date.
  ;
quit;

Re: Create Volatile Teradata table from SAS

AhmedAl_Attar — Mon, 08 Jan 2024 21:13:54 GMT

@Ronein

Have you considered using CTE(s) instead of Volatile Tables in Teradata?

https://support.teradata.com/community?id=community_question&sys_id=c258c3671b97fb00682ca8233a4bcba2

https://support.teradata.com/community?id=community_question&sys_id=45d68bef1b57fb00682ca8233a4bcb55

If you only want to derive subset of data based on information held in another derived table/query, CTE might be a better option!? The above two links might provide you with some insight.

Disclaimer: I have used CTE(s) before with Snowflake but not Teradata.

Hope this helps,

Ahmed