Archives of the TeradataForum

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

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

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>>