Link to home
Start Free TrialLog in
Avatar of ht055
ht055

asked on

iif function

hi,

Is there a iif function in oracle database?

Could any help me for this scenaria.

I have a field called credit in customer table. This field store value 1 or 0.

How to write a sql statement that when the value is 1, it refer to "Yes", if value is 0, refer to "No'. This is because I want to display value "Yes" for 1, "No" for 0.


Sometime like this.

select credit (iif credit is 0, "Yes","No") from customer.

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of dbalaski
dbalaski

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ht055
ht055

ASKER

Hi dbalaski,

You're excellent! Thanks!
Glad I could be of help ---
I noticed a typo --  
the line is:  but all is not lost -- you can do what you need with the decide function.

the line should have read:     but all is not lost -- you can do what you need with the DECODE function...
(sorry -- it was late when I typed this in  --11:30 EST)...

sincerely,
dBalaski
Avatar of ht055

ASKER

Hi dBalaski,

Can I use decode function for update this credit field process as I allows user to edit this credit field. So they can enter Yes or No. However, data type for this field is only accept 1 (for 'Yes'), 0 (for 'No').

Could you show me the example on how to do it?

Thanks.


Sure,  it can be done --   it can be done
by passing in a string or a BIND VARIABLE to the decode in the update.
I am going to do it with a BIND VARIABLE....

I inserted a new  CUSTID for update    CUSTID=4  with credit set to 1

I set the bind variable  has_credit to  "YES"

and simply use that bind variable in the update with a decode,

examples can be seen below...

Hope this answers your question,
sincerely,
dBalaski


========================================================
SQL> select * from customer;
    CUSTID CUSTNAME            CREDIT
---------- --------------- ----------
         1 Has Credit               0
         2 No Credit                1
         3 Null Credit
         4 TESTUPDATE               1

4 rows selected.

SQL> variable has_credit varchar2(3);
SQL> begin
  2     :has_credit := 'YES';
  3  end;
  4  /
PL/SQL procedure successfully completed.

SQL> get decodeupdate.sql
  1  update customer
  2    set CREDIT= decode(:has_credit,'YES',0,'NO',1)
  3* where CUSTID=4
SQL> /
1 row updated.

SQL> commit;
Commit complete.

SQL> select * from customer;
    CUSTID CUSTNAME            CREDIT
---------- --------------- ----------
         1 Has Credit               0
         2 No Credit                1
         3 Null Credit
         4 TESTUPDATE               0
4 rows selected.

===========   wahooo!    custid 4 now has credit!   using the decode  to update...
Of course,  in the above example --
we are converting the STRING stored in "has_credit"
  "YES"  is converted to 0
  "NO"   is converted to 1

looking at it,   I should have put a default value for things that do not match the search criteria.
ie:
  update customer
  set CREDIT= decode(:has_credit,'YES',0,'NO',1,1)
  where CUSTID=4

This way,  anything that doesn't match "YES"  for HAS_CREDIT, will be set to 1 (aka NO)

dB
did this solve the problem & answer the question?

dbalaski
Avatar of ht055

ASKER

Yes!
Hi,

Thanks for the good grade !  Glad I could  help out.

dBalaski
www.rdbms.org