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

Database
Ambassador

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

Did you read the thread?

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

Dieter and Abhijit Thanks.

I did read the thread but I guess I missundersttod the error and asked the Wrong question.

Query Line 17:AND RP.REGIS_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE

It doesn't like "RP."

Create Table Failed 3706 Syntax Error:expected something between the word "RP" and ".".

I wonder what could be that word?

Regards ,

MBS

Enthusiast

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

Hi,

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

on a.MATCHD_CNSMR_PRSNA_ID = b.MATCHD_CNSMR_PRSNA_ID

This ON is misplaced.

Enthusiast

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

Oh, Barani I removed that statement but I still have the same issue Create Table Failed 3706 Syntax Error:expected something between the word "RP" and ".". and I have no idea @ what line #

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  

             Group by RP.REGIS_DATETM

) With data primary index ( RP.REGIS_DATETM)

ON COMMIT PRESERVE ROWS;

Enthusiast

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

I tried creating a table that didn't work, then I try updating a Table that didn't work. Teradata VS Oracle very different.

Please some help as I am lost No Syntax is working.

UPDATE temp1
SET temp1.Q1Count = temp2.Q1Count
FROM temp1
INNER JOIN
(
SELECT CAST(RP.REGIS_DATETM AS DATE) AS MyDate,
COUNT(DISTINCT RP.REGIS_PRSNA_ID) AS Q1Count
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
GROUP BY CAST (RP.REGIS_DATETM AS DATE)
) temp2

ON temp1.MyDate = temp2.MyDate;

What is wrong with this SYNTAX? UPDATE Failed 3706: Syntax error:

Expected something between the word 'Q1Count' and the 'FROM' keyword.

Please help.

MSB

Enthusiast

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

I am thinking may be use

Insert into Temp1(temp_1.Q1Count)

SELECT CAST(RP.REGIS_DATETM AS DATE) AS MyDate,
COUNT(DISTINCT RP.REGIS_PRSNA_ID) AS Q1Count
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
GROUP BY CAST (RP.REGIS_DATETM AS DATE)

from someTable.

where Temp1.Date= SomeTable.Date;

Please help I am so LOST.

Enthusiast

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

Hi,

From what i see and understood from the query is that You want to Join the Temp1 table & SOmetable with date. Please find the modified query, You are selecting 2 columns from the SOmetable and trying to insert only one to the temp1 table.

Insert into Temp1(temp_1.Q1Count)

SEL someTable.Q1Count FROM
(
SELECT CAST(RP.REGIS_DATETM AS DATE) AS MyDate,
COUNT(DISTINCT RP.REGIS_PRSNA_ID) AS Q1Count
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
GROUP BY CAST (RP.REGIS_DATETM AS DATE)
) someTable

INNER JOIN Temp1
ON
Temp1.Date= SomeTable.Date;

Thanks & Regards,

Adharssh.

Enthusiast

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

Hi, 

The modified update query is this, You are actually joining the Update table with the Source table. When you actually update an table, the table itself joined. Usually Inner join.

UPDATE temp1
FROM (
SELECT CAST(RP.REGIS_DATETM AS DATE) AS MyDate,
COUNT(DISTINCT RP.REGIS_PRSNA_ID) AS Q1Count
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
GROUP BY CAST (RP.REGIS_DATETM AS DATE)
) temp2
SET temp1.Q1Count = temp2.Q1Count
where temp1.MyDate = temp2.MyDate;

Enthusiast

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

We can only make some guess without looking at the actual DDLs.... Share the tables DDLs and people here will definitely help you with both of your queries!

Highlighted
Enthusiast

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

Thanks for everyone help.

Here is the Functional Code for Update Query but I still need some Logic Help I am going to put here hoping someone have an guide me in a right direction. I do not have access to writing Store Proc, I do not have DBA access or any Scheduling Tools available.

Update Query.

--------------

UPDATE MY_SANDBOX.objTblTmp
FROM
(
SELECT CAST(RP.REGIS_DATETM AS DATE) AS MyDate,
COUNT(DISTINCT RP.REGIS_PRSNA_ID) AS Q1Count

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

GROUP BY CAST (RP.REGIS_DATETM AS DATE)
) temp2,

/*------------------------------------------*/

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

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

LEFT OUTER JOIN
ETL_CTRL.LOAD_CONTROL LC
ON CAST(RP.SYS_SOURCE AS DECIMAL(18,0)) = LC.LOAD_ID
AND LC.LOAD_TYPE = 'ETL'

LEFT OUTER JOIN
ETL_CTRL.SOURCE_CONTROL SC
ON LC.SOURCE_ID = SC.SOURCE_ID

