×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SUM on conditional in a SELECT ?

SUM on conditional in a SELECT ?

SUM on conditional in a SELECT ?

(OP)
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

RE: SUM on conditional in a SELECT ?

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

RE: SUM on conditional in a SELECT ?

(OP)
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

RE: SUM on conditional in a SELECT ?

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

RE: SUM on conditional in a SELECT ?

(OP)
<<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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close