Newbie - converting Access SQL to Oracle

[synapse5150]

I'm having a problem converting a query from Access SQL to Oracle. My problem is with the Access Iif statement. I thought it would be as simple as using the 'DECODE' function, but am having problems. Here's the portion of the Access SQL I am having trouble converting:

SELECT PERSONID,
Sum(IIf([INVOICEDATE]>=#1/1/2002#,IIf([INVOICEDATE]<#2/1/2002#,[SHIPPING],0),0)) AS Jan02U,
Sum(IIf([INVOICEDATE]>=#2/1/2002#,IIf([INVOICEDATE]<#3/1/2002#,[SHIPPING],0),0)) AS Feb02U,....Continues on until 4/1/2009
From NETSALESEXTRACT
Group by PERSONID

Basicaly what I am trying to do is return the [SHIPPING] field if the invoice date is between Jan 1st and Feb 1st. If not, the query returns 0 for that field.

Any help on how to re-write this for Oracle would be greatly appreciated.

Thank you all in advance!!

[skhanal]

Use CASE statement which is more elegant than DECODE.

You can do

SELECT ...
CASE
WHEN date < > THEN
END CASE

[brunobiondo]

Select personid,

sum(
case
when invoicedate >= to_char('1/1/2002') and invoicedate <= to_char('2/1/2002') then shipping
else 0
end
) as jan02u,
sum(
case
when invoicedate >= to_char('2/1/2002') and invoicedate <= to_char('3/1/2002') then shipping
else 0
end
) as feb02u,
.
.
.

From netsalesextract
group by personid

[brunobiondo]

Select personid,

sum(
case
when invoicedate >= to_char('1/1/2002') and invoicedate <= to_char('2/1/2002') then shipping
else 0
end
) as jan02u,
sum(
case
when invoicedate >= to_char('2/1/2002') and invoicedate <= to_char('3/1/2002') then shipping
else 0
end
) as feb02u,
.
.
.

From netsalesextract
group by personid

[brunobiondo]

Select personid,

sum(
case
when invoicedate >= to_char('1/1/2002','mm/dd/yyyy') and invoicedate <= to_char('2/1/2002','mm/dd/yyyy') then shipping
else 0
end
) as jan02u,
sum(
case
when invoicedate >= to_char('2/1/2002','mm/dd/yyyy') and invoicedate <= to_char('3/1/2002','mm/dd/yyyy') then shipping
else 0
end
) as feb02u,
.
.
.

From netsalesextract
group by personid

[brunobiondo]

Select personid,

sum(
case
when invoicedate >= to_date('1/1/2002','mm/dd/yyyy') and invoicedate <= to_date('2/1/2002','mm/dd/yyyy') then shipping
else 0
end
) as jan02u,
sum(
case
when invoicedate >= to_date('2/1/2002','mm/dd/yyyy') and invoicedate <= to_date('3/1/2002','mm/dd/yyyy') then shipping
else 0
end
) as feb02u,
.
.
.

From netsalesextract
group by personid

[brunobiondo]

The decode function can operate only on discrete values
(e.g. decode(id,1,'1',2,'2',null) ), so you cannot write comparison operators like '=', '>' etc. inside the function (e.g. you cannot write decode(id,<1,'1st',>=1 and < 2,'2nd',null) ).
So you have to do some trick, first grouping by month-year, then using decode on each group returned.So, your query can be rewritten as:
select to_char(INVOICEDATE,'mmyyyy') /*you can omit this-just to see the period of interest*/,
sum(decode(to_char(INVOICEDATE,'mmyyyy'),'012002', SHIPPING,0)) as JAN02U,
sum(decode(to_char(INVOICEDATE,'mmyyyy'),'022002', SHIPPING,0)) as FEB02U,
sum(decode(to_char(INVOICEDATE,'mmyyyy'),'032002', SHIPPING,0)) as MAR02U,
sum(decode(to_char(INVOICEDATE,'mmyyyy'),'042002', SHIPPING,0)) as APR02U
FROM NETSALESEXTRACT
group by to_char(INVOICEDATE,'mmyyyy')
order by to_char(INVOICEDATE,'mmyyyy')

[synapse5150]

Thanks all - You have been a lot of help. The Case statement structure works perfectly and performs better than the Decode function in my query, and now that I have the syntax figured out I can take it from here!