WHERE
RP.MKTNG_PGM_NBR IN (115)
AND LC.SOURCE_ID IN (1213)
AND RPE.SUBSCRPTN_OPT_IND = 'I'
AND RP.PRSNA_STATUS_CODE = 'AC'

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

GROUP BY CAST (RP.REGIS_DATETM AS DATE)

) temp3,

/*-------------------------------------------------*/

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

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

LEFT OUTER JOIN
ETL_CTRL.LOAD_CONTROL LC
ON CAST(RP.SYS_SOURCE AS DECIMAL(18,0)) = LC.LOAD_ID
AND LC.LOAD_TYPE = 'ETL'

LEFT OUTER JOIN
ETL_CTRL.SOURCE_CONTROL SC
ON LC.SOURCE_ID = SC.SOURCE_ID

WHERE
RP.MKTNG_PGM_NBR IN (115)
AND LC.SOURCE_ID IN (1318)
AND RPE.SUBSCRPTN_OPT_IND = 'I'
AND RP.PRSNA_STATUS_CODE = 'AC'

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

GROUP BY CAST (RP.REGIS_DATETM AS DATE)

) as temp4,

/* ------------------------------------------*/

(SELECT CAST(RPE.CNSMR_CHCE_DATETM AS DATE) as MyDate,
COUNT (DISTINCT RP.REGIS_PRSNA_ID) as Q4Count

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 = 'O'
AND RPE.CNSMR_CHCE_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE

GROUP BY CAST ( RPE.CNSMR_CHCE_DATETM AS DATE)
) as temp5,

/*-----------------------------------------------------*/

(SELECT CAST (CA.CNSMR_ACTN_START_DATETM AS DATE) as MyDate,
COUNT (DISTINCT RP.MATCHD_CNSMR_PRSNA_ID) as Q5Count

FROM
iCRM_LOAD.REGIS_PRSNA RP

INNER JOIN
iCRM.CNSMR_ACTN CA
ON RP.REGIS_PRSNA_ID = CA.REGIS_PRSNA_ID

WHERE
CA.MKTNG_PGM_NBR IN (115)
AND CA.LEGAL_ENT_NBR IN (15)
AND CA.CNSMR_ACTN_TYPE_CODE IN ('RS')

AND CA.CNSMR_ACTN_START_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE

GROUP BY CAST (CA.CNSMR_ACTN_START_DATETM AS DATE)

) as temp6,

/*----------------------------------------*/

(SELECT CAST (CA.CNSMR_ACTN_START_DATETM AS DATE) as MyDate,
COUNT (DISTINCT RP.MATCHD_CNSMR_PRSNA_ID) as Q6Count

FROM
iCRM_LOAD.REGIS_PRSNA RP

INNER JOIN
iCRM.CNSMR_ACTN CA
ON RP.REGIS_PRSNA_ID = CA.REGIS_PRSNA_ID

WHERE
CA.MKTNG_PGM_NBR IN (115)
AND CA.LEGAL_ENT_NBR IN (15)
AND CA.CNSMR_ACTN_TYPE_CODE IN ('RC')

AND CA.CNSMR_ACTN_START_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE

GROUP BY CAST (CA.CNSMR_ACTN_START_DATETM AS DATE)

as temp7,

/*---------------------------------------------------------*/

(SELECT CAST (CA.CNSMR_ACTN_START_DATETM AS DATE) as MyDate,
COUNT (DISTINCT RP.MATCHD_CNSMR_PRSNA_ID) as Q7Count

FROM
iCRM_LOAD.REGIS_PRSNA RP
INNER JOIN
iCRM.CNSMR_ACTN CA
ON RP.REGIS_PRSNA_ID = CA.REGIS_PRSNA_ID
WHERE
CA.MKTNG_PGM_NBR IN (115)
AND CA.LEGAL_ENT_NBR IN (15)
AND CA.CNSMR_ACTN_TYPE_CODE IN ('CR')

AND CA.CNSMR_ACTN_START_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE

GROUP BY CAST (CA.CNSMR_ACTN_START_DATETM AS DATE)

) as temp8

SET Q1Count = coalesce(temp2.Q1Count,0),
Q2Count = coalesce(temp3.Q2Count,0),
Q3Count = coalesce(temp4.Q3Count,0),
Q4Count = coalesce(temp5.Q4Count,0),
Q5Count = coalesce(temp6.Q5Count,0),
Q6Count = coalesce(temp7.Q6Count,0),
Q7Count = coalesce(temp8.Q7Count,0)
WHERE objTblTmp.MyDate = temp2.MyDate
and objTblTmp.MyDate = temp3.MyDate
and objTblTmp.MyDate = temp4.MyDate
and objTblTmp.MyDate = temp5.MyDate
and objTblTmp.MyDate = temp6.MyDate
and objTblTmp.MyDate = temp7.MyDate
and objTblTmp.MyDate = temp8.MyDate;