Home » SQL & PL/SQL » SQL & PL/SQL » Substitute for the IIF function used in Access
Substitute for the IIF function used in Access [message #224544] Wed, 14 March 2007 11:36 Go to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
What would I substitute for the IIF function I used in ACCESS for the following PL/SQL and get the same results:

SELECT Sum(m.MEMB_AMT_PAID) AS AmtPdYTD
FROM MEMB m INNER JOIN MEMB_ENTITY me ON m.MEMB_NUMBER = me.MEMB_NUMBER
WHERE m.MEMB_APPEAL_CODE = 'AGS'
AND m.MEMB_TYPE_CODE = 'AI'
AND to_char(m.start_date,'MM')=
IIf(to_char(m.start_date,'MM') < 7, to_char(sysdate,'YYYY')-1, to_char(sysdate,'YYYY')+1)

Thanks!
Re: Substitute for the IIF function used in Access [message #224545 is a reply to message #224544] Wed, 14 March 2007 11:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
My first guess would be case, but if IIF means what I think it does (if arg1 = true then arg2 else arg3), then your query does not make sense. It compares a month to a year..
Re: Substitute for the IIF function used in Access [message #224551 is a reply to message #224545] Wed, 14 March 2007 11:48 Go to previous messageGo to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
That is true in what the IIF is doing. I am trying to see if in the start_date field which contains a date like 1/10/2006 has a month number (like 1 for this one ) that is < 7. if it is then pull in the date with today's date in years equal to 2006. Otherwise pull in dates with the year + 1 of today's date in the start_date field. I trying to bring in dates according to our fiscal year dates. If I use the case stmt, can I use it in the where clause?

Thanks for your help!
Re: Substitute for the IIF function used in Access [message #224558 is a reply to message #224544] Wed, 14 March 2007 12:08 Go to previous messageGo to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
Sorry suppose to be:

AND to_char(m.start_date,'YYYY')=
IIf(to_char(m.start_date,'MM') < 7, to_char(sysdate,'YYYY')-1, to_char(sysdate,'YYYY')+1)
Re: Substitute for the IIF function used in Access [message #224561 is a reply to message #224551] Wed, 14 March 2007 12:13 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Yes you can use CASE in a WHERE clause, but you are comparing a character string to the number 7, which will fail for every month from July-September, and you are doing math on character strings later in that clause.
Re: Substitute for the IIF function used in Access [message #224564 is a reply to message #224561] Wed, 14 March 2007 12:15 Go to previous messageGo to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
How would you do it. would it be different?
Re: Substitute for the IIF function used in Access [message #224596 is a reply to message #224564] Wed, 14 March 2007 13:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I would do this:
and extract (year from add_months(m.start_date, 7)) = extract (year from sysdate)


Note: done of the top of my head, it might very well be wrong, but it's just to give you an idea of the direction I would choose.

[Edit: see? Wink had an error in the add_months; very confusing, because you have both a year + 1 and a year - 1. Not correct)]

[Updated on: Wed, 14 March 2007 13:40]

Report message to a moderator

Re: Substitute for the IIF function used in Access [message #224775 is a reply to message #224596] Thu, 15 March 2007 07:48 Go to previous messageGo to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
OK. I will try that. Thanks for your help.
Re: Substitute for the IIF function used in Access [message #224833 is a reply to message #224596] Thu, 15 March 2007 11:52 Go to previous messageGo to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
that ran in my sql, but I need to check the month of the start_date field. If the month is < 7 (which is July, right) then I want to bring in all the records whose start_date's year is year(sysdate) +1 because the Fiscal year will include 2008 after Jan. But, if the month is > 7 then it will bring in the start_date whose year = year(sysdate) that would be 2007. Does that make sense? it is confusing because our fiscal year begins July 1st and ends June 30th. So there is always data of 2 years in there. so how would I insert my case statement in with my where clause?
Re: Substitute for the IIF function used in Access [message #225322 is a reply to message #224833] Mon, 19 March 2007 12:36 Go to previous message
mxd198
Messages: 28
Registered: December 2005
Junior Member
Does anyone know where I could insert my case statement based on the my last reply. thanks.
Previous Topic: How to do this using procedure
Next Topic: Login as a different schema in the middle of a procedure
Goto Forum:
  


Current Time: Sun Dec 04 04:28:55 CST 2016

Total time taken to generate the page: 0.04304 seconds