Imbedded subroutine prefix error

I am trying to run this routine which works fine until I put the outside
Select in. The function consists of 4 Select statements that I need to do
to get counts of various comparisons in my tables. The problem is that the
inside PositionID has to refer to the outside PositionID and that is where I
get the error ("WHERE p3.PositionID = p.PositionID ").
How do I get it to refer this table (p).
SELECT c. CompanyID,PositionID,JobTitleShort,Poste
dData =
replace(convert(varchar,p.DateCreated,6),' ',''),
Qualified = (
SELECT Count(*)
FROM
(
SELECT
Cat1,Value1,Cat2,Value2,Cat3,Value3,Cat4
,Value4,Cat5,Value5,Cat6,Value6,Cat7
,Value7,
Cat8,Value8,TotalCats=Cat1+Cat2+Cat3+Cat
4+Cat5+Cat6+Cat7+Cat8,
TotalValues =
Value1+Value2+Value3+Value4+Value5+Value
6+Value7+Value8,
CriteriaStatus = CASE WHEN
(Cat1+Cat2+Cat3+Cat4+Cat5+Cat6+Cat7+Cat8
) =
(Value1+Value2+Value3+Value4+Value5+Valu
e6+Value7+Value8) THEN 'All'
WHEN
(Value1+Value2+Value3+Value4+Value5+Valu
e6+Value7+Value8) >=
((Cat1+Cat2+Cat3+Cat4+Cat5+Cat6+Cat7+Cat
8)/2) THEN 'Most'
ELSE 'Fails' END
FROM
(
SELECT Cat1=Case when OvertimeRequired = 1 then 1 else 0 end,
Value1=Case when OvertimeRequired = 1 then (Case when
WorkOvertime = 1 then 1 else 0 end) else 0 end,
Cat2=Case when SponserNonUS = 0 then 1 else 0 end,
Value2=Case when SponserNonUS = 0 then (Case when m.USCitizen
= 0 then 0 else 1 end) else 0 end,
Cat3=Case when p3.JobDistance > 0 then 1 else 0 end,
Value3 = Case when p3.JobDistance > 0 then (Case when
dbo.GetDistance(ZipCode,m.Zip) <= p3.JobDistance then 1 else 0 end) else 0
end,
Cat4=Case when p3.EducationLevel is not null then 1 else 0
end,
Value4=Case when p3.EducationLevel is not null then (Case
when m.EducationLevel >= p3.EducationLevel then 1 else 0 end) else 0 end,
Cat5=Case when p3.CareerLevel is not null then 1 else 0 end,
Value5=Case when p3.CareerLevel is not null then (Case when
m.CareerLevel >= p3.CareerLevel then 1 else 0 end) else 0 end,
Cat6=Case when p3.ExperienceLevel is not null then 1 else 0
end,
Value6=Case when p3.ExperienceLevel is not null then (Case
when m.ExperienceLevel >= p3.ExperienceLevel then 1 else 0 end) else 0 end,
Cat7=Case when p3.ScreenTestRequired = 1 then 1 else 0 end,
Value7=Case when p3.ScreenTestRequired = 1 then
(Case when ScreenTestScore >= p3.NotifyScreenMinScore then
1 else 0 end) else 0 end,
Cat8=Case when p3.SkillsTestRequired = 1 and
SkillsTestOffered is not null then 1 else 0 end,
Value8=Case when p3.SkillsTestRequired = 1 and
SkillsTestOffered is not null then
(Case when SkillsTestScore >= p3.NotifySkillsMinScore then
1 else 0 end) else 0 end
FROM applicant a
JOIN logon l on (a.UserID = l.UserID)
JOIN Position p3 on (a.PositionID = p3.PositionID)
JOIN ApplicantResume ar on (ar.ApplicantID = a.ApplicantID)
LEFT JOIN ApplicantPosition ap on (ap.ApplicantID =
a.ApplicantID)
LEFT JOIN MyInfo m on (m.UserID = a.UserID)
WHERE p3.PositionID = p.PositionID
) as a1
) as a2
WHERE CriteriaStatus = 'Most')
FROM position p
JOIN Companies c on (c.CompanyID = p.CompanyID)
The error I get is:
The column prefix 'p' does not match with a table name or alias name
used in the query.
Thanks,
Tomtshad (tfs@.dslextreme.com) writes:
> I am trying to run this routine which works fine until I put the outside
> Select in. The function consists of 4 Select statements that I need to
> do to get counts of various comparisons in my tables. The problem is
> that the inside PositionID has to refer to the outside PositionID and
> that is where I get the error ("WHERE p3.PositionID = p.PositionID ").
> How do I get it to refer this table (p).
Looking at your query, I will have to say that there is a whole lot of
fuzz just to get a COUNT(*). You must be able to simplify this. And maybe
even to the point you don't need to nest any derived tables.
Also, my experience is that subqueries in the SELECT list are often
expensive. Try to move the derived table to the FROM clause so you get
something like:
SELECT c.CompanyID, ... Qualified = d.cnt
FROM Positions p
JOIN Companies c ON ...
JOIN (SELECT position, cnt = COUNT(*)
FROM FROM applicant a
JOIN logon l on (a.UserID = l.UserID)
JOIN Position p3 on (a.PositionID = p3.PositionID)
JOIN ApplicantResume ar on (ar.ApplicantID = a.ApplicantID)
LEFT JOIN ApplicantPosition ap
on (ap.ApplicantID = a.ApplicantID)
LEFT JOIN MyInfo m on (m.UserID = a.UserID)
GROUP BY position) AS d ON p ON d.position = p.position
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm not sure if yours would work or not. I am just starting to look at it,
but I wasn't just trying to get the count().
The inside query is putting together a list of criteria and whether they
were met or not.
I then need to total the number of criteria and whether the user has met
each criteria or not (1=yes and 0= no). I use this total to tell whether
the user has met the criteria, met most or fails to meet them.
This is used in various reports. I now have a report that just gives me a
total number of users that have passed the criteria for each position. This
is why I need the inside querie to refer to the outside PositionID, but I
get an error on it. I have to do the inside 2 queries first to find out
whether users have passed or not.
It may not be the best way, but this was what I came up with a couple of
months ago on this group to make this work.
Thanks,
Tom
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9743B83E6219Yazorman@.127.0.0.1...
> tshad (tfs@.dslextreme.com) writes:
> Looking at your query, I will have to say that there is a whole lot of
> fuzz just to get a COUNT(*). You must be able to simplify this. And maybe
> even to the point you don't need to nest any derived tables.
> Also, my experience is that subqueries in the SELECT list are often
> expensive. Try to move the derived table to the FROM clause so you get
> something like:
> SELECT c.CompanyID, ... Qualified = d.cnt
> FROM Positions p
> JOIN Companies c ON ...
> JOIN (SELECT position, cnt = COUNT(*)
> FROM FROM applicant a
> JOIN logon l on (a.UserID = l.UserID)
> JOIN Position p3 on (a.PositionID = p3.PositionID)
> JOIN ApplicantResume ar on (ar.ApplicantID = a.ApplicantID)
> LEFT JOIN ApplicantPosition ap
> on (ap.ApplicantID = a.ApplicantID)
> LEFT JOIN MyInfo m on (m.UserID = a.UserID)
> GROUP BY position) AS d ON p ON d.position = p.position
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||tshad (tscheiderich@.ftsolutions.com) writes:
> I'm not sure if yours would work or not. I am just starting to look at
> it, but I wasn't just trying to get the count().
Ah, I see now that the outermost table had a WHERE clause. Still, all
the columns that comes before the definition of Criteria_status, has
no actual use in the query. (But I can understand that they are good
for debug.)
I can't say for sure that the outline that I gave will work for you,
as I don't have tables, nor sample data to test with (hint, hint!),
but I would encourage you to study the possibility.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97444A54ACE5Yazorman@.127.0.0.1...
> tshad (tscheiderich@.ftsolutions.com) writes:
> Ah, I see now that the outermost table had a WHERE clause. Still, all
> the columns that comes before the definition of Criteria_status, has
> no actual use in the query. (But I can understand that they are good
> for debug.)
>
Actually, I would be getting multiple records with Criteria_status equal to
either "All", "Most" or "Fail". But I am only interested in the ones that
are equal to "Most". But I have to read all the records and calculate the
Criterias and Values before I know if they fail or not.
I then need to filter out the "Most" ones and then count them.

