Unable to Create View but otherwise statement is running OK. Column names in each view or function must be unique

Question

If I do not CREATE VIEW then this statement is working fine. But when I try to create a VIEW it gives me error:

Msg 4506, Level 16, State 1, Procedure uv_AZRCRV_SOPPOPLink, Line 3 Column names in each view or function must be unique. Column name 'POPRequisitionNumber' in view or function 'uv_AZRCRV_SOPPOPLink' is specified more than once.

CREATE VIEW uv_AZRCRV_SOPPOPLink AS SELECT
['Purchase Order Requisition'].*
,['Purchase Order Requisition Line'].*
,['Purchase Order'].*
,['Purchase Order Line'].* FROM
(SELECT
    POPRequisitionNumber
    ,DOCDATE
FROM
    POP10200
UNION ALL
    SELECT
        POPRequisitionNumber
        ,DOCDATE
    FROM
        POP30200) AS ['Purchase Order Requisition Line'] 
INNER JOIN
(SELECT
    POPRequisitionNumber
    ,ORD
    ,ITEMNMBR
    ,QTYORDER
FROM
    POP10210
UNION ALL
    SELECT
        POPRequisitionNumber
        ,ORD
        ,ITEMNMBR
        ,QTYORDER
    FROM
        POP30210) AS ['Purchase Order Requisition']
            ON ['Purchase Order Requisition'].POPRequisitionNumber = ['Purchase Order Requisition Line'].POPRequisitionNumber 
LEFT JOIN
SOP60100 AS ['SOP_POPLink']
    ON
        ['SOP_POPLink'].SOPNUMBE = ['Purchase Order Requisition'].POPRequisitionNumber
    AND
        ['SOP_POPLink'].LNITMSEQ = ['Purchase Order Requisition'].ORD 
LEFT JOIN
(SELECT
    PONUMBER
    ,ORD
FROM
    POP10110
UNION ALL
    SELECT
        PONUMBER
        ,ORD
    FROM
        POP30110) AS ['Purchase Order Line']
            ON
                ['Purchase Order Line'].PONUMBER = ['SOP_POPLink'].PONUMBER
            AND
                ['Purchase Order Line'].ORD = ['SOP_POPLink'].ORD 
LEFT JOIN
(SELECT
    PONUMBER
    ,DOCDATE
FROM
    POP10100
UNION ALL
    SELECT
        PONUMBER
        ,DOCDATE
    FROM
        POP30100) AS ['Purchase Order']
            ON
                ['Purchase Order'].PONUMBER = ['Purchase Order Line'].PONUMBER 
GO

score 0 · Accepted Answer · answered Jul 08 '20 at 15:53

You have several columns with the same name in the output for the view definition. For example

POPRequisitionNumber is found in both ['Purchase Order Requisition'] and ['Purchase Order Requisition Line'] as you have select * (all columns) from both these tables in your select statement.

You will have to specify the exact columns you require individually, or alias them so they are unique.

When you run a SELECT statement on its own this is OK as the column names in the results are not being stored anywhere but if you try to create a view you cannot have 2 columns with the same name.

You would face the same issue if you used this query as a subquery.

Now I understand. However, I am new to SQL statements. Is it possible for you to correct this code for me, please... ? — adeel, Jul 09 '20 at 05:51

score 0 · Answer 2 · answered Jul 08 '20 at 16:04

SELECT * is quick and easy, but sometimes harmful. This is one of the situations where it is harmful.

In particular, quoting from Dave Markle's answer on that question:

Binding Problems. When you SELECT *, it's possible to retrieve two columns of the same name from two different tables. This can often crash your data consumer. Imagine a query that joins two tables, both of which contain a column called "ID". How would a consumer know which was which? SELECT * can also confuse views (at least in some versions SQL Server) when underlying table structures change -- the view is not rebuilt, and the data which comes back can be nonsense. And the worst part of it is that you can take care to name your columns whatever you want, but the next guy who comes along might have no way of knowing that he has to worry about adding a column which will collide with your already-developed names.

In your case, two or more of the tables that you're selecting from have a column named POPRequisitionNumber. If the data is redundant, you only need to select it once. If the data is not redundant, you can use aliases to give each of those columns a name that is more descriptive and unique.

Unable to Create View but otherwise statement is running OK. Column names in each view or function must be unique

2 Answers2