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

Database
Enthusiast

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

You got a working piece of code... What kind of help you are looking for?

Sorry no magic wand!

Enthusiast

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

Thanks Qaisar for the vote of confidence.

Fan

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

@MSB:

In the syntax of Create table with data you haven't specified the alias column names in select query. Try to run the below query and see if it works:

CREATE TABLE TWM_SANDBOX.tbl1_frm_qry1 AS
(
SELECT CAST (RP.REGIS_DATETM AS DATE) AS Column1 , COUNT(DISTINCT RP.REGIS_PRSNA_ID) AS Column2
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;

Thanks,

Gauz

Enthusiast

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

on commit delete rows will be used in ANSI mode.there no need to put preserv rows explicitly .

in ansi mode rows won't be deleted untill commit or rollback