Re: Using Union and Join in a single view

Yes I did read the error message and it's as clear to me as it is to
you. What is not clear to me is how to remedy it. Unfortunately, the
same data is not contained in these two columns either. I don't know if
anyone here knows anything about Microsoft Dynamics GP, which is where
these tables are coming from, but all three tables contain the same
SOPNUMBE column but the rows in each table are unique. One his a
historical table the other two are god knows what. Since on of these
tables does not have all the fields I want to select, I can't do a
simple union; which would be ideal for me. Maybe my entire approach is
wrong. Have you got any ideas?


Erland Sommarskog wrote:
jason.langdale@xxxxxxxxx (jason.langdale@xxxxxxxxx) writes:
Thanks for your help. I have rewritten my query but I get an error. It
might be a basic error but I am a Crystal report designer who suddenly
has to create his own views. Please let me know what you think :)

ERROR:
Server: Msg 8156, Level 16, State 1, Procedure historic_sales_2, Line 2
The column 'SOPNUMBE' was specified multiple times for
'historic_sales_2'.

Did you actually try to read the error message? :-) Yes, I realise that
you are not an SQL Server guy, but an error message cannot be much clearer
than this. The column names in a view must be unique, and you have two
columns called SOPNUMBE. Since you join over these column, they obviously
have the same contents, so why include them twice.

By the way, it looks to me that you have a different number of columns in
the two SELECT. That is not going to work out well.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: New Books Online Update Available.
    ... The exact error message you get. ... The version of SQL Server on the machine. ... This version of the Books Online will ... On the Download Center page, ...
    (microsoft.public.sqlserver.server)
  • Re: Connecting to a sql server database
    ... You are trying to use integrated security in your app (as is shown in the ... line numbered code where the connection string is shown). ... sql server, hence integrated security fails. ... > causing the error message and what the correction for this. ...
    (microsoft.public.dotnet.general)
  • Re: Invalid OLEVERB structure error 80040000 on DTS job
    ... >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... >- I copied this DTS package from a 32 bit Windows Server 2003 system. ... >I would only get that error message in the Job History logs though. ...
    (microsoft.public.sqlserver.dts)
  • Re: Please Help with SQL Query Problem
    ... Since you did not post any sample data, ... it - make yourself a favour and post the error message next time. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Using Union and Join in a single view
    ... Did you actually try to read the error message? ... columns called SOPNUMBE. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
Loading