Archives of the TeradataForum

Message Posted: Mon, 24 Nov 2003 @ 19:00:29 GMT

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

The problem is with your inner subqry where column 'id' is ambigous amongst relations C & D.

/* NOT WORKING */

SELECT *
FROM
( SELECT
   A.*
  ,C.id <---------------
  ,D.id <---------------
 FROM       ( SELECT 1 AS id1 ) AS A
 CROSS JOIN ( SELECT 1 AS id  ) AS C <---------------
 CROSS JOIN ( SELECT 1 AS id  ) AS D <---------------
) AS X (id1,id2,id3);

This is aliased to some diff name in the working qry.

/* WORKING */

SELECT *
FROM
( SELECT
   A.id1
  ,C.id AS id2 <---------------
  ,D.id AS id3 <---------------
 FROM       ( SELECT 1 AS id1 ) AS A
 CROSS JOIN ( SELECT 1 AS id  ) AS C
 CROSS JOIN ( SELECT 1 AS id  ) AS D
) AS X;

There are multiple ways to re-code not working qry so as to remove the ambiguity.

One of them is provided by you itself .

Another one could be.

SELECT *
FROM
( SELECT
   A.*
  ,C.*
  ,D.*
 FROM       ( SELECT 1 AS ida ) AS A
 CROSS JOIN ( SELECT 1 AS idc ) AS C
 CROSS JOIN ( SELECT 1 AS idd ) AS D
) AS X;

Thanks, Vivek.

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