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.

How to use derived table fields

9 posts in General Discussion Last posting was on 2011-04-17 23:07:52.0Z
kennedyc Posted on 2011-04-17 12:50:34.0Z
Sender: 4b6f.4da9c4c1.846930886@sybase.com
From: kennedyc
Newsgroups: sybase.public.ase.general
Subject: How to use derived table fields
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4daae21a.787.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Apr 2011 05:50:34 -0700
X-Trace: forums-1-dub 1303044634 10.22.241.41 (17 Apr 2011 05:50:34 -0700)
X-Original-Trace: 17 Apr 2011 05:50:34 -0700, 10.22.241.41
Lines: 25
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30116
Article PK: 79348

Hello,

below is the sql statement

select x.name
from #T a
join #T b on a.id = b.id,
(
select a.name
UNION all
select b.name
) x

When execute the query, I get the following error message:
The column prefix 'b' does not match with a table name or
alias name
used in the query. Either the table is not specified in the
FROM clause
or it has a correlation name which must be used instead.

How can I fix the error to use the derived table field?

any help is appreciated.

thanks.


"Mark A. Parsons" <iron_horse Posted on 2011-04-17 13:12:04.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to use derived table fields
References: <4daae21a.787.1681692777@sybase.com>
In-Reply-To: <4daae21a.787.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4daae724$1@forums-1-dub>
Date: 17 Apr 2011 06:12:04 -0700
X-Trace: forums-1-dub 1303045924 10.22.241.152 (17 Apr 2011 06:12:04 -0700)
X-Original-Trace: 17 Apr 2011 06:12:04 -0700, vip152.sybase.com
Lines: 55
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30119
Article PK: 79345

The derived table definition cannot reference any tables outside the scope of the derived table (ie, the derived table
cannot reference anything outside of the parenthesis). So you're getting a syntax error because the derived table
definition ...

===================
(
select a.name
UNION all
select b.name
) x
===================

... does not contain information on what 'a' and 'b' reference.

To 'fix' your query is going to depend on what exactly it is you're trying to accomplish ... and it's not clear what
exactly you're trying to accomplish ...

If you have multiple rows with the same id value then you're going to end up with a bit of a cartesian product (for all
rows with the same id) ... which would cause a volume of duplicate names in the result set ... which is where I'm
assuming the UNION all comes into play (ie, to remove the duplicates).

It seems to me you're looking for a list of distinct name's from #T, but I'm not sure why you're joining the table to
itself based on the same column (a.id = b.id). I'm thinking a simpler query would provide a distinct list of name's, eg:

==================
select distinct name from #T
==================

On 04/17/2011 08:50, kennedyc wrote:
> Hello,
>
> below is the sql statement
>
> select x.name
> from #T a
> join #T b on a.id = b.id,
> (
> select a.name
> UNION all
> select b.name
> ) x
>
> When execute the query, I get the following error message:
> The column prefix 'b' does not match with a table name or
> alias name
> used in the query. Either the table is not specified in the
> FROM clause
> or it has a correlation name which must be used instead.
>
> How can I fix the error to use the derived table field?
>
> any help is appreciated.
>
> thanks.


kennedyc Posted on 2011-04-17 14:06:28.0Z
Sender: 4b6f.4da9c4c1.846930886@sybase.com
From: kennedyc
Newsgroups: sybase.public.ase.general
Subject: Re: How to use derived table fields
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4daaf3e4.8f4.1681692777@sybase.com>
References: <4daae724$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Apr 2011 07:06:28 -0700
X-Trace: forums-1-dub 1303049188 10.22.241.41 (17 Apr 2011 07:06:28 -0700)
X-Original-Trace: 17 Apr 2011 07:06:28 -0700, 10.22.241.41
Lines: 127
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30122
Article PK: 79351

Hi Mark A,

Thanks for you reply, but my problem is a bit complicated
than that.
What I want to do is to refer the derived table fields from
the previous join.
Please let me explain more clear, actually I'm writing some
T-SQL to fix a combination problem to explore the
combination of the cards from three bags (A,B,C).
The problem is ASE doesn't support cross apply as SQL
anywhere, so I'm finding an alternative to refer the derived
table fields from the join.

any help is appreciated.

thanks alot.


The code as below:

create table #T(
bag char(1) NOT NULL,
card_no integer NOT NULL,
PRIMARY KEY (bag, card_no)
);

