locked
SQL Server Migration Assistant for Access - Query error message: GROUP BY expression must contain at least one column that is not an outer reference RRS feed

  • Question

  • After conversion, queries not working.  Code for SQL view when ran in SQL produces following error:

    GROUP BY expression must contain at least one column that is not an outer reference. (how should I rewrite query below?)

    SELECT

     

    'ALL Facilities' AS System, PayorType, ehrAdmitYYYYMM AS Month, SUM((CASE WHEN [ehrVisitTypeRollUp] = 'IP' AND

    [DRGMSFlag]

    <> 'S' THEN 1 ELSE 0 END)) AS [IP Med Cases], SUM((CASE WHEN [ehrVisitTypeRollUp] = 'IP' AND

    [DRGMSFlag]

    = 'S' THEN 1 ELSE 0 END)) AS [IP Surg Cases], SUM((CASE WHEN [ehrVisitTypeRollUp] = 'OBS' THEN 1 ELSE 0 END)) AS [OBS Cases],

     

    SUM((CASE WHEN LEFT([ehrVisitTypeRollUp], 3) = 'OPS' THEN 1 ELSE 0 END)) AS [OPS Cases],

     

    SUM((CASE WHEN [ehrVisitTypeRollUp] = 'OP_OVER' THEN 1 ELSE 0 END)) AS [OP Overnight Cases],

     

    SUM((CASE WHEN [ehrVisitTypeRollUp] = 'ER' THEN 1 ELSE 0 END)) AS [ER Cases], COUNT(ProjVisitKey) AS

    [Total Cases]

    FROM

     

    dbo.

    MAIN_Claims

    WHERE

     

    (ehrExcludeFlag IS NULL)

    OR

     

     

    (ehrExcludeFlag = ''

    )

    GROUP

     

    BY 'all facilities', PayorType,

    ehrAdmitYYYYMM

    Monday, September 26, 2011 1:41 PM

Answers

  • If you take out the constant in the GROUP BY, this will work just fine.  The 'all facilities' is what is causing the problem.  (Yes, the error message is obscure in this case.)

    In any case, the 'all facilities' does not help the grouping, since it is the same for every row.

    NOTE: GROUP BY does not control the order of rows returned.  If that is important, add an ORDER BY as well.

    FWIW,
    RLF


    • Edited by SQLWork Monday, September 26, 2011 3:37 PM Add NOTE
    • Proposed as answer by SQLWork Monday, September 26, 2011 6:04 PM
    • Marked as answer by Iric Wen Monday, March 5, 2012 1:46 AM
    Monday, September 26, 2011 3:31 PM
  • Thank you.  That has been working.
    • Marked as answer by k9-561 Monday, September 26, 2011 5:33 PM
    Monday, September 26, 2011 4:10 PM

All replies

  • If you take out the constant in the GROUP BY, this will work just fine.  The 'all facilities' is what is causing the problem.  (Yes, the error message is obscure in this case.)

    In any case, the 'all facilities' does not help the grouping, since it is the same for every row.

    NOTE: GROUP BY does not control the order of rows returned.  If that is important, add an ORDER BY as well.

    FWIW,
    RLF


    • Edited by SQLWork Monday, September 26, 2011 3:37 PM Add NOTE
    • Proposed as answer by SQLWork Monday, September 26, 2011 6:04 PM
    • Marked as answer by Iric Wen Monday, March 5, 2012 1:46 AM
    Monday, September 26, 2011 3:31 PM
  • Thank you.  That has been working.
    • Marked as answer by k9-561 Monday, September 26, 2011 5:33 PM
    Monday, September 26, 2011 4:10 PM