Convert Access SQL into Oracle equivalent

[ronaldmacdonald]

I have Access code that sums the number of occurances of an integer calculation [Days taken]

Oracle doesn't like IIf

SELECT Sum(IIf([Days taken] Between 1 And 3,1,0)) AS [1-3]

 

[SantaMufasa]

Ronald,

There are multiple methods to achieve your result in Oracle. Here are four of those methods:

select sum(1) from <table_name> where days_taken between 1 and 3;

select count(*) from <table_name> where days_taken between 1 and 3;

select sum(case when days_taken between 1 and 3 then 1) from <table_name>;

select sum(decode(days_taken,1,1,2,1,3,1)) from <table_name>;

Let us know which method you prefer.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Sorry, Ronald...I forgot the "as [1-3]" part of your question.

In each of my "select" statements above, you can define the column alias with the addition of the following code snippet:

select ... as "[1-3]" from...

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

...and the word as is optional in this context in Oracle. I.e., this works, too:

select ... "[1-3]" from...

Okay, now I think I'm done. <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Oops...one more correction/addition -- I forgot the "end" to terminate the "case" statement, above. It should read:

select sum(case when days_taken between 1 and 3 then 1 [B][I]end[/I][/B]) from <table_name>;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”