CODEWITHSUNDEEP
oracle-databaseplsqldecode
Hal
Hal

Reputation: 591

SQL - A simpler function than decode

I am working with a pl/sql procedure. I have an initialized variable myvar and I want to check its value : if it does not contain 'Z', I want it to contain 'P'.

I am currently doing it this way:

myvar := decode(myvar,'Z','Z','P');

I was just wondering if there was a simplier way to do this. I mean, decode is already simple, but I feel it's weird to specify the content of the variable while it is already in it !

If such a function would exist, it would look like this:

Function myfunction(a In Varchar2, b In Varchar2, c In Varchar2) 
Return Varchar2 
Is               
Begin
      if a <> b
      then
           return c;
      end if;
      return a;
End myfunction;

Any help would be appreciated !

Upvotes: 2

Views: 14714

Answers (4)

TrojanName
TrojanName

Reputation: 5355

To answer your original question of whether there is a simpler way, there is also this:

if myvar <> 'Z' then 
  myvar := 'P'
end if;

Upvotes: 0

FerranB
FerranB

Reputation: 36777

I agree the best option is to use CASE expression:

CASE myvar WHEN 'Z' THEN 'Z' ELSE 'P' END

Another approach if you feel happy with DECODE is to run this query:

SELECT decode(myvar,'Z','Z','P') 
  INTO myvar
  FROM DUAL;

Upvotes: 2

Jokke Heikkil&#228;
Jokke Heikkil&#228;

Reputation: 928

Put that function of yours to the program's declaration section and use it!

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132570

There is no built-in function that does exactly what you want.

You could use CASE rather than DECODE:

CASE myvar WHEN 'Z' THEN 'Z' ELSE 'P' END

It doesn't make it any shorter though!

Upvotes: 14

Related Questions