Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18

    Unanswered: New Scalar Function

    Hello Everybody. I am working with DB2 Version 7 for OS/390.

    I need an scalar function with the next conditions:
    - Parameter: Char (10)
    Contains a date in format 'YYYY-MM-DD'
    - Result: Decimal (7)
    Value is PPPPNNN where
    PPPP = (YYYY + 1) if MM > 06, else PPPP = YYYY
    NNN = Days transcurred from the last Jun-30 before parm dt

    To illustrate the requirement, 2 examples:
    Parm date: '2003-12-01' , Result: 2004154 (154 days from 2003-06-30)
    Parm date: '2004-05-30' , Result: 2004335 (335 days from 2003-06-30)

    I coded the function, but I think my version is a little complex.
    Your help will be appreciated.

    Thanks, Aloz.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: New Scalar Function

    If you post what you have coded, people may be able to suggest improvements ...

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18
    Next, the statement used in my function:

    SELECT CAST (
    (CASE WHEN SUBSTR(FEINP,6,2) < '07'
    THEN YEAR(FEINP)
    ELSE YEAR(FEINP) + 1 END) * 1000 +
    (DAYS(FEINP) -
    DAYS(DATE (CAST((CASE WHEN SUBSTR(FEINP,6,2) < '07'
    THEN YEAR(FEINP) - 1
    ELSE YEAR(FEINP) END) AS CHAR(4)) || '-06-30')))
    AS DECIMAL(7))
    FROM SYSIBM.SYSDUMMY1

    where FEINP is the input date (Parameter).

    Regards, Aloz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •