SUM on conditional in a SELECT ?

[bobhagan]

I'm trying to calculate clients' age ranges and then sum them.

CREATE VIEw V_FOODROLLUP (ENC_DT, ELDER) AS
     SELECT e.enc_dt, 
     SUM(IIF (((current_date - c.CLI_BDATE)/365) > 65 ,1,0))
     FROM encountr e, client c
     wHERE c.client_id = e.client_id
     GROUP BY  e.enc_dt

I originally wrote this in Foxpro, where it works fine. I don't find an IIF() in the IB manual, but very strangely the query errors as > 65 (at the greater than sign) is an unknown token. Is this sort of calculation possible in IB?

Any help appreciated

Bob Hagan

 

[Otto]

The IB's SQL do not have IIF keyword, you have to write or use UDFs if you need IIF like functions.
But here is a query that gives you the desired result (I hope ):
SELECT e.enc_dt, 1
FROM encountr e, client c
wHERE c.client_id = e.client_id and
(extract(year from current_date)-extract(year
from c.CLI_BDATE))>65
GROUP BY e.enc_dt
union all
SELECT e.enc_dt, 0
FROM encountr e, client c
wHERE c.client_id = e.client_id and
(extract(year from current_date)-extract(year
from c.CLI_BDATE))<=65
GROUP BY e.enc_dt

 

[bobhagan]

OK, I'll play around with it.

I'm actually looking for children, adults and elders, with a null date = adult, and a column for each category in each date row, so its a bit more complicated than my sample. This at least puts me on a track that can work.

I don't use UNION much, how will this perform?

Thanks

Bob Hagan

 

[Otto]

I think the union is fast.
But if you want much better (or the most) performance you should write a stored procedure. It is very simple for your problem.

Otto

 

[bobhagan]

<<But if you want much better (or the most) performance you should write a stored procedure>>

I'm going to have to look at that. I don't have any idea how big my potential client's tables are yet.

The other day I did a view to produce detail records and then used Crystal to figure out the categories. Also fast and pretty easy.

Thanks again

Bob Hagan