Ambiguous colum when selecting specific month data

Question

i have a query that has data that is added on monthly basis but it is giving me the SQL ambiguous error and i dont know what to do. i have researched everywhere and cant find the answer to my problem so here it goes!

this is the query:

SELECT       REF.TOS                                AS [TOS Value]
        ,RSA_BELFAST_1921.Percentage            AS [Belfast]
        ,RSA_BIRMINGHAM_1941$.Percentage        AS [Birmingham]
        ,RSA_CARDIFFREGUS_2911$.Percentage      AS [Cardiff Regus]
        ,RSA_CASTLEMEAD_1941$.Percentage        AS [Castlemead]
        ,RSA_CHELMSFORD_1941$.Percentage        AS [Chelmsford]
        ,RSA_EDNBGH_2951$.Percentage            AS [Edimburgh]
        ,RSA_EUROPE_IOM_DOUG_2911$.Percentage   AS [Europe]
        ,RSA_GLASGOW_7301$.Percentage           AS [Glasgow]
        ,RSA_GUERNSEY_ICICI_1921P$.Percentage   AS [Guernsey]
        ,RSA_HALIFAX_7301$.Percentage           AS [Halifax]
        ,RSA_HORSHAM_1002$.Percentage           AS [Horsham]
        ,RSA_IPSWICH_1921$.Percentage           AS [Ipswich]
        ,RSA_LEEDS_2911$.Percentage             AS [Leeds]
        ,RSA_LEICESTER_2951$.Percentage         AS  [Leicester]
        ,RSA_LIVERPOOL_1002$.Percentage         AS [Liverpool]
        ,RSA_MANCHESTER_1941$.Percentage        AS [Manchester]
        ,RSA_ONELONDON_1002.Percentage          AS [London]
        ,RSA_PETERBOROUGH_3825.Percentage       AS [Peterborough]
        ,RSA_REDHILL_2951.Percentage            AS [Redhill]
        ,RSA_SUNDERLAND_1941$.Percentage        AS [Sunderland]


FROM    ( SELECT TOS FROM RSA_BELFAST_1921          UNION
         SELECT TOS FROM RSA_BIRMINGHAM_1941$       UNION
         SELECT TOS FROM RSA_CARDIFFREGUS_2911$     UNION
         SELECT TOS FROM RSA_CASTLEMEAD_1941$       UNION
         SELECT TOS FROM RSA_CHELMSFORD_1941$       UNION
         SELECT TOS FROM RSA_EDNBGH_2951$           UNION
         SELECT TOS FROM RSA_EUROPE_IOM_DOUG_2911$  UNION
         SELECT TOS FROM RSA_GLASGOW_7301$          UNION
         SELECT TOS FROM RSA_GUERNSEY_ICICI_1921P$  UNION
         SELECT TOS FROM RSA_HALIFAX_7301$          UNION 
         SELECT TOS FROM RSA_HORSHAM_1002$          UNION
         SELECT TOS FROM RSA_IPSWICH_1921$          UNION
         SELECT TOS FROM RSA_LEEDS_2911$            UNION
         SELECT TOS FROM RSA_LEICESTER_2951$        UNION
         SELECT TOS FROM RSA_LIVERPOOL_1002$        UNION
         SELECT TOS FROM RSA_MANCHESTER_1941$       UNION
         SELECT TOS FROM RSA_ONELONDON_1002         UNION
         SELECT TOS FROM RSA_PETERBOROUGH_3825      UNION
         SELECT TOS FROM RSA_REDHILL_2951           UNION
         SELECT TOS FROM RSA_SUNDERLAND_1941$
       ) AS REF

FULL OUTER JOIN RSA_BELFAST_1921            ON REF.TOS = RSA_BELFAST_1921.TOS
FULL OUTER JOIN RSA_BIRMINGHAM_1941$        ON REF.TOS = RSA_BIRMINGHAM_1941$.TOS
FULL OUTER JOIN RSA_CARDIFFREGUS_2911$      ON REF.TOS = RSA_CARDIFFREGUS_2911$.TOS
FULL OUTER JOIN RSA_CASTLEMEAD_1941$        ON REF.TOS = RSA_CASTLEMEAD_1941$.TOS
FULL OUTER JOIN RSA_CHELMSFORD_1941$        ON REF.TOS = RSA_CHELMSFORD_1941$.TOS
FULL OUTER JOIN RSA_EDNBGH_2951$            ON REF.TOS = RSA_EDNBGH_2951$.TOS
FULL OUTER JOIN RSA_EUROPE_IOM_DOUG_2911$   ON REF.TOS = RSA_EUROPE_IOM_DOUG_2911$.TOS
FULL OUTER JOIN RSA_GLASGOW_7301$           ON REF.TOS = RSA_GLASGOW_7301$.TOS
FULL OUTER JOIN RSA_GUERNSEY_ICICI_1921P$   ON REF.TOS = RSA_GUERNSEY_ICICI_1921P$.TOS
FULL OUTER JOIN RSA_HALIFAX_7301$           ON REF.TOS = RSA_HALIFAX_7301$.TOS
FULL OUTER JOIN RSA_HORSHAM_1002$           ON REF.TOS = RSA_HORSHAM_1002$.TOS
FULL OUTER JOIN RSA_IPSWICH_1921$           ON REF.TOS = RSA_IPSWICH_1921$.TOS
FULL OUTER JOIN RSA_LEEDS_2911$             ON REF.TOS = RSA_LEEDS_2911$.TOS
FULL OUTER JOIN RSA_LEICESTER_2951$         ON REF.TOS = RSA_LEICESTER_2951$.TOS
FULL OUTER JOIN RSA_LIVERPOOL_1002$         ON REF.TOS = RSA_LIVERPOOL_1002$.TOS
FULL OUTER JOIN RSA_MANCHESTER_1941$        ON REF.TOS = RSA_MANCHESTER_1941$.TOS
FULL OUTER JOIN RSA_ONELONDON_1002          ON REF.TOS = RSA_ONELONDON_1002.TOS
FULL OUTER JOIN RSA_PETERBOROUGH_3825       ON REF.TOS = RSA_PETERBOROUGH_3825.TOS
FULL OUTER JOIN RSA_REDHILL_2951            ON REF.TOS = RSA_REDHILL_2951.TOS
FULL OUTER JOIN RSA_SUNDERLAND_1941$        ON REF.TOS = RSA_SUNDERLAND_1941$.TOS

