Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146

    Unanswered: Nested Select - Help

    I dont have a clue what i'm doing wrong.

    Code:
    SELECT Tbl_Region.REGION, [NEW_HMO_CONTRACTS].[# of New Members] AS [HMO NEW CONTRACTS], [NEW_HMO_MEMBERS].[# of New Members] AS [HMO NEW MEMBERS], [TERMED_HMO_CONTRACTS].[# of Termed Contracts] AS [HMO TERMED CONTRACTS], [TERMED_HMO_MEMBERS].[# of Termed Members] AS [HMO TERMED MEMBERS]
    FROM (((Tbl_Region LEFT JOIN [SELECT qry_New_Members_HMO_All_Regions_1.Reg, Count(qry_New_Members_HMO_All_Regions_1.CONTRACT_NUM) AS [# of New Members]
    FROM (SELECT tbl_hmo.Reg, tbl_hmo.CONTRACT_NUM
    FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM
    WHERE (((tbl_hmo_History.CONTRACT_NUM) Is Null))
    GROUP BY tbl_hmo.reg, tbl_hmo.CONTRACT_NUM
    
    ) AS qry_New_Members_HMO_All_Regions_1
    
    GROUP BY qry_New_Members_HMO_All_Regions_1.reg
    ) AS NEW_HMO_CONTRACTS ON Tbl_Region.REGION = [NEW_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_New_Members_HMO_All_Regions_1.reg, Count(qry_New_Members_HMO_All_Regions_1.MEMBER_NUM) AS [# of New Members]
    FROM (SELECT tbl_hmo.reg, tbl_hmo.MEMBER_NUM
    FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM
    WHERE (((tbl_hmo_History.MEMBER_NUM) Is Null))
    GROUP BY tbl_hmo.Aff_Area, tbl_hmo.MEMBER_NUM
    
    ) AS qry_New_Members_HMO_All_Regions_1
    GROUP BY qry_New_Members_HMO_All_Regions_1.reg) AS 4_NEW_HMO_MEMBERS ON Tbl_Region.REGION = [4_NEW_HMO_MEMBERS].reg) LEFT JOIN (SELECT qry_Termed_Contracts_HMO_All_Regions_1.reg, Count(qry_Termed_Contracts_HMO_All_Regions_1.CONTRACT_NUM) AS [# of Termed Contracts]
    FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM
    FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM
    WHERE (((tbl_hmo.CONTRACT_NUM) Is Null))
    GROUP BY tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM
    ) AS qry_Termed_Contracts_HMO_All_Regions_1
    GROUP BY qry_Termed_Contracts_HMO_All_Regions_1.reg) AS TERMED_HMO_CONTRACTS ON Tbl_Region.REGION = [TERMED_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_Termed_Members_HMO_All_Regions_1.reg, Count(qry_Termed_Members_HMO_All_Regions_1.MEMBER_NUM) AS [# of Termed Members]
    FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM
    FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM
    WHERE (((tbl_hmo.MEMBER_NUM) Is Null))
    GROUP BY tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM
    ) AS qry_Termed_Members_HMO_All_Regions_1
    GROUP BY qry_Termed_Members_HMO_All_Regions_1.reg) 
    AS TERMED_HMO_MEMBERS ON Tbl_Region.REGION = [TERMED_HMO_MEMBERS].reg;
    error:
    Server: Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'FROM'.
    Server: Msg 156, Level 15, State 1, Line 8
    Incorrect syntax near the keyword 'AS'.
    Server: Msg 156, Level 15, State 1, Line 18
    Incorrect syntax near the keyword 'AS'.
    Server: Msg 156, Level 15, State 1, Line 24
    Incorrect syntax near the keyword 'AS'.
    Server: Msg 156, Level 15, State 1, Line 31
    Incorrect syntax near the keyword 'AS'.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I haven't a clue what you are doing right. Are you getting paid by the parenthesis?

    "WHERE (((tbl_hmo_History.CONTRACT_NUM) Is Null))"???

    Isn't this:

    "WHERE tbl_hmo_History.CONTRACT_NUM Is Null"

    ...simpler and easier to read?

    And I suspect this may be throwing your first error:

    "LEFT JOIN [SELECT qry_New_Members_HMO_All_Regions_1.Reg,...."

    The square brackets denote a database object. SELECT is a statement, not an object.

    Clean up your code, format it well with indents, and try running the individual components separately before putting them all together. That is the best way to debug.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    it was working in access then i brought it over to sql and then MESS....

    Are you getting paid by the parenthesis? --- haha i wish.

    i will work through it again tomorrow.

    thanks for looking at it.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    THAT was working in ACCESS?!

    ...but that explains the square brackets. I've had Access throw those into free SQL querys before, and then the query won't work until you take them out again. A bug, for sure.

    Was it a single free SQL statement, or were the subqueries manifested as independent views?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    I got it to work.
    PLEASE let me know if you see anything wrong with what i did.

    Code:
    SELECT     dbo.Tbl_Region.REGION_NAME, [4_NEW_HMO_CONTRACTS].[# of New Members] AS [HMO NEW CONTRACTS], 
                          [4_NEW_HMO_MEMBERS].[# of New Members] AS [HMO NEW MEMBERS], 
                          [5_TERMED_HMO_CONTRACTS].[# of Termed Contracts] AS [HMO TERMED CONTRACTS], 
                          [5_TERMED_HMO_MEMBERS].[# of Termed Members] AS [HMO TERMED MEMBERS]
    FROM         dbo.Tbl_Region LEFT OUTER JOIN
                              (SELECT     qry_New_Members_HMO_All_Region_Names_1.Region, COUNT(qry_New_Members_HMO_All_Region_Names_1.CONTRACT_NUM) 
                                                       AS [# of New Members]
                                FROM          (SELECT     tbl_HMO.Region, tbl_HMO.CONTRACT_NUM
                                                        FROM          tbl_HMO LEFT JOIN
                                                                               dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND 
                                                                               tbl_HMO.CONTRACT_NUM = tbl_HMO_History.CONTRACT_NUM
                                                        WHERE      tbl_HMO_History.CONTRACT_NUM IS NULL
                                                        GROUP BY tbl_HMO.Region, tbl_HMO.CONTRACT_NUM) AS qry_New_Members_HMO_All_Region_Names_1
                                GROUP BY qry_New_Members_HMO_All_Region_Names_1.Region) [4_NEW_HMO_CONTRACTS] ON 
                          dbo.Tbl_Region.REGION_NAME = [4_NEW_HMO_CONTRACTS].Region LEFT OUTER JOIN
                              (SELECT     qry_New_Members_HMO_All_Region_Names_1.Region, COUNT(qry_New_Members_HMO_All_Region_Names_1.MEMBER_NUM) 
                                                       AS [# of New Members]
                                FROM          (SELECT     tbl_HMO.Region, tbl_HMO.MEMBER_NUM
                                                        FROM          tbl_HMO Left JOIN
                                                                               dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND 
                                                                               tbl_HMO.MEMBER_NUM = tbl_HMO_History.MEMBER_NUM
                                                        WHERE      tbl_HMO_History.CONTRACT_NUM IS NULL
                                                        GROUP BY tbl_HMO.Region, tbl_HMO.MEMBER_NUM) AS qry_New_Members_HMO_All_Region_Names_1
                                GROUP BY qry_New_Members_HMO_All_Region_Names_1.Region) [4_NEW_HMO_MEMBERS] ON 
                          dbo.Tbl_Region.REGION_NAME = [4_NEW_HMO_MEMBERS].Region Left OUTER JOIN
                              (SELECT     qry_Termed_Contracts_HMO_All_Region_Names_1.Region, 
                                                       COUNT(qry_Termed_Contracts_HMO_All_Region_Names_1.CONTRACT_NUM) AS [# of Termed Contracts]
                                FROM          (SELECT     dbo.tbl_HMO_History.Region, dbo.tbl_HMO_History.Contract_Num
    FROM         dbo.tbl_HMO RIGHT OUTER JOIN
                          dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND 
                          dbo.tbl_HMO.CONTRACT_NUM = dbo.tbl_HMO_History.Contract_Num
    GROUP BY dbo.tbl_HMO_History.Region, dbo.tbl_HMO.CONTRACT_NUM, dbo.tbl_HMO_History.Contract_Num
    HAVING      (dbo.tbl_HMO.CONTRACT_NUM IS NULL)) AS qry_Termed_Contracts_HMO_All_Region_Names_1
                                GROUP BY qry_Termed_Contracts_HMO_All_Region_Names_1.Region) [5_TERMED_HMO_CONTRACTS] ON 
                          dbo.Tbl_Region.REGION_NAME = [5_TERMED_HMO_CONTRACTS].Region Left OUTER JOIN
                              (SELECT     qry_Termed_Members_HMO_All_Region_Names_1.Region, COUNT(qry_Termed_Members_HMO_All_Region_Names_1.MEMBER_NUM) 
                                                       AS [# of Termed Members]
                                FROM          (SELECT     tbl_HMO_History.Region, tbl_HMO_History.MEMBER_NUM
                                                        FROM          tbl_HMO RIGHT JOIN
                                                                               dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND 
                                                                               tbl_HMO.MEMBER_NUM = tbl_HMO_History.MEMBER_NUM
                                                        WHERE      tbl_HMO.MEMBER_NUM IS NULL
                                                        GROUP BY tbl_HMO_History.Region, tbl_HMO_History.MEMBER_NUM) AS qry_Termed_Members_HMO_All_Region_Names_1
                                GROUP BY qry_Termed_Members_HMO_All_Region_Names_1.Region) [5_TERMED_HMO_MEMBERS] ON 
                          dbo.Tbl_Region.REGION_NAME = [5_TERMED_HMO_MEMBERS].Region

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •