Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Cannot create view due to duplicate column name?!

5 posts in General Discussion (old) Last posting was on 2008-09-16 12:38:51.0Z
Markus KARG Posted on 2008-09-16 06:53:33.0Z
From: "Markus KARG" <karg@quipsy.de>
Newsgroups: sybase.public.sqlanywhere
Subject: Cannot create view due to duplicate column name?!
Lines: 18
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48cf57ed$1@forums-1-dub>
Date: 15 Sep 2008 23:53:33 -0700
X-Trace: forums-1-dub 1221548013 10.22.241.152 (15 Sep 2008 23:53:33 -0700)
X-Original-Trace: 15 Sep 2008 23:53:33 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:157
Article PK: 866604

I want to create a simple view:

CREATE VIEW X AS SELECT * FROM A LEFT JOIN B

SELECT * FROM A LEFT JOIN B works well, but the above create statement is
not: ISQL says that there is a duplicate column name (that is true: column C
is found in both, tables A and B). So I tried:

CREATE VIEW X AS SELECT A.*, B.* FROM A LEFT JOIN B

but that also says that the column C is already existing.

What to do?

Thanks
Markus


"Frank Ploessel" <fpl... Posted on 2008-09-16 08:42:59.0Z
Subject: Re: Cannot create view due to duplicate column name?!
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
References: <48cf57ed$1@forums-1-dub>
Content-Transfer-Encoding: 7bit
Message-ID: <op.uhkftydaj0bybf@bonw01164.internal.imsglobal.com>
User-Agent: Opera Mail/9.51 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 16 Sep 2008 01:42:59 -0700
X-Trace: forums-1-dub 1221554579 10.22.241.152 (16 Sep 2008 01:42:59 -0700)
X-Original-Trace: 16 Sep 2008 01:42:59 -0700, vip152.sybase.com
Lines: 45
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:158
Article PK: 866603

Markus,

A view needs unique column names for it's users to select from. In the
statement
SELECT * FROM A LEFT JOIN B
both column C can be referenced by prepending the table name, i. e. the
user of this statement can use A.C and B.C to tell which column to get teh
data for.
But when you define a view on this, how should the user of the view tell
the database if X.C means column C coming from A or column C coming from
B? X.A.C or X.B.C would be syntactically correct, but semantically
something completely different: This would be understood by the database
as X being the table or view owner, A and B the table or view name, and C
the column name.

Except for very rare cases, it is always a good idea to state the columns
in a view definition explicitly. If you do this, you can either leave out
the C from one side, or give one of the C columns an alias, and everything
is fine.

Frank


Am 16.09.2008, 08:53 Uhr, schrieb Markus KARG <karg@quipsy.de>:

> I want to create a simple view:
>
> CREATE VIEW X AS SELECT * FROM A LEFT JOIN B
>
> SELECT * FROM A LEFT JOIN B works well, but the above create statement is
> not: ISQL says that there is a duplicate column name (that is true:
> column C
> is found in both, tables A and B). So I tried:
>
> CREATE VIEW X AS SELECT A.*, B.* FROM A LEFT JOIN B
>
> but that also says that the column C is already existing.
>
> What to do?
>
> Thanks
> Markus
>
>


Markus KARG Posted on 2008-09-16 09:27:06.0Z
From: "Markus KARG" <karg@quipsy.de>
Newsgroups: sybase.public.sqlanywhere
References: <48cf57ed$1@forums-1-dub> <op.uhkftydaj0bybf@bonw01164.internal.imsglobal.com>
Subject: Re: Cannot create view due to duplicate column name?!
Lines: 58
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48cf7bea$1@forums-1-dub>
Date: 16 Sep 2008 02:27:06 -0700
X-Trace: forums-1-dub 1221557226 10.22.241.152 (16 Sep 2008 02:27:06 -0700)
X-Original-Trace: 16 Sep 2008 02:27:06 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:159
Article PK: 866605

Frank,

