iif and group by

[oaishm]

I have this:

select iif(very long iif statement) as order_number, mid([a],1,2) as larry,more items
from ...
group by iif(very long iif statement), mid([a],1,2)

I get invalid procedure call

If I change it to

select iif(very long iif statement) as order_number, mid([a],1,2) as larry,more items
from ...
group by [order_number], mid([a],1,2)

of course then the iif statement is not part of an aggregate function

So I tried

select first(iif(very long iif statement)) as order_number, mid([a],1,2) as larry,more items
from ...
group by [order_number], mid([a],1,2)

and it wants a paramter for order_number

and to make sure that the iif in the where clause was the actual problem, I did:

select first(iif(very long iif statement)) as order_number, mid([a],1,2) as larry,more items
from ...
group by mid([a],1,2)

which works but is not what I want. So, how does access sql work with aliases and such. In MySql, this took two seconds because you can use the alias anywhere

 

[Joe4]

One way is to imbed your calcualtions into a subquery, i.e.

select s.order_number, s.larry, s.other items
from
(select iif(very long iif statement) as order_number, mid([a],1,2) as larry,more items
from ...) as s
group by s.order_number, s.larry

 

[oaishm]

I tried the nested select and I even tried putting it into two different queries. Alas, still didn't work. This is down right spooky. Dump the first query into a table and group on that first table, then it works, but surely this isn't how vba was invented?