-1

I am attempting to create a user defined function for DB2 and cannot figure it out. I am much more comfortable with t-sql, which would do this in about a dozen ways. I need help as all I get in return are cryptic error messages that tell me there are no valid tokens or some such.

The basic premise is that I am trying to convert a normal date into a date format used throughout the database (cyymmdd) Here goes the pseudo-code:

Get current time;
If YEAR(current time) > 1999 then 1 else 0;

Simple enough, right? Can't get it to work. Here is the actual code:

DECLARE Cn CHAR ( 1 ) ;
CASE
WHEN YEAR(X) > 1999
THEN SET Cn = 1;
ELSE SET Cn = 0;

Also tried:

DECLARE Cn CHAR ( 1 ) ;
CASE YEAR(X)
WHEN  > 1999
THEN SET Cn = 1;
ELSE SET Cn = 0;

And that fails miserably as it should.

I am really at a loss as to how I can convert 3/25/2014 into 1140325 with just SQL. I guess I can handle this in application logic, but that isn't the way I want to go with this.

Thanks in Advance.

KingOfAllTrades
  • 398
  • 1
  • 11
  • 1
    You may want to show the entire source of your function, because the fragments you've included don't seem to have valid syntax. Knowledge of T-SQL is no substitute for reading the [actual manuals](http://pic.dhe.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzcasestmt.htm). Also, why are you assigning integer values to a `CHAR(1)` variable? – mustaccio Mar 25 '14 at 22:22
  • @mustaccio, yes the syntax is incorrect in some manner. I tried many variations based on samples from the ibm website, but kept getting errors. If I can simply get a case statement to work, I can handle the logic to finish out the function. However, it looks as though James showed us a better way to convert the dates. – KingOfAllTrades Mar 26 '14 at 13:34

2 Answers2

6

It's a simple calculation to convert a DATE to CYMD:

SELECT (YEAR(CURRENT_DATE) - 1900) * 10000 
  + MONTH(CURRENT_DATE) * 100 
  + DAY(CURRENT_DATE)
FROM SYSIBM.SYSDUMMY1;

A UDF to perform the conversion:

CREATE FUNCTION QGPL.DATE2CYMD(DATE DATE) RETURNS DEC(7)
LANGUAGE SQL
DETERMINISTIC
BEGIN
    RETURN (YEAR(DATE) - 1900) * 10000
    + MONTH(DATE) * 100
    + DAY(DATE);
END

A test to verify it's working correctly:

SELECT 
    QGPL.DATE2CYMD(CURRENT_DATE) CURRENT_CYMD, 
    QGPL.DATE2CYMD(CAST(NULL AS DATE)) NULL_CYMD
FROM SYSIBM.SYSDUMMY1;

result

James Allman
  • 40,573
  • 11
  • 57
  • 70
2

In most cases, you can think of CASE as if it was a function. A CASE structure selects an expression; it doesn't execute statements. In effect, it returns a value as a function or any expression would.

Here are some coding variations that all result in MYCENT getting either '1' or '0' set:

DECLARE MYCENT CHAR ( 1 ) ;
DECLARE MYYEAR BIGINT ;

 SET MYYEAR = YEAR ( CURRENT DATE ) ;
 SET MYCENT = CASE WHEN MYYEAR > 1999 THEN '1' ELSE '0' END ;
or
 IF ( MYYEAR > 1999 )
    THEN SET MYCENT = '1' ;
    ELSE SET MYCENT = '0' ;
 END IF ;

 SET MYCENT = CASE WHEN YEAR(CURRENT DATE) > 1999 THEN '1' ELSE '0' END ;

Note that the two CASE examples both use a single WHEN...THEN... clause, but you can have as many as needed. Also note that using IF has a SET statement in each branch, but CASE simply chooses a value to return. (The value is a form of expression.)

As a syntactic element of SQL, CASE is known as a case-expression. As an expression, you can generally use CASE wherever a variable might be valid.

user2338816
  • 2,163
  • 11
  • 11