DB2 for Z/OS - SQL - Create Function Help 1

[markronz]

Hello everyone-
I am working with DB2 for z/OS and I need to create a User Defined Function. Basically, there's a bunch of SQL statements that I will be executing. Currently, in each of these locations, the SQL executes a case statement. This same case statement is in all the different locations, which is, of course, not good design. I would like to create a UDF so that this code is located in one location, versus 50 different SQL statments...

So, I need some help creating this UDF. This is what I have so far, but I'm quite sure I don't have it correct yet...

The input is a varchar(8).
The output is supposed to be a varchar(25).

Right now I have it coded as a series of IF ELSE statements, I wasn't sure if I could do a case statement in the function.

CREATE FUNCTION UDF_StatusLookup( @status varchar(8))
RETURNS @retStatus string(varchar(25))
AS 
BEGIN
IF @status = 'PROD' Then
@retStatus = 'Production'
ELSE
IF @status = 'PROD_ST1' Then
@retStatus = 'Production - Status 1'
ELSE
IF @status = 'PROD_ST2' Then
@retStatus = 'Production - Status 2'
ELSE
IF @status = 'TEST' Then
@retStatus = 'Test'
ELSE
IF @status = 'TEST_ST1' Then
@retStatus = 'Test - Status 1'
ELSE
IF @status = 'TEST_ST2' Then
@retStatus = 'Test - Status 2'
END

So could someone help me out with this? I need to have the above code corrected as is with the IF ELSE's or else re-written using a CASE statement if possible. I'd appreciate it!

 

[markronz]

Oops, just noticed that I had 'string' in my funciton before... You get the idea of what I was trying to do anyway.

Here's my stab at a case statement in my function...

CREATE FUNCTION UDF_StatusLookup(status varchar(8))
RETURNS VARCHAR(25)
BEGIN
DECLARE retStatus VARCHAR(25)
SET retStatus = (CASE status
WHEN 'PROD' THEN 'Production'
WHEN 'PROD_ST1' THEN 'Production - Status 1'
WHEN 'PROD_ST2' THEN 'Production - Status 2'
WHEN 'TEST' THEN 'Test'
WHEN 'TEST_ST1' THEN 'Test - Status 1'
WHEN 'TEST_ST1' THEN 'Test - Status 1'
ELSE 'Unknown'
END);
RETURN retStatus;
END;

So if someone could help me correct either of these attempts of mine, I would appreciate it!!!

 

[mikrom]

I don't have access to DB2 for z/OS but this works for me on DB2 UDB for i5/OS (IBM iSeries):

[COLOR=#804040][b]CREATE[/b][/color] [COLOR=#6a5acd]FUNCTION[/color] UDF_StatusLookup(status [COLOR=#2e8b57][b]varchar[/b][/color]([COLOR=#ff00ff]8[/color]))
  RETURNS [COLOR=#2e8b57][b]VARCHAR[/b][/color]([COLOR=#ff00ff]25[/color])
  LANGUAGE SQL
  DETERMINISTIC
  CONTAINS SQL
  RETURNS [COLOR=#6a5acd]NULL[/color] [COLOR=#6a5acd]ON[/color] [COLOR=#6a5acd]NULL[/color] INPUT
  NO EXTERNAL ACTION  
[COLOR=#6a5acd]BEGIN[/color]
  [COLOR=#6a5acd]RETURN[/color] (
    CASE status
      WHEN [COLOR=#ff00ff]'PROD'[/color] [COLOR=#6a5acd]THEN[/color] [COLOR=#ff00ff]'Production'[/color]
      WHEN [COLOR=#ff00ff]'PROD_ST1'[/color] [COLOR=#6a5acd]THEN[/color] [COLOR=#ff00ff]'Production - Status 1'[/color]
      WHEN [COLOR=#ff00ff]'PROD_ST2'[/color] [COLOR=#6a5acd]THEN[/color] [COLOR=#ff00ff]'Production - Status 2'[/color]
      WHEN [COLOR=#ff00ff]'TEST'[/color] [COLOR=#6a5acd]THEN[/color] [COLOR=#ff00ff]'Test'[/color]
      WHEN [COLOR=#ff00ff]'TEST_ST1'[/color] [COLOR=#6a5acd]THEN[/color] [COLOR=#ff00ff]'Test - Status 1'[/color]
      WHEN [COLOR=#ff00ff]'TEST_ST1'[/color] [COLOR=#6a5acd]THEN[/color] [COLOR=#ff00ff]'Test - Status 1'[/color]
      [COLOR=#6a5acd]ELSE[/color] [COLOR=#ff00ff]'Unknown'[/color]
    [COLOR=#6a5acd]END[/color]);
[COLOR=#6a5acd]END[/color]
;

The above statement creates the function in current schema, (which I set before with SET SCHEMA mySchema)

This short test

select  
  UDF_StatusLookup('prod'),
  UDF_StatusLookup('PROD'),
  UDF_StatusLookup('TEST')
from 
  sysibm.sysdummy1

shows the result

....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....
UDF_STATUSLOOKUP           UDF_STATUSLOOKUP           UDF_STATUSLOOKUP         
Unknown                    Production                 Test                     
********  End of data  ********

Maybe it would be similar on DB2 on z/OS - try it.

 

[markronz]

Great! Thanks so much!!

 

[MarcLodge]

Markronz,
Sorry bit late to this thread but if the name of the DB2 subsystem is different across the environments, you can issue the following code

SELECT CURRENT SERVER
FROM SYSIBM.SYSDUMMY1

which will give you the name of the subsystem with no need for coding UDFs.

Marc