By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Need help converting oracle decode statement for MS Access

ollyb303
P: 74
Hello,

I have been using the following expression in Access as part of a statement to query an Oracle database:

Expand|Select|Wrap|Line Numbers
  1. (Sum(CASE WHEN STATS_DAILY_SA.LOGIN_TIME > (STATS_DAILY_SA.SCHEDULED_TIME - DECODE(STATS_DAILY_SA.EXCEPTION_TIME, Null, 0, 
  2. STATS_DAILY_SA.EXCEPTION_TIME)) THEN 0 ELSE ((STATS_DAILY_SA.SCHEDULED_TIME - DECODE(STATS_DAILY_SA.EXCEPTION_TIME, Null, 0, 
  3. STATS_DAILY_SA.EXCEPTION_TIME)) - STATS_DAILY_SA.LOGIN_TIME) END)/Sum(STATS_DAILY_SA.SCHEDULED_TIME)*100)
It works fine.

However, I now need to use the same statement (well, get the same result at least) within Access - the Query I will be querying, "Query2" has the same fields, but I am trying to use this expression as part of a crosstab query:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM (Sum(CASE WHEN Query2.LOGIN_TIME > (Query2.SCHEDULED_TIME - DECODE(Query2.EXCEPTION_TIME, Null, 0, 
  2. Query2.EXCEPTION_TIME)) THEN 0 ELSE ((Query2.SCHEDULED_TIME - DECODE(Query2.EXCEPTION_TIME, Null, 0, 
  3. Query2.EXCEPTION_TIME)) - Query2.LOGIN_TIME) END)/Sum(Query2.SCHEDULED_TIME)*100) AS SumOfSTAT
This doesn't work, giving me a 3075 Error - syntax error (missing operator) in query expression...

I first assumed that this was due to using the Decode function (which I don't believe works in Access?), so I tried using IIFs and even Switch statements, but I'm getting similar errors.

Can anyone help with this, I'm getting a serious headache here!!

Many thanks,

Olly
Feb 4 '09 #1
Share this Question
Share on Google+
1 Reply


ollyb303
P: 74
Solved this by converting my Nulls to zero on the way into query2 using NVL, then used a simpler IIF statement for the crosstab:

Expand|Select|Wrap|Line Numbers
  1. (Sum(IIf(Query2.LOGIN_TIME>(Query2.SCHEDULED_TIME-Query2.EXCEPTION_TIME),0,(Query2.SCHEDULED_TIME-Query2.EXCEPTION_TIME-Query2.LOGIN_TIME)))/Sum(Query2.SCHEDULED_TIME)*100)
Feb 4 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.