• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1093
  • Last Modified:

SQL error when using embedded select

I am not a SQL expert but I am getting an error I just do not understand in SQL 2005.  As always I am creating a temp table to generate reports off of. The problem is with the (select CASE(.... statement. If I run it outside of this sp it works fine and if I ommit it from the sp the sp works fine. I swear all the columns I am requesting are in my table , but the tables are large so I will spare the table definition unless nessecary

Here it is. The Error follows after ...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:            Steve Vasilakos
-- Modified date: 7/31/2007
-- Description:      
-- =============================================
ALTER PROCEDURE [dbo].[sp_REPORT_DailySummary_PAPER]
      -- Add the parameters for the stored procedure here
      @siteID int = 0,
      @Month int = 0,
                     @transType int = 0
AS
BEGIN
SET NOCOUNT ON;
create table #reptable(
        CheckCountry int,
        CheckType int,
        PercentApp bigint,
        DayofMonth int,
        PresentNumber int,
        PresentAmount smallmoney,
        ApprovedNumber int,
        ApprovedAmount smallmoney,
        OverNumber int,
        OverAmount smallmoney,
        TotalNumber int,
        TotalAmount int,      
)

insert into #reptable (CheckCountry,CheckType,PercentApp,DayofMonth,PresentNumber,PresentAmount,ApprovedNumber,ApprovedAmount, OverNumber,OverAmount,TotalNumber,TotalAmount)

select
Checks.checkCountry,
Checks.checkType,

---this is the problem right here
(select(CAST(SUM(CASE when (Trans.TransactionResult = 2 or Trans.TransactionResult = 4)
                 then 1
                 when TransactionResult = 3 then 0
                 else 0                  
                 end)as float)/Count(*))*100),  
--end problem
 DATEPART (dd, transactiondate) as DayofMonth,
-- checks presented
      sum (case when (trans.TransactionResult <> 0 AND trans.TransactionResult <> 1)  then 1
      else 0 end),
    sum (case when (trans.TransactionResult <> 0 AND trans.TransactionResult <> 1)  then   checks.CheckAmount else 0 end),

-- checks approved
      sum (case when (trans.TransactionResult = 2)  then 1else 0 end),

      sum (case when trans.TransactionResult = 2  then checks.CheckAmount else 0 end),

-- checks, override            
      sum (case when trans.TransactionResult = 4 then 1 else 0 end), sum (case
            when trans.TransactionResult = 4 then checks.CheckAmount else 0
         end),

-- TOTAL PAID
      sum (case when (trans.TransactionResult = 2 OR trans.TransactionResult = 4)  then 1
      else 0 end),

      sum (case when (trans.TransactionResult = 2 OR trans.TransactionResult = 4)  then checks.CheckAmount else 0 end)
FROM
TerminalInfo
INNER JOIN Site ON TerminalInfo.SiteID = Site.SiteID
INNER JOIN Trans ON TerminalInfo.TerminalID = Trans.TerminalID
INNER JOIN Checks ON Checks.transactionID = Trans.TransactionID

WHERE
    Site.SiteID = @siteID and
    datepart(mm,transactiondate) = @Month AND
    Checks.checkType = @transType
GROUP BY
datepart(dd, transactiondate)

order by dayofmonth

if (@transtype=1)-- PAYROLL and CANADIAN PERSONAL
begin
   select
CheckCountry,CheckType,PercentApp,DayofMonth,PresentNumber,PresentAmount,ApprovedNumber,ApprovedAmount, OverNumber,OverAmount,TotalNumber,TotalAmount
   from #reptable
   where  CheckType = 1 AND CheckCountry != 0
   end
drop table #reptable
END

I know the order by and group by clause are not needed nor is the "if" statement if only one option is valid but I was trying to simplify this statement from some other versions. I can provide table definitions for checks and trans if needed but like I said. If I take out the first (select(case... statement the query works fine.  The problem is the error I get is meaningless

Error: Msg 8120, Level 16, State 1, Procedure sp_REPORT_DailySummary_PAPER, Line 32
Column 'Checks.checkCountry' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

-svasilakos
0
svasilakos
Asked:
svasilakos
1 Solution
 
appariCommented:
try adding Checks.checkCountry, Checks.checkType in group by clause as follows,



select
Checks.checkCountry,
Checks.checkType,
(select(CAST(SUM(CASE when (Trans.TransactionResult = 2 or Trans.TransactionResult = 4)
                 then 1
                 when TransactionResult = 3 then 0
                 else 0                  
                 end)as float)/Count(*))*100),  
 DATEPART (dd, transactiondate) as DayofMonth,
-- checks presented
      sum (case when (trans.TransactionResult <> 0 AND trans.TransactionResult <> 1)  then 1
      else 0 end),
    sum (case when (trans.TransactionResult <> 0 AND trans.TransactionResult <> 1)  then   checks.CheckAmount else 0 end),

-- checks approved
      sum (case when (trans.TransactionResult = 2)  then 1else 0 end),

      sum (case when trans.TransactionResult = 2  then checks.CheckAmount else 0 end),

-- checks, override            
      sum (case when trans.TransactionResult = 4 then 1 else 0 end), sum (case
            when trans.TransactionResult = 4 then checks.CheckAmount else 0
         end),

-- TOTAL PAID
      sum (case when (trans.TransactionResult = 2 OR trans.TransactionResult = 4)  then 1
      else 0 end),

      sum (case when (trans.TransactionResult = 2 OR trans.TransactionResult = 4)  then checks.CheckAmount else 0 end)
FROM
TerminalInfo
INNER JOIN Site ON TerminalInfo.SiteID = Site.SiteID
INNER JOIN Trans ON TerminalInfo.TerminalID = Trans.TerminalID
INNER JOIN Checks ON Checks.transactionID = Trans.TransactionID

WHERE
    Site.SiteID = @siteID and
    datepart(mm,transactiondate) = @Month AND
    Checks.checkType = @transType
GROUP BY
Checks.checkCountry,
Checks.checkType,
datepart(dd, transactiondate)

order by dayofmonth
0
 
kenhaleyCommented:
The error is telling you exactly what the problem is.  When you have an aggregate query (a query that has a "group by" clause), every column in the SELECT portion must be either SUMmed (or otherwise aggregated with MIN(), MAX(), AVG(), etc.) or included in the GROUP BY clause.  Otherwise SQL doesn't know what to do with it.  Simple Example:
   select col1, col2, sum(col3) from table1 group by col1, col2
is legal, but  
   select col1, col2, sum(col3) from table1 group by col1
is illegal, because SQL doesn't know what to do with col2.

In your case, you have several columns that are neither summed nor included in the GROUP BY.  SQL found the first such column and reported it in the error message you got.

Also, if you have an expression (instead of just a column name) as one of the selected columns, such as
     col3 * col4 as product
then you must group by the expression  ("group by col3 * col4") and not the name of it ("group by product" doesn't work).

Hope this helps,
Ken
0
 
svasilakosAuthor Commented:
Thank you so much. Your advise and explanation was so helpful. Obviously I made some novice mistakes. Thanks again
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now