update and inner join issue

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

update and inner join issue

Hi,

 

i have this code:

 

UPDATE a
SET 
 EventNum = b.EventNum,
 EventType = b.EventType,
 FromDate = b.FromDate,
 ToDate = b.ToDate,
 Last_EnterDate = GetDate(),
 TimesInOffer = TimesInOffer + 1    
from  [campaignoperatorsDB].dbo.B2C_Casino_0000_Fact_CRM_Machine_PlayersHistory a
inner join [campaignoperatorsDB].dbo.B2C_Casino_0000_Fact_CRM_Machine_PlayersInProtocols B                                                         
   on (a.CID=B.CID and a.OfferNum = B.OfferNum )

 

 

when i try convert to teradata it warn me that update order befor inner join between 2 tables not exsist in TERADATA some one know how to  present it anyway.

 

Tx!!!

6 REPLIES
Teradata Employee

Re: update and inner join issue

It would be helpful to see the translated SQL that was attempted on Teradata and the actual error message.

Junior Contributor

Re: update and inner join issue

Teradata simply doesn't allow explicit join syntax (this also means no Outer Join) for Updates, you must switch to old style comma-delimited syntax:

 

from  [campaignoperatorsDB].dbo.B2C_Casino_0000_Fact_CRM_Machine_PlayersHistory a,
      [campaignoperatorsDB].dbo.B2C_Casino_0000_Fact_CRM_Machine_PlayersInProtocols B                                                          
where (a.CID=B.CID and a.OfferNum = B.OfferNum )
Enthusiast

Re: update and inner join issue

her you can see my code after conversion and the warning error:

 

(X) UPDATE a -----------> TERADATA PUT HER ERROR WARNING

from campaignoperatorsDB.B2C_Casino_0000_Fact_CRM_Machine_PlayersHistory a,

campaignoperatorsDB.B2C_Casino_0000_Fact_CRM_Machine_PlayersInProtocols B

SET

EventNum = b.EventNum,

EventType = b.EventType,

FromDate = b.FromDate,

ToDate = b.ToDate,

Last_EnterDate = CURRENT_TIMESTAMP,

TimesInOffer = TimesInOffer+1

where((a.CID = B.CID and a.OfferNum = B.OfferNum)) AND

CAST(CAST((CURRENT_TIMESTAMP(FORMAT 'YYYYMMDD')) AS VARCHAR(30)) AS INTEGER)

not between a.FromDate and

CAST((CURRENT_DATE - INTERVAL '1' day )

CAST(CAST(SUBSTRING(a.ToDate FROM 1 FOR 8) AS TIMESTAMP(3)) AS TIMESTAMP(3)))

(FORMAT 'YYYYMMDD')) AS VARCHAR(30)) AS INTEGER);

 

 

Tx!!!

 

Enthusiast

Re: update and inner join issue

i Guess my problem was the date line , couse when i fix the code as 'dnoeth' suggested it's work.

Enthusiast

Re: update and inner join issue

Hi All,

 

  How do i update the table using multiple join conditions? can anyone help me on this. Below is my query

UPDATE XDW_PRD_COREV..ASSC_ACTV_LOG L
INNER JOIN XDW_PRD_COREV.ORGN_LVL ORG on ORG.ORGN_LVL_KEY = L.ORGN_LVL_KEY
inner join (SEL DISTINCT TIME_ZONE, PRIN, SYS FROM XDW_PRD_ETLV.WFX_TECH_SYS_PRIN_ETL) ETL
ON ETL.SYS = ORG.SITE_SYS AND ETL.PRIN = ORG.CO_PRIN AND ORG.ORGN_LVL_NM = 'PRIN' AND ORG.XDW_REC_STS_CD = 'A'
SET ACTV_END_DTTM = ACTV_END_DTTM + INTERVAL '4' HOUR, actv_strt_dttm = actv_strt_dttm + INTERVAL '4' HOUR
WHERE CAST(CAST(CAST(ACTV_END_DTTM AS CHAR(19)) AS TIMESTAMP(0)) AS TIME(0)) = '19:59:59' and ETL.TIME_ZONE = 'EAST'
AND L.SRC_SYS_CD = 'WFX';

Teradata Employee

Re: update and inner join issue

One approach: Use implicit JOIN syntax with commas and predicates in the WHERE clause.

UPDATE L FROM
XDW_PRD_COREV.ASSC_ACTV_LOG L
INNER JOIN XDW_PRD_COREV.ORGN_LVL ORG,
(SEL DISTINCT TIME_ZONE, PRIN, SYS FROM XDW_PRD_ETLV.WFX_TECH_SYS_PRIN_ETL) ETL
SET ACTV_END_DTTM = L.ACTV_END_DTTM + INTERVAL '4' HOUR,
 actv_strt_dttm = L.actv_strt_dttm + INTERVAL '4' HOUR
WHERE CAST(CAST(CAST(L.ACTV_END_DTTM AS CHAR(19)) AS TIMESTAMP(0)) AS TIME(0)) = '19:59:59' 
and ETL.TIME_ZONE = 'EAST'
AND L.SRC_SYS_CD = 'WFX'
AND ORG.ORGN_LVL_KEY = L.ORGN_LVL_KEY
AND ETL.SYS = ORG.SITE_SYS AND ETL.PRIN = ORG.CO_PRIN 
AND ORG.ORGN_LVL_NM = 'PRIN' AND ORG.XDW_REC_STS_CD = 'A';

Be careful with Time Zones / TIME data types.

 

Might be better to revise this condition, e.g.

SUBSTRING(CAST(L.ACTV_END_DTTM AS CHAR(19)) FROM 12 FOR 8)= '19:59:59'