Converting Access Query to Oracle SQL 1

[cantona]

I have an access query which I need to convert to Oracle SQL. The access query contains the following 2 expressions,

Funding: IIf([SPCL_FE_HE_FUNDED] Is Null, [FE_HE_FUNDED], [SPCL_FE_HE_FUNDED])

Hours: IIf([FUNDING] In (“40”,”41”,”42”),0,[FES_POSSIBLE_HOURS])

Ive translated the first into..

Case When spcl_fe_he_funded Is Null then fe_he_funded else spcl_fe_he_funded end As Funding,

However, as the second one uses Funding which is created in the first expression im not sure how I can replicate it? Can anyone help?

 

[cmmrfrds]

There are a number of ways to do it, but probably the easiest is to expand the case statement to a 'searched case' statment and handle the logic in the one statement. It evaluates the 'whens' from top down.

General idea.

case
When spcl_fe_he_funded in ('40','41','42') then 0
When fe_he_funded in ('40','41','42') then 0
else FES_POSSIBLE_HOURS end as Funding

 

[jimirvine]

Funding: IIf([SPCL_FE_HE_FUNDED] Is Null, [FE_HE_FUNDED], [SPCL_FE_HE_FUNDED])

Could have been handled in Access with the Nz function and likewise, can be handled in Oracle with the NVL function

NVL(SPCL_FE_HE_FUNDED,FE_HE_FUNDED) funded

The second example should (as you had already done with the first) be converted into a searched case expression:

 CASE WHEN NVL(SPCL_FE_HE_FUNDED,FE_HE_FUNDED) In (“40”,”41”,”42”) 
      THEN 0 
      ELSE FES_POSSIBLE_HOURS 
      END hours

If you are using the NVL function, the datatypes of the two parameters MUST match.
Finally, are you sure that you want to be using '40','50' etc. It looks to me like the should be numbers and not text.

 

[cantona]

Hi Jim,

Thanks for your help! Ive now got it working as i wanted!

 

[jimirvine]

No probs. Glad you got it working