mssql subqueries, losing table references
Im working off of an SQL 2000 warehouse and trying to page results for a subquery. MySQL makes it easy with "select * from [table] LIMIT 1, 3" (3 records starting from the 1st record).
SQL 2000 requires trickery with nested subqueries
SELECT * FROM (
SELECT TOP x * FROM (
SELECT TOP y fields
FROM table
WHERE conditions
ORDER BY table.field ASC) as foo
ORDER by field DESC) as bar
ORDER by field ASC
x is the number of rows you want returned and y is x+offset.
With that in mind& if Im running one query that needs a subquery to return paged results (I want the 1st contact, or the 3rd contact, etc), that one subquery has 2 subqueries under it. My problem is that by the time it gets to the innermost subquery, it seems to have lost the reference to one of the tables from the parent query. Here is what Im doing, in a simplified manner to show the issue.
USE [GILVSAM]
SELECT
dbo.SBR.SBRSTDT AS [Student ID] ,
dbo.SBR.SBRLAST AS [Last Name] ,
dbo.SBR.SBRFIRST AS [First Name] ,
(
SELECT TOP 1
sciname
FROM
(
SELECT TOP 1
*
FROM
(
SELECT TOP 1
*
FROM
sci
WHERE
scipu = 'Y'
--at this point, it appears to have lost the reference to the table
--I get 'The column prefix 'dbo.SBR' does not match with a table name or alias name used in the query.'
--I've tried all sorts of workarounds but my Microsoft SQL Server experience doesn't encompass this.
AND ( SCISTDT = dbo.SBR.SBRSTDT )
ORDER BY
scics DESC ,
sciname ASC
) AS foo
ORDER BY
scics ASC ,
sciname DESC
) AS bar
ORDER BY
scics DESC ,
sciname ASC
) AS [Contact 1 Name]
FROM
dbo.SBR
LEFT OUTER JOIN dbo.SCI AS SCI_1
ON dbo.SBR.SBRSTDT = SCI_1.SCISTDT
WHERE
( dbo.SBR.SBRCGRD IN ( 'PK' , 'KG' , '01' , '02' , '03' , '04' , '05' ) )
AND ( dbo.SBR.SBRCSCHL = '0032' )
ORDER BY
dbo.SBR.SBREXNBR ,
dbo.SBR.SBRSTDT
I have attached the full query.
How to I "pass" the table reference down?
Basically the master query/report has columns for names of contact #1, contact #2, etc.. however each of these contacts are a separate record from another table
contact #1 name, phone, address, etc
contact #2 name, phone, address, etc
so I need to 'page' the results so the report looks like this
contact 1 name, contact 1 phone, contact 2 name, contact 2 phone, etc
Report.sql.txt
SQL 2000 requires trickery with nested subqueries
SELECT * FROM (
SELECT TOP x * FROM (
SELECT TOP y fields
FROM table
WHERE conditions
ORDER BY table.field ASC) as foo
ORDER by field DESC) as bar
ORDER by field ASC
x is the number of rows you want returned and y is x+offset.
With that in mind& if Im running one query that needs a subquery to return paged results (I want the 1st contact, or the 3rd contact, etc), that one subquery has 2 subqueries under it. My problem is that by the time it gets to the innermost subquery, it seems to have lost the reference to one of the tables from the parent query. Here is what Im doing, in a simplified manner to show the issue.
USE [GILVSAM]
SELECT
dbo.SBR.SBRSTDT AS [Student ID] ,
dbo.SBR.SBRLAST AS [Last Name] ,
dbo.SBR.SBRFIRST AS [First Name] ,
(
SELECT TOP 1
sciname
FROM
(
SELECT TOP 1
*
FROM
(
SELECT TOP 1
*
FROM
sci
WHERE
scipu = 'Y'
--at this point, it appears to have lost the reference to the table
--I get 'The column prefix 'dbo.SBR' does not match with a table name or alias name used in the query.'
--I've tried all sorts of workarounds but my Microsoft SQL Server experience doesn't encompass this.
AND ( SCISTDT = dbo.SBR.SBRSTDT )
ORDER BY
scics DESC ,
sciname ASC
) AS foo
ORDER BY
scics ASC ,
sciname DESC
) AS bar
ORDER BY
scics DESC ,
sciname ASC
) AS [Contact 1 Name]
FROM
dbo.SBR
LEFT OUTER JOIN dbo.SCI AS SCI_1
ON dbo.SBR.SBRSTDT = SCI_1.SCISTDT
WHERE
( dbo.SBR.SBRCGRD IN ( 'PK' , 'KG' , '01' , '02' , '03' , '04' , '05' ) )
AND ( dbo.SBR.SBRCSCHL = '0032' )
ORDER BY
dbo.SBR.SBREXNBR ,
dbo.SBR.SBRSTDT
I have attached the full query.
How to I "pass" the table reference down?
Basically the master query/report has columns for names of contact #1, contact #2, etc.. however each of these contacts are a separate record from another table
contact #1 name, phone, address, etc
contact #2 name, phone, address, etc
so I need to 'page' the results so the report looks like this
contact 1 name, contact 1 phone, contact 2 name, contact 2 phone, etc
Report.sql.txt
oh, and in that sample query, there is no point nesting that deep - nor any point using an order by when the data source is going to return a single row anyway (e.g. ok within the foo subquery, but no point from the foo subquery, nor the bar subquery). But figured it is an example anyway, so maybe a moot point mentioning it.
Had a good look at that query, seems to be a better way, so long as there is a "uniqueness" that can be checked for contacts to make sure it is in sequence. Seems to be SCISTDT and SCICS, so, using that as a basis... Have a look at :
SELECT
SBR.SBRSTDT AS [Student ID] ,
SBR.SBRLAST AS [Last Name] ,
SBR.SBRFIRST AS [First Name],
SCI_1.SCINAME AS [Contact 1 Name],
SCI_2.SCINAME AS [Contact 2 Name],
SCI_3.SCINAME AS [Contact 3 Name],
SCI_4.SCINAME AS [Contact 4 Name]
FROM dbo.SBR
LEFT OUTER JOIN (select (select count(*) from dbo.sci i where i.scics < sci.scics and i.scistdt = sci.scistdt) as seq, * from dbo.sci sci) AS SCI_1 ON SBR.SBRSTDT = SCI_1.SCISTDT AND SCI_1.SEQ = 0
LEFT OUTER JOIN (select (select count(*) from dbo.sci i where i.scics < sci.scics and i.scistdt = sci.scistdt) as seq, * from dbo.sci sci) AS SCI_2 ON SBR.SBRSTDT = SCI_2.SCISTDT AND SCI_2.SEQ = 1
LEFT OUTER JOIN (select (select count(*) from dbo.sci i where i.scics < sci.scics and i.scistdt = sci.scistdt) as seq, * from dbo.sci sci) AS SCI_3 ON SBR.SBRSTDT = SCI_3.SCISTDT AND SCI_3.SEQ = 2
LEFT OUTER JOIN (select (select count(*) from dbo.sci i where i.scics < sci.scics and i.scistdt = sci.scistdt) as seq, * from dbo.sci sci) AS SCI_4 ON SBR.SBRSTDT = SCI_4.SCISTDT AND SCI_4.SEQ = 3
WHERE ( SBR.SBRCGRD IN ( 'PK' , 'KG' , '01' , '02' , '03' , '04' , '05' ) )
AND ( SBR.SBRCSCHL = '0032' )
ORDER BY SBR.SBREXNBR , SBR.SBRSTDT
Or, if lots and lots of contacts, then might like to try:
SELECT
SBR.SBRSTDT AS [Student ID] ,
SBR.SBRLAST AS [Last Name] ,
SBR.SBRFIRST AS [First Name],
MAX(CASE WHEN SCI_1.SEQ = 0 then SCI_1.SCINAME else '' END) AS [Contact 1 Name],
MAX(CASE WHEN SCI_1.SEQ = 1 then SCI_1.SCINAME else '' END) AS [Contact 2 Name],
MAX(CASE WHEN SCI_1.SEQ = 2 then SCI_1.SCINAME else '' END) AS [Contact 3 Name],
MAX(CASE WHEN SCI_1.SEQ = 3 then SCI_1.SCINAME else '' END) AS [Contact 4 Name]
FROM dbo.SBR
LEFT OUTER JOIN (select (select count(*) from dbo.sci i where i.scics < sci.scics and i.scistdt = sci.scistdt) as seq, * from dbo.sci sci) AS SCI_1 ON SBR.SBRSTDT = SCI_1.SCISTDT
GROUP BY SBR.SBRSTDT, SBR.SBRLAST, SBR.SBRFIRST
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Really ? Still getting column / alias errors !? Can you please do a print screen / paste the error from the last query I posted - professional curiosity, and would really, really like to find out why...
ASKER
I'll have to wait until monday since that's at work. I do remember the error saying something about no column named 'seq' exists (like it didn't 'take' your "AS seq" alias).
Wonder if it is being fussy about case sensitivity ? If you wouldn't mind checking on Monday, would like to find out... Thanks for your reply.
ASKER
the query without your last post worked (minus contact information) however plugging in your entire post gives this error
Msg 8120, Level 16, State 1, Line 3
Column 'dbo.SBR.SBREXNBR' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
if I leave in your left join, and take out the 4 max() lines (that display the column information), the error goes away and the query runs (but obviously without the contact information).
Msg 8120, Level 16, State 1, Line 3
Column 'dbo.SBR.SBREXNBR' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
if I leave in your left join, and take out the 4 max() lines (that display the column information), the error goes away and the query runs (but obviously without the contact information).
Thanks for getting back... That is very curious considering SBR.SBREXNBR is not in that query... Curiouser and curiouser! cried Alice...
ASKER
well when it was plugged into the overall query that error occurs
Does it work independantly of the rest ? Wanna show the new "entire" query, or are you happy with your workaround ?
ASKER
independently it works. I'm happy with my workaround (I forced the IT guys to upgrade that box to a SQL 2005 instance so I could use temporary tables with ROW_NUMBER(). I'll try to re-make the query to what it was when I tried to integrate your solution in it.
the associated error I get is:
Msg 8120, Level 16, State 1, Line 2
Column 'M.SBREXNBR' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
however the column SBREXNBR does indeed exist in the SBR (M) table.
the associated error I get is:
Msg 8120, Level 16, State 1, Line 2
Column 'M.SBREXNBR' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
however the column SBREXNBR does indeed exist in the SBR (M) table.
USE [GILVSAM]
SELECT TOP 100 PERCENT
M.SBREXNBR AS [State ID] ,
M.SBRSTDT AS [Student ID] ,
M.SBRLAST AS [Last Name] ,
M.SBRFIRST AS [First Name] ,
M.SBRMIDDLE AS [Middle Initial] ,
M.SBRSEX AS Gender ,
M.SBRBIRTH AS DOB ,
'(' + M.SBRAC + ') ' + M.SBREX + ' - ' + M.SBRNO AS [Phone Number] ,
M.SBRRNBR + ' ' + M.SBRRDIR + ' ' + M.SBRRSTR + ' ' + M.SBRRTYPE
+ ' ' + M.SBRRAPT AS [Street Address] ,
M.SBRRCITY AS City ,
M.SBRRST AS State ,
M.SBRRZIP5 AS Zipcode ,
(
SELECT
CASE WHEN EXISTS ( SELECT
1
FROM
dbo.SHC
WHERE
SHC.SHCSTDT = M.SBRSTDT ) THEN 1
ELSE 0
END AS Expr1
) AS [Medical Problem] ,
M.SBRRACE AS Race ,
'n/a' AS [Race Description] ,
M.SBRCGRD AS [Grade Level] ,
'n/a' AS [Grade Level Description] ,
M.SBRLEP AS [LEP Category Code] ,
M.SBRLNELG AS [Lunch Type Code] ,
'n/a' AS [Lunch Type Description] ,
M.SBRLANG AS [Primary Language] ,
'n/a' AS [Primary Langauge Description] ,
M.SBRCSCHL AS [School Code] ,
'BELL ELEMENTARY SCHOOL' AS [School Description] ,
M.SBRPRIM AS [Special Ed Category Code] ,
'n/a' AS [Special Ed Category Description] ,
'***' AS [Medical Reason] ,
( CASE WHEN M.sbrcgrd = M.sbrlgrd THEN 1
ELSE 0
END ) AS Retained ,
M.SBRCHMRM AS [Home Room] ,
(
SELECT
TCHLNAME
FROM
dbo.SST
WHERE
( SSTTCHR = (
SELECT
SHRTCHR
FROM
dbo.SHR
WHERE
( SHRHMRM = M.SBRCHMRM )
AND ( SHRSCHL = '0032' )
AND ( SHRFY = SUBSTRING(CAST(YEAR(GETDATE()) AS CHAR) ,
3 , 2) )
) )
AND ( SSTSCHL = '0032' )
AND ( SSTSY = SUBSTRING(CAST(YEAR(GETDATE()) AS CHAR) , 3 ,
2) )
) AS [Homeroom Teacher Last name] ,
MAX(CASE WHEN SCI_1.SEQ = 0 then SCI_1.SCINAME
else ''
END) AS [Contact 1 Name] ,
MAX(CASE WHEN SCI_1.SEQ = 1 then SCI_1.SCINAME
else ''
END) AS [Contact 2 Name] ,
MAX(CASE WHEN SCI_1.SEQ = 2 then SCI_1.SCINAME
else ''
END) AS [Contact 3 Name] ,
MAX(CASE WHEN SCI_1.SEQ = 3 then SCI_1.SCINAME
else ''
END) AS [Contact 4 Name]
FROM
dbo.SBR AS M
LEFT OUTER JOIN (
select
(
select
count(*)
from
dbo.sci i
where
i.scics < sci.scics
and i.scistdt = sci.scistdt
) as seq ,
*
from
dbo.sci sci
) AS SCI_1
ON M.SBRSTDT = SCI_1.SCISTDT
WHERE
( M.SBRCGRD IN ( 'PK' , 'KG' , '01' , '02' , '03' , '04' , '05' ) )
AND ( M.SBRCSCHL = '0032' )
GROUP BY
M.SBRSTDT ,
M.SBRLAST ,
M.SBRFIRST
ORDER BY
M.SBREXNBR ,
M.SBRSTDT
Yeah, any / all of those M.* columns would have to be in that group by given that sql statement - which is clearly not the way (ie group by).
However should be able to do something (if student ID is unique enough) like:
However should be able to do something (if student ID is unique enough) like:
SELECT TOP 100 PERCENT
M.SBREXNBR AS [State ID] ,
M.SBRSTDT AS [Student ID] ,
M.SBRLAST AS [Last Name] ,
M.SBRFIRST AS [First Name] ,
M.SBRMIDDLE AS [Middle Initial] ,
M.SBRSEX AS Gender ,
M.SBRBIRTH AS DOB ,
'(' + M.SBRAC + ') ' + M.SBREX + ' - ' + M.SBRNO AS [Phone Number] ,
M.SBRRNBR + ' ' + M.SBRRDIR + ' ' + M.SBRRSTR + ' ' + M.SBRRTYPE
+ ' ' + M.SBRRAPT AS [Street Address] ,
M.SBRRCITY AS City ,
M.SBRRST AS State ,
M.SBRRZIP5 AS Zipcode ,
(
SELECT
CASE WHEN EXISTS ( SELECT
1
FROM
dbo.SHC
WHERE
SHC.SHCSTDT = M.SBRSTDT ) THEN 1
ELSE 0
END AS Expr1
) AS [Medical Problem] ,
M.SBRRACE AS Race ,
'n/a' AS [Race Description] ,
M.SBRCGRD AS [Grade Level] ,
'n/a' AS [Grade Level Description] ,
M.SBRLEP AS [LEP Category Code] ,
M.SBRLNELG AS [Lunch Type Code] ,
'n/a' AS [Lunch Type Description] ,
M.SBRLANG AS [Primary Language] ,
'n/a' AS [Primary Langauge Description] ,
M.SBRCSCHL AS [School Code] ,
'BELL ELEMENTARY SCHOOL' AS [School Description] ,
M.SBRPRIM AS [Special Ed Category Code] ,
'n/a' AS [Special Ed Category Description] ,
'***' AS [Medical Reason] ,
( CASE WHEN M.sbrcgrd = M.sbrlgrd THEN 1
ELSE 0
END ) AS Retained ,
M.SBRCHMRM AS [Home Room] ,
(
SELECT
TCHLNAME
FROM
dbo.SST
WHERE
( SSTTCHR = (
SELECT
SHRTCHR
FROM
dbo.SHR
WHERE
( SHRHMRM = M.SBRCHMRM )
AND ( SHRSCHL = '0032' )
AND ( SHRFY = SUBSTRING(CAST(YEAR(GETDATE()) AS CHAR) ,
3 , 2) )
) )
AND ( SSTSCHL = '0032' )
AND ( SSTSY = SUBSTRING(CAST(YEAR(GETDATE()) AS CHAR) , 3 ,
2) )
) AS [Homeroom Teacher Last name] ,
SCI_4.[Contact 1 Name] ,
SCI_4.[Contact 2 Name] ,
SCI_4.[Contact 3 Name] ,
SCI_4.[Contact 4 Name]
FROM
dbo.SBR AS M
INNER JOIN (SELECT
SBR.SBRSTDT,
MAX(CASE WHEN SCI_1.SEQ = 0 then SCI_1.SCINAME else '' END) AS [Contact 1 Name],
MAX(CASE WHEN SCI_1.SEQ = 1 then SCI_1.SCINAME else '' END) AS [Contact 2 Name],
MAX(CASE WHEN SCI_1.SEQ = 2 then SCI_1.SCINAME else '' END) AS [Contact 3 Name],
MAX(CASE WHEN SCI_1.SEQ = 3 then SCI_1.SCINAME else '' END) AS [Contact 4 Name]
FROM dbo.SBR
LEFT OUTER JOIN (select (select count(*) from dbo.sci i where i.scics < sci.scics and i.scistdt = sci.scistdt) as seq, * from dbo.sci sci) AS SCI_1 ON SBR.SBRSTDT = SCI_1.SCISTDT
GROUP BY SBR.SBRSTDT) as SCI_4 on M.SBRSTDT = SCI_4.SBRSTDT
WHERE
( M.SBRCGRD IN ( 'PK' , 'KG' , '01' , '02' , '03' , '04' , '05' ) )
AND ( M.SBRCSCHL = '0032' )
ORDER BY
M.SBREXNBR ,
M.SBRSTDT
ASKER
yeah both columns in the ORDER BY (M.SBREXNBR and M.SBRSTDT) are both unique, 2 'primary keys' if you will.
sbrexnbr is a field that uniquely identifies that student within the state of florida, sbrstdt is a field that uniquely identifies that student within this company. Why they used 2 unique fields is a really long story.
sbrexnbr is a field that uniquely identifies that student within the state of florida, sbrstdt is a field that uniquely identifies that student within this company. Why they used 2 unique fields is a really long story.
In which case, think that query should go pretty dang close (typos and D'Oh level syntax errors excepted).
Using the shortened query above by way of example...
and interestingly, the query is not using anything from the dbo.sci left outer join linked in the main "From"
Open in new window