WHERE REF.TOS = 'ef (DSCP 46)' and [Report Date] = 'April 2016'
ORDER BY [TOS Value]

we started doing this last month and we only had 1 months worth of data so the query ran fine and produced the expected result which was:

TOS Value      Belfast    Birmingham    Report Date
ef (DSCP 46)    0.14%      1.91%          March 2016

but now that we have added the april data we get the ambiguous error!

help please! what is wrong with my query!? Also.... now that i have added the second month of data if i run the query without the monthly "WHERE" parameter the query produces like 52k rows of data when it should only be 1 row!

then choose any one of the table column report_date and check it — Chanukya, May 06 '16 at 10:43
That's the problem so. The column [Report Date] is "ambiguous" to SQL Server as it doesn't know from which table to use the column from. Put the alias you used in front of [Report Date] and it will tell SQL Server to use that column in that table. — Shaneis, May 06 '16 at 10:44
Move the WHERE clause conditions to the ON clauses to get true full outer join result. — jarlh, May 06 '16 at 10:44
But ambiguous will not happen when you simply add a new month of data (it's a parse time error), the query must have been modified. — dnoeth, May 06 '16 at 10:45
@VenkateshRangu could you show me an example if possible please? — Raul Gonzales, May 06 '16 at 10:46
Possible duplicate of [Query error with ambiguous column name in SQL](http://stackoverflow.com/questions/12662954/query-error-with-ambiguous-column-name-in-sql) — paparazzo, May 09 '16 at 07:41

score 5 · Accepted Answer · answered May 06 '16 at 10:45

5

Presumably, you need to include [Report Date] in all your joins. Otherwise, you are going to get Cartesian products and the results will not be correct.

Once you do this, you will need logic like:

COALESCE(t1.[Report Date], t2.[Report Date], . . .) = 'April 2016'

I will make a couple of comments:

You should use table aliases so the query is easier to write and to read.
Dates should be stored in an ISO standard format, such as YYYY-MM.
More importantly: The data structure looks way too complicated. It looks like similar data for different cities is in different tables. That is not the relational way to design databases.
The extensive use of full outer join also suggests a problem with the underlying data structure.

answered May 06 '16 at 10:45

Gordon Linoff

1,242,037
58
646
786

I agree with all your points as they make perfect sense... it is just that at work they assigned this task to me and i am a novice using SQL and had to create something that worked without even thinking about design and format. I know that your answer will work as your reputation presedes you but still dont know where to amend the query so that it will work. any change you can show me how the query should look please as i have a 3 hour deadline to meet? Thanks in advance! – Raul Gonzales May 06 '16 at 10:55
Need to add [Report Date] to Ref for the join so I don't think you need COALESCE. I would try and help but OP wants us to code for him. – paparazzo May 06 '16 at 14:04
@Paparazzi I would really really be greatful if you could help me as i have hit a wall and i dont know how to fix the issue although Gordon Lunoff annswer is the correct one but i just dont know how to put his suggestions into the query. – Raul Gonzales May 09 '16 at 06:24
@RaulGonzales If you don't know how to do it then how can you know it is correct? – paparazzo May 09 '16 at 07:41
@Paparazzi because the end result should be the 2 months of data that i have in the DB and now what i am getting is 500k rows of repeated data when it only should be 2 rows of data for the 2 months that i have. i just dont know how to phrase the query that Gordon Linoff suggested. i know... i am a total newby! – Raul Gonzales May 09 '16 at 07:45
@RaulGonzales You are a total newbie that has shown no effort. SO is not a code generation service. – paparazzo May 09 '16 at 07:51
@Paparazzi i have done my very best to make this query work but i have got nowhere and hence i posted it on SO. Like you.... i also have a degree in engineering so please dont tell me that i have shown no effort. thank you anyway. – Raul Gonzales May 09 '16 at 07:54

Ambiguous colum when selecting specific month data

1 Answers1