> I can't say for sure that the outline that I gave will work for you,
> as I don't have tables, nor sample data to test with (hint, hint!),
> but I would encourage you to study the possibility.
I will.
But why was I getting the error?
And how can I get it work?
I have done subqueries before where the inside query references the outside
query. But I can't figure out why this one won't work.
Thanks,
Tom
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi tshad
I think I see what you're trying to do: in your set A2 you want to get
a count of rows BY PositionID, like so:
Position ID CountRows
1 6
2 3
3 10
etc.
Is this right?
To do this, you need to return a rowcount AND the position ID from A2 -
you can then join A2 to your other tables (position and Companies) on
PositionID, so that A2 doesn't have to try to refer to a column outside
itself. Instead of
SELECT Count(*) FROM (lots of SQL) AS A2
use
SELECT PositionID,Count(*) FROM (lots of SQL) AS A2 GROUP BY Position
ID.
Make this statement with the GROUP BY into a separate subquery, and
join it to the two other tables which you've put at the end:
SELECT c.CompanyID,p.[nb: qualified because there's now 2 PositionID
columns in the outer set]PositionID,JobTitleShort,PostedData =
replace(convert(varchar,p.DateCreated,6),' ',''),
totals.RowCount AS Qualified FROM
(SELECT PositionID,Count(*) FROM (lots of SQL) AS A2 GROUP BY
Position ID) totals
INNER JOIN
position p
ON totals.PositionID=p.PositionID
JOIN
Companies c
on (c.CompanyID = p.CompanyID)
WHERE... etc
By the way, you can also get rid of all the complicated calculation
within A2 - it's wasted, as having done all that calculation, SQL then
only returns a rowcount from the resulting set!
A2 (the SQL i've referred to as (lots of SQL) can be slimmed down to
something like this:
SELECT PositionID [qualify this, specifying which table it should come
from] FROM
applicant a
JOIN
logon l
etc
hope this helps.
cheers
Seb|||tshad (tscheiderich@.ftsolutions.com) writes:
> But why was I getting the error?
If you insist to get answer to that question, you better post the
CREATE TABLE statements for the table, so it's possible to play with
query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Fri, 6 Jan 2006 18:00:48 -0800, tshad wrote:
(snip)
>But why was I getting the error?
Hi Tom,
Normally, a derived table can't refer to columns from the outer query.
The table has to be materialized before the joins in the FROM clause can
be evaluated, so there's no way to know which row is referred to. (This
is the theoretic description - in reality, the optimizer will probably
choose a faster strategy).
If a derived table is used inside a subquery, it still can't refer to
other tables used in the subquery, but it can refer to the tables used
outside of the subquery. This is possible becuase the complete subquery
has to be re-evaluated for any row in the outer query anyway (again, in
theory).
It appears as if SQL Server is unable to recognise this situation if you
start nesting subqueries. I would consider this to be a bug. The very
simple script below will reproduce this behaviour on SQL Server 2000
SP4. I don't have SQL Server 2005 installed, so I can't tell if this is
fixed in SQL Server 2005.
CREATE TABLE t1 (a int, b int)
CREATE TABLE t2 (a int, b int)
go
-- Single derived table - no problem
SELECT (SELECT a
FROM (SELECT *
FROM t2
WHERE t2.b = t1.b
) AS Derived
) AS Subquery
FROM t1
go
-- Nested derived table - error
SELECT (SELECT a
FROM (SELECT *
FROM (SELECT *
FROM t2
WHERE t2.b = t1.b
) AS Inner_Derived
) AS Outer_Derived
) AS Subquery
FROM t1
go
DROP TABLE t1
DROP TABLE t2
go

