volatile table as select..with data - not loading data

Database
Enthusiast

volatile table as select..with data - not loading data

I am creating a multiset volatile table from a select statement that has a table join. Is this not allowed for the creation of the volatile table?

When I execute the statement it creates the table but loads no data (data does return from the select statement).

All the columns are coming from the same table the join is just for filtering

CREATE MULTISET VOLATILE TABLE vt_tmp
AS ( SELECT t1.COLA
, t1.COLB
FROM TABLE_1 t1
INNER JOIN TABLE_2 t2
ON t1.COLA = t2.COLAA
)
WITH DATA NO PRIMARY INDEX

Teradata 13.0
23 REPLIES
Senior Apprentice

Re: volatile table as select..with data - not loading data

Just add ON COMMIT PRESERVE ROWS, as the default is ON COMMIT DELETE ROWS.

Dieter
Enthusiast

Re: volatile table as select..with data - not loading data

That worked perfectly.. Thanks..
Fan

Re: volatile table as select..with data - not loading data

Hi

I've used volatile tables to obtain the current date and time when i start the bteq script and at the end of bteq to insert into a logg table start time/date of the script and end time/date of the script and worked fine but the question is how can i create a varible in my BTEQ script (.SET var1 ?) and put in this variable the result of a select into statement?

ex:

select sysdate into v_now from dual;

dbms_output.putline(v_now);

Thank You

Re: volatile table as select..with data - not loading data

Hi,

I have a question related to "sas teradata" using "multiset volatile table".

Previously my script in "BTEQ" format and it runs perfectly----  the general format for this script is :-

create multiset volatile xyz as (code..........) by tera;

create multiset volatile xyz_1 as (code.....from xyz.) by tera;

select  ......code.....from xyz_1

Now, the above code using the database for that i do not have create table access on teradata. In that case how can i retrive data by using sas teradata. 

Enthusiast

Re: volatile table as select..with data - not loading data

hello,dieter:

Where we can use 'on commit delete rows'option?I haven't use this options until now.

Enthusiast

Re: volatile table as select..with data - not loading data

ON COMMIT DELETE ROWS is the default option set with volatile table syntax. You can mention that explicitly as well, but if you specify this option then you will loose the data as soon as the transaction is completed.

Re: volatile table as select..with data - not loading data

Hi paulxia39,breidy

As above they said,We always  put  'ON COMMIT PRESERVE ROWS' like below syntax

So we can save vt_tmp  data rows for further usage.

CREATE MULTISET VOLATILE TABLE vt_tmp

AS ( SELECT t1.COLA

, t1.COLB

FROM TABLE_1 t1

INNER JOIN TABLE_2 t2

ON t1.COLA = t2.COLAA

)

WITH DATA NO PRIMARY INDEX

ON COMMIT PRESERVE ROWS;

Enthusiast

Re: volatile table as select..with data - not loading data

How do I create a Table from Select Query with Data?

Below is my Query and Table I want to create?

create table TWM_SANDBOX.tbl1_frm_qry1 as
(
SELECT CAST (RP.REGIS_DATETM AS DATE), COUNT(DISTINCT RP.REGIS_PRSNA_ID)
FROM
iCRM_LOAD.REGIS_PRSNA RP
INNER JOIN
iCRM.MKTNG_PGM MP
ON RP.MKTNG_PGM_NBR = MP.MKTNG_PGM_NBR
INNER JOIN
iCRM.REGIS_PRSNA_EMAIL_ADDR RPE
ON RP.REGIS_PRSNA_ID = RPE.REGIS_PRSNA_ID
AND RP.MKTNG_PGM_NBR = RPE.MKTNG_PGM_NBR
WHERE
RP.MKTNG_PGM_NBR IN (115)
AND RPE.SUBSCRPTN_OPT_IND = 'I'
AND RP.PRSNA_STATUS_CODE = 'AC'
AND RP.REGIS_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE

)
with data;

Kindly correct my Syntax,

Best Regards,

MBS

Enthusiast

Re: volatile table as select..with data - not loading data

You will have to use Primary index something like below

create table TWM_SANDBOX.Tbl_frm_Query1 as

(

SELECT CAST (RP.REGIS_DATETM AS DATE), COUNT(DISTINCT RP.REGIS_PRSNA_ID)

FROM

         iCRM_LOAD.REGIS_PRSNA RP

INNER JOIN

         iCRM.MKTNG_PGM MP

         ON RP.MKTNG_PGM_NBR = MP.MKTNG_PGM_NBR

INNER JOIN

         iCRM.REGIS_PRSNA_EMAIL_ADDR RPE

         ON RP.REGIS_PRSNA_ID = RPE.REGIS_PRSNA_ID

         AND RP.MKTNG_PGM_NBR = RPE.MKTNG_PGM_NBR

WHERE

         RP.MKTNG_PGM_NBR IN (115)

         AND RPE.SUBSCRPTN_OPT_IND = 'I'

         AND RP.PRSNA_STATUS_CODE = 'AC'

AND RP.REGIS_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE

on a.MATCHD_CNSMR_PRSNA_ID = b.MATCHD_CNSMR_PRSNA_ID

 

) With data primary index ( Columns) ;

 

or NO PRIMARY INDEX 

HTH

 

Thanks

Abhijit