If I remember correctly, inline subselects do not work ideally with Firebird. The main problem is that you do not group by n.upccode, n.plu and n.unitcount, but if you do, the result may contain more rows than you want.

With Firebird 2.1 this can easily be circumvented (hopefully, itemdetails contains less than 1 million rows?):

WITH ID_TEMP (upccode, plu, unitcount, description) as
(select id.upccode, id.plu, id.unitcount, id.description from itemdetails id
where not exists(select null
from item_details id2
where id.upccode = id2.upccode
and id.plu = id2.plu
and id.unitcount = id2.unitcount
and id.rdb$db_key > id2.rdb$db_key))
select
d.header,
case when (n.reference is null) then
case when d.upccode = '' then d.plu
else
d.upccode
end
else
case when n.upccode = '' then n.plu
else
n.upccode
end
end as UPCCode,
h.manufacturer,
h.brand,
g.description as category,
case when (n.reference is null) then
d.description
else
n.description
end as description,
nn.description as conDescription,
sum(case when (n.reference is null) then coalesce(s.onHand,0)
else
(coalesce(s.onHand,0) * d.unitcount)/n.unitcount
end) as onhnahd,
sum(coalesce(sc.cost,0) * coalesce(s.onhand,0)) as costvalue,
sum((coalesce(sp.price,0)-coalesce(sb.buydown,0)) * coalesce
(s.onhand,0)) as retailvalue,
sum(((coalesce(sp.price,0)-coalesce(sb.buydown,0)) * coalesce
(s.onhand,0))-
(coalesce(sc.cost,0) * coalesce(s.onhand,0))) as profit
from itemdetails d
left join itemdetails n on (n.header = d.header and n.description
='Consolidator')
left join itemheaders h on (h.reference = d.header)
left join categories g on (g.reference = h.category)
join departments dp on (dp.reference = h.department)
left join stocks s on (s.item = d.reference)
left join storecosts sc on (sc.item = d.reference)
left join icrcurrentitemprice(d.reference) sp on (d.reference =
d.reference)
left join icrcurrentitembuydown(d.reference) sb on (d.reference =
d.reference)
left join id_temp nn on nn.upccode = n.upccode and nn.plu = n.plu and nn.unitcount = n.unitcount
Where
((d.Deleted is null) and (d.description <> 'Consolidator'))
group by 1,2,3,4,5,6,7
order by 1,3,4,5

It might also be possible to do something similar with Firebird 2.0, though then you need to move from WITH to within the LEFT JOIN (more messy in my opinion, so I've never really bothered to learn this properly).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Roland Chie
Sent: 11. februar 2009 15:32
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Invalid expression in the select list Error

Can someone please help me out with this query? When I try to
execute it give this error "Invalid expression in the select list
(not contained in either an aggregate function or the GROUP BY
clause)." The error comes in when I added the subselect but it
returns a single value

select
d.header,
case when (n.reference is null) then
case when d.upccode = '' then d.plu
else
d.upccode
end
else
case when n.upccode = '' then n.plu
else
n.upccode
end
end as UPCCode,
h.manufacturer,
h.brand,
g.description as category,
case when (n.reference is null) then
d.description
else
n.description
end as description,
(select first(1)nn.description from itemdetails nn
where nn.upccode = n.upccode and
nn.plu = n.plu and
nn.unitcount = n.unitcount) as conDescription,
sum(case when (n.reference is null) then coalesce(s.onHand,0)
else
(coalesce(s.onHand,0) * d.unitcount)/n.unitcount
end) as onhnahd,
sum(coalesce(sc.cost,0) * coalesce(s.onhand,0)) as costvalue,
sum((coalesce(sp.price,0)-coalesce(sb.buydown,0)) * coalesce
(s.onhand,0)) as retailvalue,
sum(((coalesce(sp.price,0)-coalesce(sb.buydown,0)) * coalesce
(s.onhand,0))-
(coalesce(sc.cost,0) * coalesce(s.onhand,0))) as profit
from itemdetails d
left join itemdetails n on (n.header = d.header and n.description
='Consolidator')
left join itemheaders h on (h.reference = d.header)
left join categories g on (g.reference = h.category)
join departments dp on (dp.reference = h.department)
left join stocks s on (s.item = d.reference)
left join storecosts sc on (sc.item = d.reference)
left join icrcurrentitemprice(d.reference) sp on (d.reference =
d.reference)
left join icrcurrentitembuydown(d.reference) sb on (d.reference =
d.reference)
Where
((d.Deleted is null) and (d.description <> 'Consolidator'))
group by 1,2,3,4,5,6,7
order by 1,3,4,5