thanks a lot for this explanation. Now I understand the problem.
Unfortunately this means that I have to add 200+ columns now, instead of a
simple asterisk... :-(

Thanks
Markus

"Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> schrieb im Newsbeitrag
news:op.uhkftydaj0bybf@bonw01164.internal.imsglobal.com...

> Markus,
>
> A view needs unique column names for it's users to select from. In the
> statement
> SELECT * FROM A LEFT JOIN B
> both column C can be referenced by prepending the table name, i. e. the
> user of this statement can use A.C and B.C to tell which column to get teh
> data for.
> But when you define a view on this, how should the user of the view tell
> the database if X.C means column C coming from A or column C coming from
> B? X.A.C or X.B.C would be syntactically correct, but semantically
> something completely different: This would be understood by the database
> as X being the table or view owner, A and B the table or view name, and C
> the column name.
>
> Except for very rare cases, it is always a good idea to state the columns
> in a view definition explicitly. If you do this, you can either leave out
> the C from one side, or give one of the C columns an alias, and everything
> is fine.
>
> Frank
>
>
> Am 16.09.2008, 08:53 Uhr, schrieb Markus KARG <karg@quipsy.de>:
>
>> I want to create a simple view:
>>
>> CREATE VIEW X AS SELECT * FROM A LEFT JOIN B
>>
>> SELECT * FROM A LEFT JOIN B works well, but the above create statement is
>> not: ISQL says that there is a duplicate column name (that is true:
>> column C
>> is found in both, tables A and B). So I tried:
>>
>> CREATE VIEW X AS SELECT A.*, B.* FROM A LEFT JOIN B
>>
>> but that also says that the column C is already existing.
>>
>> What to do?
>>
>> Thanks
>> Markus
>>
>>
>


"Frank Ploessel" <fpl... Posted on 2008-09-16 12:36:00.0Z
Subject: Re: Cannot create view due to duplicate column name?!
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
References: <48cf57ed$1@forums-1-dub> <op.uhkftydaj0bybf@bonw01164.internal.imsglobal.com> <48cf7bea$1@forums-1-dub>
Content-Transfer-Encoding: 7bit
Message-ID: <op.uhkqmbihj0bybf@bonw01164.internal.imsglobal.com>
User-Agent: Opera Mail/9.51 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 16 Sep 2008 05:36:00 -0700
X-Trace: forums-1-dub 1221568560 10.22.241.152 (16 Sep 2008 05:36:00 -0700)
X-Original-Trace: 16 Sep 2008 05:36:00 -0700, vip152.sybase.com
Lines: 92
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:160
Article PK: 866607

Markus,

What about leaving this boring work to the computer which is better in
these things than humans? In this case, the database engine can support
you writing the SQL as follows:
If you execute

select list(tname || '.' || cname)
from sys.syscolumns
where tname in('A', 'B')

you would get a complete list of columns of both tables A and B as a
result, which you then can paste into the CREATE VIEW statement and adapt
as required.
You can obviously apply all types of where conditions in the above
statement, and the list statement also has an optional order by clause
which can be any expression based on syscolumns, as long as it is either
numeric or [n][var]char.

Hope this helps.

Frank

Am 16.09.2008, 11:27 Uhr, schrieb Markus KARG <karg@quipsy.de>:

> Frank,
>
> thanks a lot for this explanation. Now I understand the problem.
> Unfortunately this means that I have to add 200+ columns now, instead of
> a
> simple asterisk... :-(
>
> Thanks
> Markus
>
> "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> schrieb im
> Newsbeitrag
> news:op.uhkftydaj0bybf@bonw01164.internal.imsglobal.com...
>> Markus,
>>
>> A view needs unique column names for it's users to select from. In the
>> statement
>> SELECT * FROM A LEFT JOIN B
>> both column C can be referenced by prepending the table name, i. e. the
>> user of this statement can use A.C and B.C to tell which column to get
>> teh
>> data for.
>> But when you define a view on this, how should the user of the view tell
>> the database if X.C means column C coming from A or column C coming from
>> B? X.A.C or X.B.C would be syntactically correct, but semantically
>> something completely different: This would be understood by the database
>> as X being the table or view owner, A and B the table or view name, and
>> C
>> the column name.
>>
>> Except for very rare cases, it is always a good idea to state the
>> columns
>> in a view definition explicitly. If you do this, you can either leave
>> out
>> the C from one side, or give one of the C columns an alias, and
>> everything
>> is fine.
>>
>> Frank
>>
>>
>> Am 16.09.2008, 08:53 Uhr, schrieb Markus KARG <karg@quipsy.de>:
>>
>>> I want to create a simple view:
>>>
>>> CREATE VIEW X AS SELECT * FROM A LEFT JOIN B
>>>
>>> SELECT * FROM A LEFT JOIN B works well, but the above create statement
>>> is
>>> not: ISQL says that there is a duplicate column name (that is true:
>>> column C
>>> is found in both, tables A and B). So I tried:
>>>
>>> CREATE VIEW X AS SELECT A.*, B.* FROM A LEFT JOIN B
>>>
>>> but that also says that the column C is already existing.
>>>
>>> What to do?
>>>
>>> Thanks
>>> Markus
>>>
>>>
>>
>
>


Markus KARG Posted on 2008-09-16 12:38:51.0Z
From: "Markus KARG" <karg@quipsy.de>
Newsgroups: sybase.public.sqlanywhere
References: <48cf57ed$1@forums-1-dub> <op.uhkftydaj0bybf@bonw01164.internal.imsglobal.com> <48cf7bea$1@forums-1-dub> <op.uhkqmbihj0bybf@bonw01164.internal.imsglobal.com>
Subject: Re: Cannot create view due to duplicate column name?!
Lines: 98
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48cfa8db@forums-1-dub>
Date: 16 Sep 2008 05:38:51 -0700
X-Trace: forums-1-dub 1221568731 10.22.241.152 (16 Sep 2008 05:38:51 -0700)
X-Original-Trace: 16 Sep 2008 05:38:51 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:161
Article PK: 866606

COOL! :-)

"Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> schrieb im Newsbeitrag
news:op.uhkqmbihj0bybf@bonw01164.internal.imsglobal.com...

> Markus,
>
> What about leaving this boring work to the computer which is better in
> these things than humans? In this case, the database engine can support
> you writing the SQL as follows:
> If you execute
>
> select list(tname || '.' || cname)
> from sys.syscolumns
> where tname in('A', 'B')
>
> you would get a complete list of columns of both tables A and B as a
> result, which you then can paste into the CREATE VIEW statement and adapt
> as required.
> You can obviously apply all types of where conditions in the above
> statement, and the list statement also has an optional order by clause
> which can be any expression based on syscolumns, as long as it is either
> numeric or [n][var]char.
>
> Hope this helps.
>
> Frank
>
> Am 16.09.2008, 11:27 Uhr, schrieb Markus KARG <karg@quipsy.de>:
>
>> Frank,
>>
>> thanks a lot for this explanation. Now I understand the problem.
>> Unfortunately this means that I have to add 200+ columns now, instead of
>> a
>> simple asterisk... :-(
>>
>> Thanks
>> Markus
>>
>> "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> schrieb im
>> Newsbeitrag
>> news:op.uhkftydaj0bybf@bonw01164.internal.imsglobal.com...
>>> Markus,
>>>
>>> A view needs unique column names for it's users to select from. In the
>>> statement
>>> SELECT * FROM A LEFT JOIN B
>>> both column C can be referenced by prepending the table name, i. e. the
>>> user of this statement can use A.C and B.C to tell which column to get
>>> teh
>>> data for.
>>> But when you define a view on this, how should the user of the view tell
>>> the database if X.C means column C coming from A or column C coming from
>>> B? X.A.C or X.B.C would be syntactically correct, but semantically
>>> something completely different: This would be understood by the database
>>> as X being the table or view owner, A and B the table or view name, and
>>> C
>>> the column name.
>>>
>>> Except for very rare cases, it is always a good idea to state the
>>> columns
>>> in a view definition explicitly. If you do this, you can either leave
>>> out
>>> the C from one side, or give one of the C columns an alias, and
>>> everything
>>> is fine.
>>>
>>> Frank
>>>
>>>
>>> Am 16.09.2008, 08:53 Uhr, schrieb Markus KARG <karg@quipsy.de>:
>>>
>>>> I want to create a simple view:
>>>>
>>>> CREATE VIEW X AS SELECT * FROM A LEFT JOIN B
>>>>
>>>> SELECT * FROM A LEFT JOIN B works well, but the above create statement
>>>> is
>>>> not: ISQL says that there is a duplicate column name (that is true:
>>>> column C
>>>> is found in both, tables A and B). So I tried:
>>>>
>>>> CREATE VIEW X AS SELECT A.*, B.* FROM A LEFT JOIN B
>>>>
>>>> but that also says that the column C is already existing.
>>>>
>>>> What to do?
>>>>
>>>> Thanks
>>>> Markus
>>>>
>>>>
>>>
>>
>>
>