Strange Query Error

Question

Post reply

Strange Query Error

ckempste

SSCoach

Points: 17983
More actions
April 23, 2003 at 1:16 am

#81465

Hi
I have a very strange problem that I am perplexed with whats going on. The query is below, as a VIEW it works perfectly, returning 98k rows, BUT, in
query analyser, this query gives me the error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'organisation' does not match with a table name or alias name used in the query.
The error is occuring around the statement:
(
SELECT TOP 1 region_id
FROM (
SELECT distinct a.*, r.region_id, r.region_description
FROM(
SELECTaddress_postcode As AddressPostCode
FROM Address a
WHERE organisation.org_id = a.address_org_id
ANDa.address_addresstype_id = 1
) As a,
Region r,
PostCode p
WHEREa.AddressPostCode = p.postcode
ANDr.region_id = p.postcode_region
) As pc
) As ContractorRegion
Here is the query in its entirity:
SELECT organisation.*,
(SELECT LTRIM(RTRIM(orgname_name))
FROM Organisation_name
WHERE orgname_org_id = organisation.org_id AND orgname_current_ind = 1 AND orgname_nametype_id = 1) AS LegalName,
(SELECT LTRIM(RTRIM(orgname_name))
FROM Organisation_name
WHERE orgname_org_id = organisation.org_id AND orgname_current_ind = 1 AND orgname_nametype_id = 2) AS TradingName,
(select emptype_description from employer_type WHERE emptype_id = org_emptype_id) As EmployerTypeDesc,
(
SELECT TOP 1 region_id
FROM (
SELECT distinct a.*, r.region_id, r.region_description
FROM(
SELECTaddress_postcode As AddressPostCode
FROM Address a
WHERE organisation.org_id = a.address_org_id
ANDa.address_addresstype_id = 1
) As a,
Region r,
PostCode p
WHEREa.AddressPostCode = p.postcode
ANDr.region_id = p.postcode_region
) As pc
) As ContractorRegion,
an.anzsic_parent_code AS ANZSICCodeRoot,
anparent.anzsic_desc AS ANZSICCodeRootDescription
FROM dbo.Organisation
LEFT OUTER JOIN ANZSIC an
ON an.anzsic_code = LEFT(organisation.org_anzsic_code, 2)
LEFT OUTER JOIN ANZSIC anparent
ON anparent.anzsic_code = an.anzsic_parent_code

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ckempste SSCoach Points: 17983 More actions · Answer 1

Hi all

Following on with this..

If you wrapper the SQL in create view statement, it creates and runs perfectly.

If you wrapper the SQL in create procedure statement, it fails with the same error.

If you use EM and the GUI to edit the view, and hit the ! (run) option, it fails, BUT the ALTER VIEW will work perfectly.

Weird.

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 2

I think the problem is that in the statement

SELECT organisation.*,

(SELECT LTRIM(RTRIM(orgname_name))

FROM Organisation_name

you need to change this to:

SELECT organisation.*,

(SELECT LTRIM(RTRIM(orgname_name))

FROM Organisation_name as organisation

Jeremy

ckempste SSCoach Points: 17983 More actions · Answer 3

Hi all

Here is another very basic test:

-- 3 levels (FAILS, BUT WORKS IN A CREATE VIEW statement and running it)

select

(select org_id

from

(select org_id

from (select org_id

from organisation lvl3 where lvl0.org_id = lvl3.org_id) as lvl2

where lvl0.org_id = lvl2.org_id) as lvl1

where lvl0.org_id = lvl1.org_id) as aa

from

organisation lvl0

where lvl0.org_id = 2

select * from cktestyeh

-- 2 levels (works perfectly with or without a view)

select

(select org_id

from

(select org_id

from organisation lvl2

where lvl0.org_id = lvl2.org_id) as lvl1

where lvl0.org_id = lvl1.org_id

) as aa

from

organisation lvl0

where lvl0.org_id = 2

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Tested myself. Apparently there is a limitation within subqueries to cascade inward.

Testing myself it seems at level 3 it cannot see any of the alias before itself, even level 2.

The reason it works right within a Create View or Alter View is however odd but it may be building interanl dependencies differently is my only guess. Might try an MS SQL newsgroup that is run by MS to see what they say.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 5

Very interesting!

Suppose the obvious solution is to flatten the inner most two sub queries.

ckempste SSCoach Points: 17983 More actions · Answer 6

Hi Guys

Whats really strange is that the developer swears black and blue that he initially created select via query analyser with no issues, the only change since that faithful day was:

a) Win 2k sp3 applied

b) SQL Server 2k sp3 applied

c) SOAP Toolkit 3.0 uninstalled, 2.0SP1 installed, the 3.0 re-installed

I will try the MS newsgroup.

Cheers

Ck

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

Antares686 SSC Guru Points: 125444 More actions · Answer 7

This

quote:
SOAP Toolkit 3.0 uninstalled, 2.0SP1 installed, the 3.0 re-installed

never happend on my machine but I am at 2K SP3 but I tested against 7 SP4 as well with the same results.

ckempste SSCoach Points: 17983 More actions · Answer 8

Hi all

Microsoft has assigned bug #: 469546 to it for SS2k

This may be fixed in Yukon, doesnt get the error apparently, but returns the incorrect results.

Cheers

Ck

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"