Members   Search   Help    Register    Login   Home
Home » SQL & PL/SQL » SQL & PL/SQL » Need help converting Access SQL to Oracle (Oracle 10g)
icon5.gif  Need help converting Access SQL to Oracle [message #429548] Wed, 04 November 2009 08:38 Go to next message
synapse5150
Messages: 2
Registered: November 2009
Location: St. Louis, MO
Junior Member
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!1
Report message to a moderator
Re: Need help converting Access SQL to Oracle [message #429553 is a reply to message #429548] Wed, 04 November 2009 08:55 Go to previous messageGo to next message
ThomasG
Messages: 1941
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
In Oracle you can use the CASE statement in a similar way.

And if you want to use fixed date constants in your SQL, always use an explicit to_date with the appropriate format mask.
Report message to a moderator
Re: Need help converting Access SQL to Oracle [message #429554 is a reply to message #429548] Wed, 04 November 2009 09:00 Go to previous messageGo to next message
Kevin Meade
Messages: 1050
Registered: December 1999
Senior Member
There are two approaches to your specific need:

1) you can try to rewrite the sql using equivelant transforms (eg. substitute decode for if)

2) you can create a statement of specification from evaluation of the original, and then ignore the origianl and write new code from the specification.

So I have a question and a suggestion:

? Did you ever look at the statement and then write down what you think it does ? In your case you seem to understand it well enough, and it looks simple enough that maybe it would be overkill to create a formal spec. Then again, it can't hurt and it shows others your professionalism to have done so, so maybe it would be work considering. A written spec also affords you the opportunity to show your spec to others for commentary to see if you got it right, or if there is a needed change, or even if the original is wrong (which happens more often than we would like to admit).

Try using CASE and not DECODE. CASE is more robust than DECODE and easily handles inequality tests.

Good luck, Kevin
Report message to a moderator
Re: Need help converting Access SQL to Oracle [message #429555 is a reply to message #429553] Wed, 04 November 2009 09:00 Go to previous messageGo to next message
JRowbottom
Messages: 5371
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So you'd need something like:
SELECT Personid
      ,sum(case when invoice_date between to_date('01-jan-2002','dd-mon-yyyy') and to_date('01-feb-2002','dd-mon-yyyy') then SHIPPING else 0 end)
FROM <table>
GROUP BY personid;
Report message to a moderator
icon14.gif  Re: Need help converting Access SQL to Oracle [message #429564 is a reply to message #429555] Wed, 04 November 2009 09:17 Go to previous message
synapse5150
Messages: 2
Registered: November 2009
Location: St. Louis, MO
Junior Member
Thanks all - That helps a lot.

JRowbottom - You're example is exactly what I was needing. I have the gaps filled now and can continue on.
Report message to a moderator
Previous Topic:Help with Creating a Database
Next Topic:Retrieving a resource's working days in financial periods between hire and termination date
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 1 20:47:09 CST 2009

Total time taken to generate the page: 1.72680 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.