Solved

mssql subqueries, losing table references

Posted on 2008-10-14
16
551 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:MMDeveloper
  • 10
  • 6
16 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22717945
Wow, 1200 lines... Was going to simply use Table Alias or Table name to clarify and seperately identify each data source...  Even though the column names are uniquely identified with the table as a prefix in the column name itself, you do need to help it out when there is more than one table of the same name...

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"
SELECT

      SBR.SBRSTDT AS [Student ID] ,

      SBR.SBRLAST AS [Last Name] ,

      SBR.SBRFIRST AS [First Name] ,

      ( SELECT TOP 1 bar.sciname 

        FROM ( SELECT TOP 1 * 

               FROM ( SELECT TOP 1 *

                      FROM dbo.sci s

                      WHERE s.scipu = 'Y' AND s.SCISTDT = SBR.SBRSTDT

                      ORDER BY s.scics DESC , s.sciname ASC ) AS foo

               ORDER BY foo.scics ASC ,foo.sciname DESC ) AS bar

        ORDER BY bar.scics DESC , bar.sciname ASC ) AS [Contact 1 Name]
 

FROM  dbo.SBR SBR

LEFT OUTER JOIN dbo.SCI SCI ON SBR.SBRSTDT = SCI.SCISTDT              -- this is not used...
 

WHERE ( SBR.SBRCGRD IN ( 'PK' , 'KG' , '01' , '02' , '03' , '04' , '05' ) )

AND ( SBR.SBRCSCHL = '0032' )
 

ORDER BY SBR.SBREXNBR , SBR.SBRSTDT

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22717960
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22718208
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

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22718225
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 

Open in new window

0
 
LVL 15

Accepted Solution

by:
MMDeveloper earned 0 total points
ID: 22743906
I still kept getting column/alias errors. I ended up fixing it myself by populating temporary tables and selecting from them.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22746243
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...
0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 22746564
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).
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22746572
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.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 15

Author Comment

by:MMDeveloper
ID: 22757416
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).
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22757587
Thanks for getting back... That is very curious considering SBR.SBREXNBR is not in that query... Curiouser and curiouser! cried Alice...
0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 22760660
well when it was plugged into the overall query that error occurs
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22760698
Does it work independantly of the rest ? Wanna show the new "entire" query, or are you happy with your workaround ?
0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 22766401
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.
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

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22766724
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:

  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

Open in new window

0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 22770202
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22770261
In which case, think that query should go pretty dang close (typos and D'Oh level syntax errors excepted).
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now