Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Unanswered: using subquery in aggregate function

    Hi,
    I have an a powerbuilder application connected to SQL Server through Microsoft ODBC, and I'm having the following error :
    cannot perform aggregate function on an expression containing aggregate funtion or subquery.
    The query that is giving the following error works on sybase but the problem is that I'm trying to make it work also for SQL Server, but the problem is it contains aggregate function on an expreesion containing a subquery like:
    SELECT emp_main.employee_id ,
    sum(case when ( pmonthtr.paytype in (Select payreg_hd.grosstax from payreg_hd where payreg_hd.year between 2003 and 2003 and ( payreg_hd.reg_id in ('LL') ))) then pmonthtr.amount_l else 0 end) ,
    ......
    I wanna ask if somoene knows if there is an option inthe ODBC profile of the database that I must select to overcome is problem or if there are no methods in SQL server to solve it.
    thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It is probably possible to rewrite your query. There are several issues with what you have posted that can affect efficiency, at least. Post the entire statement, along with a description of what you are trying to accomplish, and forum members can take a crack at it.

    blindman

  3. #3
    Join Date
    Oct 2003
    Posts
    20
    Originally posted by blindman
    It is probably possible to rewrite your query. There are several issues with what you have posted that can affect efficiency, at least. Post the entire statement, along with a description of what you are trying to accomplish, and forum members can take a crack at it.

    blindman
    Hi blindman,
    In fact, I'm trying to make the payroll application (that works with sybase) to run on SQL Server and Oracle. The main objective is to get the employee income tax for some criteria. This is the query that works on Sybase SQL Anywhere and I'm trying to get it work also for SQL Server:
    SELECT emp_main.employee_id ,
    sum(case when ( pmonthtr.paytype in (Select payreg_hd.grosstax from payreg_hd where payreg_hd.year between 2003 and 2003 and ( payreg_hd.reg_id in ('LL') ))) then pmonthtr.amount_l else 0 end) ,
    sum(case when ( pmonthtr.paytype in (Select payreg_hd.schreb from payreg_hd where payreg_hd.year between 2003 and 2003 and ( payreg_hd.reg_id in ('LL') ))) then pmonthtr.amount_l else 0 end) ,
    sum(case when ( pmonthtr.paytype in (Select payreg_hd.rebate from payreg_hd where payreg_hd.year between 2003 and 2003 and ( payreg_hd.reg_id in ('LL') ))) then pmonthtr.amount_l else 0 end) ,
    sum(case when ( pmonthtr.paytype in (Select payreg_hd.transport from payreg_hd where payreg_hd.year between 2003 and 2003 and ( payreg_hd.reg_id in ('LL') ))) then pmonthtr.amount_l else 0 end) ,
    sum(case when ( pmonthtr.paytype in (Select payreg_hd.tax1 from payreg_hd where payreg_hd.year between 2003 and 2003 and ( payreg_hd.reg_id in ('LL') ))) then pmonthtr.amount_l else 0 end) ,
    sum(case when ( pmonthtr.paytype in (Select payreg_hd.off_tax from payreg_hd where payreg_hd.year between 2003 and 2003 and ( payreg_hd.reg_id in ('LL') ))) then pmonthtr.amount_l else 0 end) ,
    sum(case when ( pmonthtr.paytype in (Select payreg_hd.off_tax from payreg_hd where payreg_hd.year between 2003 and 2003 and ( payreg_hd.reg_id in ('LL') ))) then pmonthtr.quantity else 0 end) ,
    sum(case when ( pmonthtr.paytype in (Select payreg_hd.adm_rebate from payreg_hd where payreg_hd.year between 2003 and 2003 and ( payreg_hd.reg_id in ('LL') ))) then pmonthtr.amount_l else 0 end) ,
    max(case when emp_main.dt_socsec is null then '1900-01-01' else emp_main.dt_socsec end ),
    max(case when emp_main.dt_ended is null then '1900-01-01' else emp_main.dt_ended end ),
    max(case when emp_main.marital_status is null then 0 else emp_main.marital_status end ),
    max(case when emp_main.reb_childnum is null then 0 else emp_main.reb_childnum end ),
    max(case when emp_main.sp_on_charge is null then 0 else emp_main.sp_on_charge end ),
    pmonthtr.branch ,
    pmonthtr.dpt ,
    pmonthtr.subdpt ,
    max(pmonthtr.emp_type) ,
    (max(emp_main.payperiod))
    FROM emp_main,pmonthtr,paytype
    WHERE ( emp_main.employee_id = pmonthtr.employee_id ) and
    (( pmonthtr.paytype in (Select payreg_hd.transport from payreg_hd where payreg_hd.year between 2003 and 2003 and
    ( payreg_hd.reg_id in ('LL') ) )) or
    ( pmonthtr.paytype in (Select payreg_hd.grosstax from payreg_hd where payreg_hd.year between 2003 and 2003 and
    ( payreg_hd.reg_id in ('LL') ) )) or
    ( pmonthtr.paytype in (Select payreg_hd.schreb from payreg_hd where payreg_hd.year between 2003 and 2003 and
    ( payreg_hd.reg_id in ('LL') ) )) or
    ( pmonthtr.paytype in (Select payreg_hd.rebate from payreg_hd where payreg_hd.year between 2003 and 2003 and
    ( payreg_hd.reg_id in ('LL') ) )) or
    ( pmonthtr.paytype in (Select payreg_hd.off_tax from payreg_hd where payreg_hd.year between 2003 and 2003 and ( payreg_hd.reg_id in ('LL') ) )) or
    ( pmonthtr.paytype in (Select payreg_hd.tax1 from payreg_hd where payreg_hd.year between 2003 and 2003 and ( payreg_hd.reg_id in ('LL') ) )) or
    ( pmonthtr.paytype in (Select payreg_hd.adm_rebate from payreg_hd where payreg_hd.year between 2003 and 2003 and ( payreg_hd.reg_id in ('LL') ) ))) and
    ( pmonthtr.paytype = paytype.paytype_id ) and
    ( pmonthtr.year = paytype.year ) and
    ( emp_main.employee_id between '0' and 'zzzzz' ) And
    ( pmonthtr.branch between '0' and 'zzz' ) And
    ( pmonthtr.dpt between '0' and 'zzz' ) And
    ( pmonthtr.subdpt between '000' and '000' ) And
    ( ( 0 = 0) or (pmonthtr.emp_type between 0 and 10000 )) and
    ( emp_main.active <> 1 ) and
    ( emp_main.payperiod between '0' and 'z' ) And
    ( paytype.plevel >= 1 ) and
    ( pmonthtr.reg_id in ('LL') ) and
    ((cast(pmonthtr.year as varchar) + pmonthtr.month) between '200301' and '200312' )
    Group By emp_main.employee_id , pmonthtr.branch , pmonthtr.dpt , pmonthtr.subdpt
    Order By 15 ASC, 16 ASC, 17 ASC, Emp_main.employee_id ;
    From this query a cursor is built .
    Pmonthtr is the transaction table that contains all the amount and quantity for each employee for a month and a paytype.
    Thanks.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    good lord.

    There is so much wrong with this. You can start by replacing all the IN('LL') clauses with straight = 'LL' clauses, and then you can eliminate some of your WHERE clauses by JOINing your tables in the FROM clause:

    FROM emp_main
    inner join pmonthtr on emp_main.employee_id = pmonthtr.employee_id
    inner join paytype on pmonthtr.year = paytype.year

    You can also eliminate a lot of the CASE statements in your SELECT clause by replacing them with ISNULL functions like this:

    max(isnull(emp_main.dt_socsec, '1900-01-01'))

    All this is going to solve your main problem, which is the CASE statments in your aggregate calculations, but that is going to take me a while longer to crack.

    If we ever meet, you are going to owe me a very large beer.

    blindman

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and clauses like:

    ((0 = 0) or (pmonthtr.emp_type between 0 and 10000))

    or logically meaningless because they always evaluate to true.

  6. #6
    Join Date
    Oct 2003
    Posts
    20
    Originally posted by blindman
    ...and clauses like:

    ((0 = 0) or (pmonthtr.emp_type between 0 and 10000))

    or logically meaningless because they always evaluate to true.
    Hello blindman,
    I owe u a beer even u don't crack it.
    The problem is not in IN ('LL') because 'LL' is a regulation id and in the criteria user can select between different regulation id example in ('LL','LW').
    The query a have gave u is an example of a selection of criteria so all the values.
    The other prob. is that I didn't make that query but someone else and I have to make it work on SQL Server.
    The main problem for SQL Server is the use of subquery in aggregate function like
    sum(case when ( pmonthtr.paytype in (Select payreg_hd.grosstax from payreg_hd where payreg_hd.year between 2003 and 2003 and ( payreg_hd.reg_id in ('LL') ))) then pmonthtr.amount_l else 0 end) .
    and the sum is done on pmonthtr.amount_l for each type of paytype.
    I solved this problem by eliminating all the sum from this query and making 7 select into in the script to get the sum of amount_l:
    SELECT sum(pmonthtr.amount_l)
    INTO :lc_CBT
    FROM pmonthtr
    WHERE ( pmonthtr.employee_id = :ls_emp_id) and
    ( pmonthtr.paytype in (Select payreg_hd.grosstax from payreg_hd where payreg_hd.year between :ii_year1 and :ii_year2 )) and
    ((cast(pmonthtr.year as varchar) + pmonthtr.month) between cast(:ii_year1 as varchar) + :is_mnth1 and cast(:ii_year2 as varchar) + :is_mnth2 );
    It doen't seem like a good solution.
    Thanks .

Posting Permissions

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