>And how can I get it work?
Now that's the harder question, I guess.
Start with www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info) writes:
> It appears as if SQL Server is unable to recognise this situation if you
> start nesting subqueries. I would consider this to be a bug. The very
> simple script below will reproduce this behaviour on SQL Server 2000
> SP4. I don't have SQL Server 2005 installed, so I can't tell if this is
> fixed in SQL Server 2005.
Both your queries work on SQL 2005, so it appears to have been fixed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:h9k0s1pqtgusku3pmh58bqk47d67ab75uj@.
4ax.com...
> On Fri, 6 Jan 2006 18:00:48 -0800, tshad wrote:
> (snip)
> Hi Tom,
> Normally, a derived table can't refer to columns from the outer query.
> The table has to be materialized before the joins in the FROM clause can
> be evaluated, so there's no way to know which row is referred to. (This
> is the theoretic description - in reality, the optimizer will probably
> choose a faster strategy).
> If a derived table is used inside a subquery, it still can't refer to
> other tables used in the subquery, but it can refer to the tables used
> outside of the subquery. This is possible becuase the complete subquery
> has to be re-evaluated for any row in the outer query anyway (again, in
> theory).
> It appears as if SQL Server is unable to recognise this situation if you
> start nesting subqueries. I would consider this to be a bug. The very
> simple script below will reproduce this behaviour on SQL Server 2000
> SP4. I don't have SQL Server 2005 installed, so I can't tell if this is
> fixed in SQL Server 2005.
> CREATE TABLE t1 (a int, b int)
> CREATE TABLE t2 (a int, b int)
> go
> -- Single derived table - no problem
> SELECT (SELECT a
> FROM (SELECT *
> FROM t2
> WHERE t2.b = t1.b
> ) AS Derived
> ) AS Subquery
> FROM t1
> go
> -- Nested derived table - error
> SELECT (SELECT a
> FROM (SELECT *
> FROM (SELECT *
> FROM t2
> WHERE t2.b = t1.b
> ) AS Inner_Derived
> ) AS Outer_Derived
> ) AS Subquery
> FROM t1
> go
> DROP TABLE t1
> DROP TABLE t2
> go
Hi Hugo,
Yes, that is exactly what is happening. There may be a different way to
restructure the statement (maybe by a join to get rid of the inner table),
but I'm not sure how it would be done in my situation.
Maybe the inner_derived table has to reference the Outer_Derived and than
the Outer_Derived reference t1 somehow.

>
> Now that's the harder question, I guess.
> Start with www.aspfaq.com/5006.
This doesn't appear to be the faq you meant (or maybe it was) as doesn't
seem to have anything to do with query/subquery question.
Thanks,
Tom
> --
> Hugo Kornelis, SQL Server MVP