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,PostedData =
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+Cat4+Cat5+Cat6+Cat7+Cat8,
         TotalValues =
Value1+Value2+Value3+Value4+Value5+Value6+Value7+Value8,
         CriteriaStatus = CASE WHEN
(Cat1+Cat2+Cat3+Cat4+Cat5+Cat6+Cat7+Cat8) =
(Value1+Value2+Value3+Value4+Value5+Value6+Value7+Value8) THEN 'All'
                  WHEN
(Value1+Value2+Value3+Value4+Value5+Value6+Value7+Value8) >=
((Cat1+Cat2+Cat3+Cat4+Cat5+Cat6+Cat7+Cat8)/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,

Tom

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 (t**@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

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

Show quoteHide quote

tshad (tscheider***@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.)

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.

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 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
Show quoteHide quote

tshad (tscheider***@ftsolutions.com) writes:
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.

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

Now that's the harder question, I guess.

Start with www.aspfaq.com/5006.

Hugo Kornelis (h***@perFact.REMOVETHIS.info) writes:
Both your queries work on SQL 2005, so it appears to have been fixed.

Show quote Hide quote 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.

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
Show quoteHide quote

I don't think you can call a SP from within a query, but would a User
Function, where you pass the outside field (t1.b in your example)?

Thanks,

Tom

Show quoteHide quote

On Wed, 18 Jan 2006 06:15:19 -0800, tshad wrote:

Hi Tom,

This is not possible in SQL Server 2000. In SQL Server 2005, it can be
done with the new CROSS APPLY operator. I'm not sure if it would help
you in this case, though.

www.aspfaq.com.5006

Hi Hugo,

I was able to make it work by creating a User Function.  Works great.  Since
I was really only concerned with the count of rows, I took the inner querys
and put them into my new User Function (QualifiedCount).  This function is
passed the PositionID and it is put directly into the query (Where
a.PositionID = @PositionID ).  The @PositionID used to be the outside field
I was unable to access, but since it is passed in it works fine

************************************************
CREATE FUNCTION QualifiedCount
(@PositionID Int)
RETURNS Int
AS
BEGIN
declare @Qualified Int

Select @Qualified = (Select Count(*) from (
    ...
             left join MyInfo m on (m.UserID = a.UserID)
             Where a.PositionID = @PositionID
             ) as a1) as a2 where CriteriaStatus = 'All')

return @Qualified
end
****************************************

I call it from my select as so:

Qualified = dbo.QualifiedCount(p.PositionID)

Thanks,

Tom

Show quoteHide quote

tshad (t**@dslextreme.com) writes:
No. A derived table cannot reference an outer derived table which it is
nested to. It can in fact not reference anything at all in the query
which it is part of. But if that query is a correlated subquery, the
derived table can reference something in the main query for that subquery.

The only problem is that SQL 2000 has not really understood this, and
incorrectly flags this query as incorrect.

That's a generic FAQ entry, which advices you which information you should
provide to increase your chances to get help.

Show quote Hide quote But in Hugos example (the first one that worked):

SELECT (SELECT a
        FROM  (SELECT *
               FROM   t2
               WHERE  t2.b = t1.b
              ) AS Derived
       ) AS Subquery
FROM   t1

Isn't it referencing the outside select here (t1.b)?

But it can't reference the same field in this one (which is just a level
deeper):

SELECT (SELECT a
        FROM  (SELECT *
               FROM  (SELECT *
                      FROM   t2
                      WHERE  t2.b = t1.b
                     ) AS Inner_Derived
              ) AS Outer_Derived
       ) AS Subquery
FROM   t1

This is where it gets confusing.

Thanks,

Tom
Show quoteHide quote

tshad (tscheider***@ftsolutions.com) writes:
Derived may not reference to something in Subquery, that is the query
which it is nested into. But Subquery is correlated with t1, and Subquery
may reference to t1. Since Derived is part of Subquery, Derived may also
refer to t1.

Show quoteHide quoteIn theory nothing has changed. Inner_derived may still refer to t1, but
not to Outer_derived or Subquery. Problem is that SQL 2000 has not
understood this. Since this works in the SQL 2005, we will have to assume
that SQL 2000 simply has a bug here.

On Wed, 18 Jan 2006 04:49:05 -0800, tshad wrote:

(snip)
Hi Tom,

Maybe -- but see below.

Of course, since Erland confirmed that my simplified repro runs fine on
SQL Server 2005, you might consider an upgrade. I'm sure that Erland (or
anyone else who is already running SQL Server 2005) will gladly run any
runnable code you post to verify that an upgrade will indeed fix your
problem.

Did you actually read the page, or did you just take a short glance?

The very first paragraph explains exactly why that page was written and
why I gave you that link.

(In fact, I'm quite surprised to find that I even have to point you to
this FAQ. After all, you're not exactly new in this group. And Erland
has already asked you twice in this very same discussion to provide DDL
and sample data).

Anyway - if you want me to help you finding a workaround for this bug,
you'll have to give me some code that I can run to repro your situation
and the expected results. If you want others to test if your script will
indeed work in SQL Server 2005, you'll have to give them some code that
they can run to repro your situation.

The query is much too complex to warrant a re-write without knowledge of
table structure, data and expected results.

Show quote Hide quote I agree and I usually do put together a DDL, but this one would be tough to
put together (as you said it is a little complex).  I didn't know how to
reasonably glean it down to a bite size piece.

You're right abou the Faq.  I thought it was something about subqueries so I
thought you had sent the wrong one.

Sorry.

Tom
Show quoteHide quote

NULL in Joins
Memory problems using Sql Server 2000
Joing two tables but avoid cartesian product
Dynamic SQL Column Total Help
Datetime constraint: is there a better way to do this?