#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    14
    Rep Power
    0

    Problem on query


    Hi,

    I try to run the following query in firebird

    SELECT c.id,c.vergid,c.vergino,c.bank,c.iban,c.address_inv,c.cl_name, c.tip, c.sehir,c.ilce,c.contact_person,c.mobil1,c.aciklama,sumfa, sumfb,sumcsa, sumcsb,sumcfa, sumcfb from clients as c

    left join ( select id,client_id, sum(grand_sum*(100-disc_prcnt)/100) as sumfa from inv_header where inv_type in (1,2,3,4,5,6,7,8,9,10) group by client_id) as iha on c.id=iha.client_id

    left join ( select id,client_id, sum(grand_sum*(100-disc_prcnt)/100) as sumfb from inv_header where inv_type in (11,12,13,14,15,16,17,18,19,20) group by client_id) as ihb on c.id=ihb.client_id

    left join ( select id,cl_id, sum(miktar) as sumcsa from ceksenet where tip in (1,2,3,4,5,6,7,8,9,10) group by cl_id) as csa on c.id=csa.cl_id

    left join ( select id,cl_id, sum(miktar) as sumcsb from ceksenet where tip in (11,12,13,14,15,16,17,18,19,20) group by cl_id) as csb on c.id=csb.cl_id

    left join ( select id,cl_id, sum(miktar) as sumcfa from cfis where tip='TAHSILAT' group by cl_id) as cfa on c.id=cfa.cl_id

    left join ( select id,cl_id, sum(miktar) as sumcfb from cfis where tip='ODEME' group by cl_id) as cfb on c.id=cfb.cl_id

    group by c.id, iha.id,ihb.id, csa.id,csb.id, cfa.id,cfb.id ORDER BY cl_name asc
    I get the following error code :
    Invalid token.
    Dynamic SQL Error. SQL error code = -104.
    Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).
    Actually this query runs without any problem (or i think so) in MySQL.

    So, how I can fix the problem to have it work in firebird?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8
    I think the problem is here :

    left join ( select id,client_id, sum(grand_sum*(100-disc_prcnt)/100) as sumfa from inv_header where inv_type in (1,2,3,4,5,6,7,8,9,10) group by client_id, id ) as iha on c.id=iha.client_id

    You have more that one place the problem above.

    All fields that are not contained in either an aggregate function must be contained by GROUP BY clause.

IMN logo majestic logo threadwatch logo seochat tools logo