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.

Issue with correlated columns if correlated with 2 or more level up parent.

2 posts in General Discussion Last posting was on 2009-10-29 15:01:50.0Z
Avadhoot Kulkarni Posted on 2009-10-29 07:19:23.0Z
Sender: 432c.4ae93f28.1804289383@sybase.com
From: Avadhoot Kulkarni
Newsgroups: sybase.public.ase.general
Subject: Issue with correlated columns if correlated with 2 or more level up parent.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ae941fb.437c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Oct 2009 23:19:23 -0800
X-Trace: forums-1-dub 1256800763 10.22.241.41 (28 Oct 2009 23:19:23 -0800)
X-Original-Trace: 28 Oct 2009 23:19:23 -0800, 10.22.241.41
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28566
Article PK: 77807

I tried to use following dynamically generated query by some
automated tool on Sybase ASE 15.0.1 (windows).

SELECT
"CustomerID"=[Project1].[CustomerID],
"C2"=(SELECT
Count([Filter1].[A1]) AS [A1]
FROM ( SELECT
[Extent3].[OrderID] AS [A1]
FROM [Schema].[Orders] AS [Extent3]
WHERE [Project1].[CustomerID]=[Extent3].[ClientID]
) AS [Filter1]
)
FROM
[Schema].[Customers] AS [Project1]

As I am trying to correlate a column [Project1].[CustomerID]
with the immediate but one parent from clause alias.

It generated following error.
The column prefix 'Project1' does not match with a table
name or alias name used in the query

Is this an known issue? Is there any work around?


"Mark A. Parsons" <iron_horse Posted on 2009-10-29 15:01:50.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Issue with correlated columns if correlated with 2 or more level up parent.
References: <4ae941fb.437c.1681692777@sybase.com>
In-Reply-To: <4ae941fb.437c.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091014-0, 10/14/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ae9ae5e$1@forums-1-dub>
Date: 29 Oct 2009 07:01:50 -0800
X-Trace: forums-1-dub 1256828510 10.22.241.152 (29 Oct 2009 07:01:50 -0800)
X-Original-Trace: 29 Oct 2009 07:01:50 -0800, vip152.sybase.com
Lines: 77
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28568
Article PK: 77811

What you're seeing is expected behaviour, ie, your coding method is invalid.

See the ASE 15.0.2, Transact-SQL Users Guide, Chapter 9: SQL Derived Tables, subsection: Correlated SQL derived tables.

NOTE: The referenced section of the manuals is actually a copy from the ASE 12.5.1 Transact-SQL Users Guide, Chapter 5;
which in turn also shows up as ASE 15.0 Transact-SQL Users Guide, Chapter 9. All 3 manuals state the same thing, ie,
what you're seeing is expected behaviour.

-----------

As for workarounds ... sure, code the query differently so that it meets T-SQL coding standards/requirements.

Perhaps I'm missing a subtlety of your logic, but it looks like you want a count of orders (Orders.OrderID) broken down
by customer (CustomerID).

Some (more direct) queries that come to mind:

===============================

-- for Customers with at least 1 order

select Customers.CustomerID,
count(Orders.OrderID) as C2
from Customers,
Orders
where Customers.CustomerID = Orders.ClientID
group by Customers.CustomerID

===============================

-- for all Customers

select Customers.CustomerID,
count(Orders.OrderID) as C2
from Customers,
Orders
where Customers.CustomerID *= Orders.ClientID
group by Customers.CustomerID

===============================

-- for all Customers

select Customers.CustomerID,
C2 = (select count(Orders.OrderID)
from Orders
where Customers.CustomerID = Orders.ClientID)
from Customers
===============================

Avadhoot Kulkarni wrote:
> I tried to use following dynamically generated query by some
> automated tool on Sybase ASE 15.0.1 (windows).
>
> SELECT
> "CustomerID"=[Project1].[CustomerID],
> "C2"=(SELECT
> Count([Filter1].[A1]) AS [A1]
> FROM ( SELECT
> [Extent3].[OrderID] AS [A1]
> FROM [Schema].[Orders] AS [Extent3]
> WHERE [Project1].[CustomerID]=[Extent3].[ClientID]
> ) AS [Filter1]
> )
> FROM
> [Schema].[Customers] AS [Project1]
>
> As I am trying to correlate a column [Project1].[CustomerID]
> with the immediate but one parent from clause alias.
>
> It generated following error.
> The column prefix 'Project1' does not match with a table
> name or alias name used in the query
>
> Is this an known issue? Is there any work around?