INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

DB2 for Z/OS - SQL - Create Function Help

DB2 for Z/OS - SQL - Create Function Help

(OP)
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.


CODE

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!

RE: DB2 for Z/OS - SQL - Create Function Help

(OP)
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...

CODE

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!!!

RE: DB2 for Z/OS - SQL - Create Function Help

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

CODE

CREATE FUNCTION UDF_StatusLookup(status varchar(8))
  RETURNS VARCHAR(25)
  LANGUAGE SQL
  DETERMINISTIC
  CONTAINS SQL
  RETURNS NULL ON NULL INPUT
  NO EXTERNAL ACTION  
BEGIN
  RETURN (
    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);
END
;
The above statement creates the function in current schema, (which I set before with SET SCHEMA mySchema)

This short test

CODE

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

CODE

....+....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.
 

RE: DB2 for Z/OS - SQL - Create Function Help

(OP)
Great! Thanks so much!!

RE: DB2 for Z/OS - SQL - Create Function Help

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

CODE

SELECT CURRENT SERVER
FROM SYSIBM.SYSDUMMY1
which will give you the name of the subsystem with no need for coding UDFs.

Marc

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close