|
|
Posted by blueghost73@yahoo.com on 08/09/06 17:29
SQL lets you alias your field name, so that's the way to avoid
duplicates. If you have to pull SOPNUMBE from two different places,
just be sure to clearly define which is which, and give each one a
different alias. So if the field was in two tables called TABLE1 and
TABLE2, you could have something like this in your SELECT statement:
SELECT TABLE1.SOPNUMBE AS SOPNUMA, TABLE2.SOPNUMBE AS SOPNUMB ...
--Richard
jason.langdale@gmail.com wrote:
> 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@gmail.com (jason.langdale@gmail.com) 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@sommarskog.se
> >
> > 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
Navigation:
[Reply to this message]
|