create view

CREATE VIEW getUsedColumns AS SELECT * FROM (SELECT DISTINCT attr FROM iba UNION SELECT DISTINCT attr FROM new_iba) WHERE attr != 0;

its working with oracle

how to chenge in t-sql

thanx

Hi,

you have to ALIAS Your SubQuery.

CREATE VIEW getUsedColumns
AS
SELECT * FROM
(
SELECT DISTINCT attr
FROM iba
UNION
SELECT DISTINCT attr
FROM new_iba
) SomeSubQuery
WHERE attr != 0;

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

You need to specify an alias for the derived table. And you don't really need the DISTINCT in each of the SELECT statements since the UNION will eliminate the duplicates anyway. You will get better performance by writing it as:

SELECT * FROM (SELECT Dattr FROM iba UNION SELECT attr FROM new_iba) as t

WHERE attr != 0;