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: 7901
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: 2882
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 messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Hope this helps.

More than pablolee's answer? I doubt.

Regards
Michel
Re: IIF and Oracle [message #677192 is a reply to message #443904] Thu, 29 August 2019 11:38 Go to previous messageGo to next message
Jethi
Messages: 1
Registered: August 2019
Junior Member
HI,
ANY BODY GOT THE CORRECT SOLUTIONS ON THIS.

Can any please help how to use nested DECODE for multiple columns values.

Like DECODE(Col1,search , result,(DECODE(COL2....
Re: IIF and Oracle [message #677196 is a reply to message #677192] Thu, 29 August 2019 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
If you don't know how to format the code, learn it using SQL Formatter.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Explain why the previous replies don't answer your issue.

[Updated on: Thu, 29 August 2019 13:32]

Report message to a moderator

Re: IIF and Oracle [message #677197 is a reply to message #677192] Thu, 29 August 2019 13:39 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
The correct solution, based on tge small amount of information that you have posted, is... Don't.
Use a case expression.
Re: IIF and Oracle [message #677246 is a reply to message #677197] Thu, 05 September 2019 13:05 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
case
when [No_of_files] Between 1 And 10 And [Timedur] Between 1 And 30)
     ( ([No_of_files] Between 11And 3 And [Timedur] Between 1 And 60) then
 'Yes'
else 
'No'
end 

I leave to you to figure out how to determine the time duration and the number of files

[Updated on: Thu, 05 September 2019 13:06]

Report message to a moderator

Re: IIF and Oracle [message #677257 is a reply to message #677192] Fri, 06 September 2019 08:32 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Instead of reviving and hijacking a nine-year-old thread you should start your own, even if the subject is the same.

AND WRITING IN ALL CAPS COMES ACROSS IN THE PRINTED WORD AS SHOUTING!
Previous Topic: ORA-29913 ORA-29400 KUP-00554
Next Topic: BI Publisher Apex web Services
Goto Forum:
  


Current Time: Thu Mar 28 06:44:33 CDT 2024