INSERT INTO #T
SELECT 'A', 1 UNION ALL
SELECT 'A', 2 UNION ALL
SELECT 'B', 1 UNION ALL
SELECT 'B', 2 UNION ALL
SELECT 'C', 1;

select x.bag,x.card_no
from a
join b on a.bag = 'A' and b.bag = 'B'
join c on c.bag = 'C'
cross apply
(
select a.bag,a.card_no <- The key of this code is to refer
the derived table field from the previous join
union all
select b.bag,b.card_no
union all
select c.bag,c.card_no
) as x

/*====== Output Result shows all combination=========*/
combination_no Person Pool ball
1 A 1
1 B 1
1 C 1
2 A 1
2 B 2
2 C 1
3 A 2
3 B 1
3 C 1
4 A 2
4 B 2
4 C 1

> The derived table definition cannot reference any tables
> outside the scope of the derived table (ie, the derived
> table cannot reference anything outside of the
> parenthesis). So you're getting a syntax error because
> the derived table definition ...
>
> ===================
> (
> select a.name
> UNION all
> select b.name
> ) x
> ===================
>
> ... does not contain information on what 'a' and 'b'
> reference.
>
> To 'fix' your query is going to depend on what exactly it
> is you're trying to accomplish ... and it's not clear what
> exactly you're trying to accomplish ...
>
> If you have multiple rows with the same id value then
> you're going to end up with a bit of a cartesian product
> (for all rows with the same id) ... which would cause a
> volume of duplicate names in the result set ... which is
> where I'm assuming the UNION all comes into play (ie, to
> remove the duplicates).
>
> It seems to me you're looking for a list of distinct
> name's from #T, but I'm not sure why you're joining the
> table to itself based on the same column (a.id = b.id).
> I'm thinking a simpler query would provide a distinct list
> of name's, eg:
>
> ==================
> select distinct name from #T
> ==================
>
>
> On 04/17/2011 08:50, kennedyc wrote:
> > Hello,
> >
> > below is the sql statement
> >
> > select x.name
> > from #T a
> > join #T b on a.id = b.id,
> > (
> > select a.name
> > UNION all
> > select b.name
> > ) x
> >
> > When execute the query, I get the following error
> > message: The column prefix 'b' does not match with a
> > table name or alias name
> > used in the query. Either the table is not specified in
> > the FROM clause
> > or it has a correlation name which must be used instead.
> >
> > How can I fix the error to use the derived table field?
> >
> > any help is appreciated.
> >
> > thanks.


"Mark A. Parsons" <iron_horse Posted on 2011-04-17 14:47:21.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to use derived table fields
References: <4daae724$1@forums-1-dub> <4daaf3e4.8f4.1681692777@sybase.com>
In-Reply-To: <4daaf3e4.8f4.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4daafd79$1@forums-1-dub>
Date: 17 Apr 2011 07:47:21 -0700
X-Trace: forums-1-dub 1303051641 10.22.241.152 (17 Apr 2011 07:47:21 -0700)
X-Original-Trace: 17 Apr 2011 07:47:21 -0700, vip152.sybase.com
Lines: 131
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30123
Article PK: 79354

Could you update/fix your example ... it's not clear how you get from your a/b/c/x query (2 columns in output named
'bag' and 'card_no') to the final result set (3 columns in output named 'combination_no', 'Person' and 'Pool ball').

