Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Red face Unanswered: Getting An Error Message

    I need to get Totals from two different tables, for a monthly report the users have to do. I'm getting an error message, can anyone tell me where I went wrong please??

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER VIEW [dbo].[MonthlyReport_view]
    AS
    SELECT     COUNT(*) AS [total_rows],
    
    COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Single Parent w/child 1 to 6 yrs%') THEN 1 END) AS Single, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Single Parent w/child 6 yrs & up%') THEN 1 END) AS [Single Parent W/child 6 yrs & up], 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Two-Parent Family%') THEN 1 END) AS Two, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Non-Needy%') THEN 1 END) AS [Non-Needy], 
                          COUNT(CASE WHEN (People_tbl.ReferralStatus LIKE N'Disregarded%') THEN 1 END) AS Disregarded, 
                          COUNT(CASE WHEN (People_tbl.ReferralStatus LIKE N'Exempt%') THEN 1 END) AS Exempt, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Counseling%') THEN 1 END) AS Counseling, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Other%') THEN 1 END) AS Other, 
                          COUNT(CASE WHEN (People_tbl.ReferralStatus LIKE N'Active%') THEN 1 END) AS Active, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'At Risk%') THEN 1 END) AS [At Risk]
                          UNION ALL
                          COUNT(CASE WHEN (Student_ind.Age <= 3) THEN 1 END) AS [Child 1-3],
                          COUNT(CASE WHEN (Student_ind.Age <= 4) THEN 1 END) AS [Child 4-5],
                          COUNT(CASE WHEN (Student_ind.Age <= 12) THEN 1 END) AS [Child 1-12] 
                          
    
    FROM         dbo.People_tbl
    WHERE     (ReferralStatus <> N'Inactive')and (Student_ind.[Active_Inactive] <> N'Inactive') 
    GROUP BY TANF
    HAVING      (TANF = N'Yes')

  2. #2
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    After the UNION ALL the query is missing a SELECT e.g

    UNION ALL

    SELECT COUNT(*) AS [total_rows],

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Here is what I did, getting allot of errors with this though, now is this because the select statements do not have an equal amount of statements? Err Expressions?

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER VIEW [dbo].[MonthlyReport_view]
    AS
    SELECT     
    
    COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Single Parent w/child 1 to 6 yrs%') THEN 1 END) AS Single, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Single Parent w/child 6 yrs & up%') THEN 1 END) AS [Single Parent W/child 6 yrs & up], 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Two-Parent Family%') THEN 1 END) AS Two, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Non-Needy%') THEN 1 END) AS [Non-Needy], 
                          COUNT(CASE WHEN (People_tbl.ReferralStatus LIKE N'Disregarded%') THEN 1 END) AS Disregarded, 
                          COUNT(CASE WHEN (People_tbl.ReferralStatus LIKE N'Exempt%') THEN 1 END) AS Exempt, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Counseling%') THEN 1 END) AS Counseling, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'Other%') THEN 1 END) AS Other, 
                          COUNT(CASE WHEN (People_tbl.ReferralStatus LIKE N'Active%') THEN 1 END) AS Active, 
                          COUNT(CASE WHEN (People_tbl.TypeOfParticipant LIKE N'At Risk%') THEN 1 END) AS [At Risk]
                          UNION ALL
                      
                  SELECT COUNT(*) AS [total_rows],
     COUNT(CASE WHEN (Student_ind.Age <= 3) THEN 1 END) AS [Child 1-3],
                          COUNT(CASE WHEN (Student_ind.Age <= 4) THEN 1 END) AS [Child 4-5],
                          COUNT(CASE WHEN (Student_ind.Age <= 12) THEN 1 END) AS [Child 1-12] 
                          
    
         FROM Student_ind
       RIGHT OUTER JOIN People_tbl
          ON (Student_ind.[Parent ID] = People_tbl.[Parent ID])
    
    WHERE     (ReferralStatus <> N'Inactive')and (Student_ind.[Active_Inactive] <> N'Inactive') 
    GROUP BY TANF
    HAVING      (TANF = N'Yes')

    Errors = Msg 4104, Level 16, State 1, Procedure MonthlyReport_view, Line 3
    The multi-part identifier "People_tbl.TypeOfParticipant" could not be bound.
    Msg 4104, Level 16, State 1, Procedure MonthlyReport_view, Line 3
    The multi-part identifier "People_tbl.TypeOfParticipant" could not be bound.
    Msg 4104, Level 16, State 1, Procedure MonthlyReport_view, Line 3
    The multi-part identifier "People_tbl.TypeOfParticipant" could not be bound.
    Msg 4104, Level 16, State 1, Procedure MonthlyReport_view, Line 3
    The multi-part identifier "People_tbl.TypeOfParticipant" could not be bound.
    Msg 4104, Level 16, State 1, Procedure MonthlyReport_view, Line 3
    The multi-part identifier "People_tbl.ReferralStatus" could not be bound.
    Msg 4104, Level 16, State 1, Procedure MonthlyReport_view, Line 3
    The multi-part identifier "People_tbl.ReferralStatus" could not be bound.
    Msg 4104, Level 16, State 1, Procedure MonthlyReport_view, Line 3
    The multi-part identifier "People_tbl.TypeOfParticipant" could not be bound.
    Msg 4104, Level 16, State 1, Procedure MonthlyReport_view, Line 3
    The multi-part identifier "People_tbl.TypeOfParticipant" could not be bound.
    Msg 4104, Level 16, State 1, Procedure MonthlyReport_view, Line 3
    The multi-part identifier "People_tbl.ReferralStatus" could not be bound.
    Msg 4104, Level 16, State 1, Procedure MonthlyReport_view, Line 3
    The multi-part identifier "People_tbl.TypeOfParticipant" could not be bound.
    Msg 209, Level 16, State 1, Procedure MonthlyReport_view, Line 27
    Ambiguous column name 'TANF'.
    Msg 209, Level 16, State 1, Procedure MonthlyReport_view, Line 28
    Ambiguous column name 'TANF'.
    Msg 205, Level 16, State 1, Procedure MonthlyReport_view, Line 28
    All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

  4. #4
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    1)Ensure you have equal amount of columns on both SELECT statements - you've got 10 on top and 4 on the bottom
    2)TANF - specify from which table
    3)For the other errors - ensure you have a table to select from - the query in the first part as it is currently - only has "SELECT .... " there is no FROM

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking

    Ok thank you Jack

  6. #6
    Join Date
    Aug 2010
    Posts
    1
    Good Luck for getting what you want

Posting Permissions

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