Convert IIF to DECODE 1

[Zeroanarchy]

Hi is there a way of converting the following IIF statement from access into a Decode statement in Oracle?

Payable: IIf([AMOUNT]=0,[AMOUNTDESCRIPTION],IIf([ORDER]=31,Null,FormatCurrency([AMOUNT])))

Thanks

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[SantaMufasa]

Yes, Zero, we could probably formulat a DECODE statement, but it appears that a CASE construct would better suit your conditions. I'll respond with a CASE statement for you if you could please confirm, using a plain-English functional narrative of what you want (i.e., confirm the translation of your IIF statement). (I'm not familiar with what the "Payable:" component of your syntax is doing.)

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Zeroanarchy]

Hey SantaMufasa,

Below is the actual staement including table names

Payable: IIf([Productserviceannualmax].[AMOUNT]=0,[Productserviceannualmax].[AMOUNTDESCRIPTION],IIf([table1].[ORDER]=31,Null,FormatCurrency([Productserviceannualmax].[AMOUNT])))

* "Payable" just renames the Column header to Payable.
* [Productserviceannualmax] is a table name
* [Table1] is a table name
* [Order]is a field value
* Format Currency, just converts the value of Amount into Currency value.

Hope this helps

Thanks for your help.

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[SantaMufasa]

Zero,

(First, I'll bet that table1 doesn't really have a column named "ORDER" since that is an Oracle key word that cannot be a column name.)

I'll assert a functional narrative of your code (as I interpret it), then I'll assert some Oracle CASE code that represents that functional narrative. (If my functional narrative misinterprets your IIFs, then please advise.):

1) Determine the value of ProductServiceAnnualMax's AMOUNT.
2) If that value = 0, then return the value of ProductServiceAnnualMax's AMOUNTDESCRIPTION.
3) Otherwise, Determine the value of Table1's ORDER.
4) If that value = 31, then return a NULL.
5) If none of the above conditions is TRUE, then return the value that results from the function, "FormatCurrency" which argument is ProductServiceAnnualMax's AMOUNT.
6) Label the result of above, "Payable".

To give you a proof-of-concept, I created a couple of rows of bogus data for each of your tables and a simple rendition of your function. Here, then, is the Oracle CASE code that represents the functional narrative, above:

(Iteration 1)
select ProductServiceAnnualMax.AMOUNT
      ,ProductServiceAnnualMax.AMOUNTDESCRIPTION
      ,table1.ord
      ,case when ProductServiceAnnualMax.AMOUNT = 0
                 then ProductServiceAnnualMax.AMOUNTDESCRIPTION
            when table1.ord = 31 then null
            else FormatCurrency(ProductServiceAnnualMax.AMOUNT)
       end Payable
  from ProductServiceAnnualMax, table1
 where table1.ord = 31
/
AMOUNT AMOUNTDESC        ORD PAYABLE
------ ---------- ---------- ----------
     0 Zero               31 Zero
     1 One                31

(Iteration 2)
col payable format a10
select ProductServiceAnnualMax.AMOUNT
      ,ProductServiceAnnualMax.AMOUNTDESCRIPTION
      ,table1.ord
      ,case when ProductServiceAnnualMax.AMOUNT = 0
                 then ProductServiceAnnualMax.AMOUNTDESCRIPTION
            when table1.ord = 31 then null
            else FormatCurrency(ProductServiceAnnualMax.AMOUNT)
       end Payable
  from ProductServiceAnnualMax, table1
 where table1.ord = 32
/

AMOUNT AMOUNTDESC        ORD PAYABLE
------ ---------- ---------- ----------
     0 Zero               32 Zero
     1 One                32        $1.

Let us know if this helps resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

First, I'll bet that table1 doesn't really have a column named "ORDER" since that is an Oracle key word that cannot be a column name

but Zero mentioned that he was initially working with an Access db (where ORDER can - not saying that it should[/] - be used as a column / field name. He may also have decided to still use ORDER to match up to the Access DB and used "ORDER" to make it work.
(Just my 2c )

 

[Zeroanarchy]

Hi SantaMufasa, thankyou for you help on this issue. I do apologise for not getting back to you sooner, I have been waiting for the table1 table to be created in the database by one of the IT guys and that just happened.

Thanks jimirvine, for the pickup on "ORDER", yes it was a field in a table but I quickly changed my field name request to "SORTORDER".

Is there any other Reserved keywords, that I am not able to use as field??

Also on the topic of Oracle can anyone recommend any reference books that relate to the SQL element of Oracle.

Thanks


ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[SantaMufasa]

Yes, Zero, click here for Oracle Reserved and Keywords.

Click on this link: Oracle SQL Books for a whole list of books on Amazon.com to fit a variety of budgets.

Let us know if this information resolves your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]