On 04/17/2011 10:06, kennedyc wrote:
> Hi Mark A,
>
> Thanks for you reply, but my problem is a bit complicated
> than that.
> What I want to do is to refer the derived table fields from
> the previous join.
> Please let me explain more clear, actually I'm writing some
> T-SQL to fix a combination problem to explore the
> combination of the cards from three bags (A,B,C).
> The problem is ASE doesn't support cross apply as SQL
> anywhere, so I'm finding an alternative to refer the derived
> table fields from the join.
>
> any help is appreciated.
>
> thanks alot.
>
>
> The code as below:
>
> create table #T(
> bag char(1) NOT NULL,
> card_no integer NOT NULL,
> PRIMARY KEY (bag, card_no)
> );
>
> INSERT INTO #T
> SELECT 'A', 1 UNION ALL
> SELECT 'A', 2 UNION ALL
> SELECT 'B', 1 UNION ALL
> SELECT 'B', 2 UNION ALL
> SELECT 'C', 1;
>
> select x.bag,x.card_no
> from a
> join b on a.bag = 'A' and b.bag = 'B'
> join c on c.bag = 'C'
> cross apply
> (
> select a.bag,a.card_no<- The key of this code is to refer
> the derived table field from the previous join
> union all
> select b.bag,b.card_no
> union all
> select c.bag,c.card_no
> ) as x
>
> /*====== Output Result shows all combination=========*/
> combination_no Person Pool ball
> 1 A 1
> 1 B 1
> 1 C 1
> 2 A 1
> 2 B 2
> 2 C 1
> 3 A 2
> 3 B 1
> 3 C 1
> 4 A 2
> 4 B 2
> 4 C 1
>
>> The derived table definition cannot reference any tables
>> outside the scope of the derived table (ie, the derived
>> table cannot reference anything outside of the
>> parenthesis). So you're getting a syntax error because
>> the derived table definition ...
>>
>> ===================
>> (
>> select a.name
>> UNION all
>> select b.name
>> ) x
>> ===================
>>
>> ... does not contain information on what 'a' and 'b'
>> reference.
>>
>> To 'fix' your query is going to depend on what exactly it
>> is you're trying to accomplish ... and it's not clear what
>> exactly you're trying to accomplish ...
>>
>> If you have multiple rows with the same id value then
>> you're going to end up with a bit of a cartesian product
>> (for all rows with the same id) ... which would cause a
>> volume of duplicate names in the result set ... which is
>> where I'm assuming the UNION all comes into play (ie, to
>> remove the duplicates).
>>
>> It seems to me you're looking for a list of distinct
>> name's from #T, but I'm not sure why you're joining the
>> table to itself based on the same column (a.id = b.id).
>> I'm thinking a simpler query would provide a distinct list
>> of name's, eg:
>>
>> ==================
>> select distinct name from #T
>> ==================
>>
>>
>> On 04/17/2011 08:50, kennedyc wrote:
>>> Hello,
>>>
>>> below is the sql statement
>>>
>>> select x.name
>>> from #T a
>>> join #T b on a.id = b.id,
>>> (
>>> select a.name
>>> UNION all
>>> select b.name
>>> ) x
>>>
>>> When execute the query, I get the following error
>>> message: The column prefix 'b' does not match with a
>>> table name or alias name
>>> used in the query. Either the table is not specified in
>>> the FROM clause
>>> or it has a correlation name which must be used instead.
>>>
>>> How can I fix the error to use the derived table field?
>>>
>>> any help is appreciated.
>>>
>>> thanks.


Rob V [ Sybase ] Posted on 2011-04-17 13:11:00.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to use derived table fields
References: <4daae21a.787.1681692777@sybase.com>
In-Reply-To: <4daae21a.787.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4daae6e4$1@forums-1-dub>
Date: 17 Apr 2011 06:11:00 -0700
X-Trace: forums-1-dub 1303045860 10.22.241.152 (17 Apr 2011 06:11:00 -0700)
X-Original-Trace: 17 Apr 2011 06:11:00 -0700, vip152.sybase.com
Lines: 63
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30118
Article PK: 79350

The syntax in the derived table is invalid: the statement 'select
a.name' if wrong: there must be a FROM clause, which is missing. Same
for 'select 'b.name' -- that the error msg is only for 'b' is basically
coincidental (try reversing the order of the two selects and I predict
you'll see the same error but then for 'a').

A derived table needs to consist of a select statement that would be
valid by itself as well, and that's the problem here. WHen there's a
union, the same applies: it should also be valid as a query on its own.
Both the following examples are correct:

select * from (select a=1 union select b=2) x

select * from (select a.name from a union select b.name from b) x

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------

On 17-Apr-2011 14:50, kennedyc wrote:
> Hello,
>
> below is the sql statement
>
> select x.name
> from #T a
> join #T b on a.id = b.id,
> (
> select a.name
> UNION all
> select b.name
> ) x
>
> When execute the query, I get the following error message:
> The column prefix 'b' does not match with a table name or
> alias name
> used in the query. Either the table is not specified in the
> FROM clause
> or it has a correlation name which must be used instead.
>
> How can I fix the error to use the derived table field?
>
> any help is appreciated.
>
> thanks.