Column names in each view or function must be unique

Question

while creating view I am getting error Column names in each view or function must be unique but while framing select query i am getting only one record.i have to use col1 and col2 for both the tables..if data doesnot exist in A table it will take from B table.how i can do this.Thanks in advance..

Create View ViewName AS
select 
A.col1 as col1,
A.col2  as col2,
null as col1,
B.col2 as col2
from table A,table B where A.col3=B.col3

Solution 1

From your comments, it looks like you are looking for this:

Create View ViewName AS
select 
   A.col1,
   COALESCE(A.col2, B.col2) AS col2
from table A
  left join table B 
  on  A.col3=B.col3;

Use a LEFT OUTER JOIN to handle the condition where the join fails.

Explanation of the actual error:

The error says it all - your view has two columns named col1 and two columns col2. Change the columns names of one of the sets. Unlike views, an adhoc select query doesn't require unique names (or any column name at all, for that matter).

Solution 2

Based on your comment, you will probably need something like this:

CREATE VIEW ViewName AS
SELECT ISNULL(A.col1, B.col1) as col1, -- This will return B.Col1 if A.Col1 is null
       ISNULL(A.col2, B.col2) as col2,
FROM table A INNER JOIN table B ON(A.col3 = B.col3)

Edit

Based on your comments to this answer, you can do something like this:

ALTER VIEW temp AS 

SELECT COALESCE(A.col1, D.col1) as col1,
COALESCE(A.col2, B.col2, C.col2, D.col2) as col2 
FROM table A 
INNER JOIN table1 B ON (A.col3=B.col3)
INNER JOIN table3 C ON (A.col3=C.col3)
INNER JOIN table4 D ON (A.col3=D.col3) 

Note: you wrote COALESCE(A.col1,null,null,D.col1), this is equivalent to COALESCE(A.col1, D.col1), since the coalesce function will return the first argument it receives that is not null.

Solution 3

LIke this we can do?

alter view temp as select COALESCE(A.col1,null,null,D.col1) as col1, COALESCE(A.col2,B.col2,C.col2,D.col2) as col2 from table A INNER JOIN table1 B INNER JOIN table3 C INNER JOIN table4 ON (A.col3=B.col3 and A.col3=C.col3 and A.col3=D.col3)