Home » SQL & PL/SQL » SQL & PL/SQL » IIF and Oracle (Windows 2003 and Oracle 10g)
IIF and Oracle [message #443904] Thu, 18 February 2010 00:05 Go to next message
aijaz786
Messages: 91
Registered: February 2010
Member
I will appreciate if someone could transform this MS Access syntax in Oracle 10g format.

I checked the postings but I did not find any specific sol.

IIf([No_of_files] Between 1 And 10 And [Timedur] Between 1 And 30,Yes,IIf([No_of_files] Between 11And 3 And [Timedur] Between 1 And 60,Yes,No)) TestField



Thanks

Re: IIF and Oracle [message #443906 is a reply to message #443904] Thu, 18 February 2010 00:09 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Search for 'CASE statement in oracle' or 'DECODE in oracle' in Google.

regards,
Delna
Re: IIF and Oracle [message #443907 is a reply to message #443904] Thu, 18 February 2010 00:10 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
I did but I could not get right conversion
Re: IIF and Oracle [message #443909 is a reply to message #443904] Thu, 18 February 2010 00:11 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
For time duration you have to search for date functions available in Oracle.

regards,
Delna
Re: IIF and Oracle [message #443910 is a reply to message #443904] Thu, 18 February 2010 00:12 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
I just need equivalent of this statement in Oracle 10g

Thanks
Re: IIF and Oracle [message #443911 is a reply to message #443907] Thu, 18 February 2010 00:15 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Are you want to use this IIF in SQL SELECT statement?
If yes, then you can't achive this functionality as it is, because Oracle SQL does not support boolean type.
You can do it using 1/0.
In PL/SQL you can play with boolean data.

regards,
Delna
Re: IIF and Oracle [message #443920 is a reply to message #443904] Thu, 18 February 2010 00:38 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Please some body write this statement using Oracle Case.

Thanks.
Re: IIF and Oracle [message #443925 is a reply to message #443920] Thu, 18 February 2010 00:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In general, people are unwilling to do other people's work for them, especially if the one requesting it does not show any sign of what (s)he did or tried him/herself.
Show us what you tried so far and where you got stuck. We will help you out waaay faster than when you just demand others to do it for you.
Re: IIF and Oracle [message #443928 is a reply to message #443904] Thu, 18 February 2010 00:50 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
I tried the following
decode(((No_of_Physical_files between 1 and 500) and (uploaddurationdw between 1 and 30)),'YES',
decode((No_of_Physical_files between 1 and 500) and (uploaddurationdw between 1 and 30)),'YES','NO') WithinTargetRange


or


CASE WHEN (No_of_Physical_files between 1 and 500) and (uploaddurationdw between 1 and 30)
THEN 'YES' END as WithinTargetRange,
CASE WHEN (No_of_Physical_files between 501 and 3000) and (uploaddurationdw between 1 and 180)
THEN 'YES END as WithinTargetRange
ELSE 'NO' as WithinTargetRange
END

Not working.

Re: IIF and Oracle [message #443939 is a reply to message #443928] Thu, 18 February 2010 01:23 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
You have too many 'end's in your block. Here's a hint, end comes at the ... end. You are only trying to create a single column. Therfore, ony one "as WithinTargetRange" is necessary.

Think

case when condition then value
     when condition then value
     when condition then value
     else vale
end as name


[edit: PS "Not working" is not a valid error in Oracle. Try in future to give more useful information]

[Updated on: Thu, 18 February 2010 01:25]

Report message to a moderator

Re: IIF and Oracle [message #443954 is a reply to message #443904] Thu, 18 February 2010 02:41 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
 create table RB_TEST( A NUMBER);

INSERT INTO RB_TEST(A)VALUES(1);
INSERT INTO RB_TEST(A)VALUES(2);
INSERT INTO RB_TEST(A)VALUES(3);
COMMIT;


SELECT A ,  CASE WHEN A = 1 THEN 'ONE'
                 WHEN A = 2 THEN 'TWO'
                 WHEN A = 3 THEN 'THREE'
            END IN_WRODS
FROM RB_TEST


         A IN_WR
---------- -----
         1 ONE
         2 TWO
         3 THREE


Hope this helps.
Re: IIF and Oracle [message #443956 is a reply to message #443954] Thu, 18 February 2010 02:43 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Hope this helps.

More than pablolee's answer? I doubt.

Regards
Michel
Previous Topic: problen in ref cursor
Next Topic: Improving performance of a VIEW
Goto Forum:
  


Current Time: Mon Sep 26 07:50:26 CDT 2016

Total time taken to generate the page: 0.12026 seconds