Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 10 Jun 2004 @ 12:53:35 GMT


     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 


Subj:   Strange error in case of UNION inside a INSERT-SELECT query
 
From:   Agarwal, Rishi

Hi all,

I am working on a INSERT-SELECT query that is giving error in some special case. The SQL is as:

     INSERT INTO TEMPTABLE
     SELECT A.OWNERCARRIERID AS OwnerCarrierId,
            A.MONTHID AS MonthId,
            B.ExternalSubsFinal AS ExternalSubsFinal,
            C.ExternalGAFinal AS ExternalGaFinal,
            D.ExternalDaFinal,
            D.PrevMonthSubFinal

     FROM
              (select ownercarrierid , monthid from VW_MMAD_FA_MM_EXTRNL_SUBS

              Union
              select ownercarrierid , monthid from VW_MMAD_FA_MM_EXTRNL_GA

              Union
              select ownercarrierid , monthid from VW_MMAD_FA_MM_EXTRNL_DA
              ) A

     LEFT OUTER JOIN VW_MMAD_FA_MM_EXTRNL_SUBS B
                ON (A.OWNERCARRIERID = B.OWNERCARRIERID and A.MONTHID = B.MONTHID)

     LEFT OUTER JOIN VW_MMAD_FA_MM_EXTRNL_GA C
                ON (A.OWNERCARRIERID     = C.OWNERCARRIERID and A.MONTHID = C.MONTHID)

     LEFT OUTER JOIN VW_MMAD_FA_MM_EXTRNL_DA D
                ON (A.OWNERCARRIERID     = D.OWNERCARRIERID and A.MONTHID = D.MONTHID)

SELECT portion of this SQL when executed separately is working fine. But when we do INSERT-SELECT, the same query is giving error "table/View/Trigger name 'A' is ambiguous" . This error is generated only when two SELECT queries inside the Union block returns NO records. When all the three SELECT queries inside the Union block returns records or when more than one SELECT returns records, in those cases it is working fine. But when more than one SELECT returns NO records, in that case only it's throwing error. We are dealing with such case in our application.

Also, if I modify the Union block using one more derived table as mentioned below, it's working absolutely fine in all the cases:

     INSERT INTO TEMPTABLE
     SELECT  A.OWNERCARRIERID AS OwnerCarrierId,
             A.MONTHID AS MonthId,
             B.ExternalSubsFinal AS ExternalSubsFinal,
             C.ExternalGAFinal AS ExternalGaFinal,
             D.ExternalDaFinal,D.PrevMonthSubFinal

     FROM
              (SELECT ownercarrierid, monthid FROM
              (select ownercarrierid , monthid from VW_MMAD_FA_MM_EXTRNL_SUBS

              Union
              select ownercarrierid , monthid from VW_MMAD_FA_MM_EXTRNL_GA

              Union
              select ownercarrierid , monthid from VW_MMAD_FA_MM_EXTRNL_DA ) T
              ) A

     LEFT OUTER JOIN VW_MMAD_FA_MM_EXTRNL_SUBS B
                ON (A.OWNERCARRIERID = B.OWNERCARRIERID and A.MONTHID = B.MONTHID)

     LEFT OUTER JOIN VW_MMAD_FA_MM_EXTRNL_GA C
                ON (A.OWNERCARRIERID = C.OWNERCARRIERID and A.MONTHID = C.MONTHID)

     LEFT OUTER JOIN VW_MMAD_FA_MM_EXTRNL_DA D
                ON (A.OWNERCARRIERID = D.OWNERCARRIERID and A.MONTHID = D.MONTHID)

Can anyone tell me what is the reason behind this strange behavior.


Thanks

Rishi



     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016