SQL help: Cannot perform an aggregate function on an expression containing a subquery

Deebaser

So I have dis code. This code looks at a table that we are creating to see if there are any duplicate records on our front end. Right now, we are classifying any org that has the character string "XXX" as high priority.

I need to enhance this such that cases where we are also capturing actions that have a different txt and date but are showing on the front end. However, I am getting the error: Cannot perform an aggregate function on an expression containing a subquery while running the code below.

select 'METRICNAME' as metric_name
        ,'DUPL' as aggregate_type_cd
        , E.fr_ref as lob_cd
        , count(1) as Refined_CurrMetricValue
        , sum(case 
        when organization_name like '%XXX%' then 1 
        when action_key  in (SELECT action_key 
        from SCHEMA.dupey_dups e4
        where
        e4.action_key   = E.action_key 
        and e4.action_clss = E.action_clss
        AND e4.action_dt <> E.action_dt
        AND e4.action_txt <> E.action_txt
        ) then 1
        
        else 0 end) as "URGENT_COUNT"

The original is

select 'METRICNAME' as metric_name
        ,'DUPL' as aggregate_type_cd
        , E.fr_ref as lob_cd
        , count(1) as Refined_CurrMetricValue
        , sum(case 
        when organization_name like '%XXX%' then 1    
        else 0 end) as "URGENT_COUNT"

This is in sybase. there has to be a way to shoehorn this into a case statement, right?
pls halp

Delmain

Is this in a stored proc? You could pull the duplicate check into a temp table before you run that query, then join onto the temp table from the main query, then you don't need a sub-query in the main case statement.

syndalis

Generic structural question. Can you set up an array of all cases that meet your criteria first, then perform the aggregate function off of the array you established in the first step?

Since I assume you are doing this for reporting purposes and you are not having to update or drop anything...

splitting it into two steps on the backend that appear as one step on the front end is the fastest route to getting it done.

edit: I realize my answer depends on sybase either having an array you can use (my sybase knowledge is slim), or having something outside of SQL you can use to set it up like ruby or PHP.

edit: delmain mentioning a temp table is the same suggestion as mine, if arrays are not an available option.

Im not a fan of using temp tables personally, but they do get the job done in a pinch.

Deebaser

Yeah a temp table would be my go to solution, but my boss think's that's too messy.
We do not have arrays available, but I imagine we can just use a # temp table if it comes down to it.

syndalis

Deebaser wrote: »

Yeah a temp table would be my go to solution, but my boss think's that's too messy.
We do not have arrays available, but I imagine we can just use a # temp table if it comes down to it.

Well, you could always catch and release. create the temp table to receive the data, generate the output then drop the temp table in-code.

Delmain

Oh yeah, naturally. That's what I meant by temp, create, fill, join, drop.

hsu

A common way to get around creating/using/dropping a temp table is to build it on the fly:

select a, b, sum(c), sum(d)
from (
    select a,b,c,d
    from x
) r
group by a, b

In your example, this would look like:

select metric_name, aggregate_type_cd, lob_cd
, count(Refined_CurrMetricValue) as Refined_CurrMetricValue
, sum(URGENT_COUNT) as URGENT_COUNT
from (
    select 'METRICNAME' as metric_name
    ,'DUPL' as aggregate_type_cd
    , E.fr_ref as lob_cd
    , 1 as Refined_CurrMetricValue
    , case when organization_name like '%XXX%' then 1
      when action_key in (
          SELECT action_key from SCHEMA.dupey_dups e4
          where e4.action_key = E.action_key and e4.action_clss = E.action_clss
          and e4.action_dt <> E.action_dt and e4.action_txt <> E.action_txt
      ) then 1
      else 0 end) as URGENT_COUNT
    from E
) R
group by metric_name, aggregate_type_cd, lob_cd

Deebaser

Thanks All!
I got it. Went with making a CTE