April 23, 2003 at 1:16 am
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"
April 23, 2003 at 1:25 am
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"
April 23, 2003 at 1:28 am
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
April 23, 2003 at 1:51 am
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"
April 23, 2003 at 4:29 am
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.
April 23, 2003 at 2:51 pm
Very interesting!
Suppose the obvious solution is to flatten the inner most two sub queries.
April 23, 2003 at 7:24 pm
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"
April 24, 2003 at 3:27 am
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.
April 25, 2003 at 6:47 pm
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"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply