Joined Tables are not allowed in FROM clause.

Database
Enthusiast

Joined Tables are not allowed in FROM clause.

Hi

 

I am running the below update statement in a store procedure and it give me error " SPL1027:E(L127), Missing/Invalid SQL statement'E(3706):Syntax error: Joined Tables are not allowed in FROM clause.'.

 

update
sas
from
DWD09V_APP_EHR_DBO.StyleAreaSeasonality sas
inner join
-- join with both active/inactive BDITEMS to get the DeptNo
DWD09C_EHR_IMPORT.BDITEMS b
on b.SectArea = sas.SectArea
and b.StyleRef = sas.StyleRef
--FROM
--DWD09C_EHR_IMPORT.BDITEMS b
inner join
 (select
         StyleRef, 
         SectArea, 
         GroupNo
   from DWD09C_EHR_IMPORT.STCCGRD
   group by
      StyleRef, 
      SectArea, 
      GroupNo
) 
sg
on sg.StyleRef = sas.StyleRef
and sg.SectArea = sas.SectArea,
DWD09V_APP_EHR_DBO.dimGroup G
set
   GroupID = g.dimGroupID
where
g.GroupNo = sg.GroupNo
and g.SectArea = sg.SectArea
and g.DeptNo = b.DeptNo
and v_date between g.SCDStartDate AND NVL(g.SCDEndDate, v_date) 
AND sas.SeasonalityRangeID = seasonalityID
and sas.GroupID <> g.dimGroupID; 

 

 

Any solution for this error ?

 

Thanks

Akshat

1 REPLY
Teradata Employee

Re: Joined Tables are not allowed in FROM clause.

You can't use the JOIN keyword.  Use FROM table-list instead.  Also, I believe the SET clause has to precede the WHERE clause.  E.g.,

 

update
DWD09V_APP_EHR_DBO.StyleAreaSeasonality sas
from
DWD09C_EHR_IMPORT.BDITEMS b
,(select
         StyleRef,
         SectArea,
         GroupNo
   from DWD09C_EHR_IMPORT.STCCGRD
   group by
      StyleRef,
      SectArea,
      GroupNo
) sg
,DWD09V_APP_EHR_DBO.dimGroup G
set
   GroupID = g.dimGroupID
WHERE b.SectArea = sas.SectArea
and b.StyleRef = sas.StyleRef
AND sg.StyleRef = sas.StyleRef
and sg.SectArea = sas.SectArea
AND g.GroupNo = sg.GroupNo
and g.SectArea = sg.SectArea
and g.DeptNo = b.DeptNo
and v_date between g.SCDStartDate AND NVL(g.SCDEndDate, v_date)
AND sas.SeasonalityRangeID = seasonalityID
and sas.GroupID <> g.dimGroupID;
;