Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2007
    Location
    Bangalore
    Posts
    1

    Unanswered: Doubt in db2 functions can any one help me

    Hi,
    I have written a query in sql.but i need this to be in db2. I dont know the syntax of db2. Can any one help me to solve this....

    CREATE FUNCTION getStartDate(@RequesteeName varchar(100))
    RETURNS VARCHAR(100)
    BEGIN

    DECLARE @ppm varchar(100)
    DECLARE @StartDate DateTime

    -- Getting the Primary Profile Indicator of the requesteename by matching with Fullname in amdperson table
    SELECT @ppm = PrimaryProfileIndicator from amdPerson as Person where Person.FullName = @Requesteename

    IF @ppm IS NOT NULL
    BEGIN
    IF @ppm='PS'
    BEGIN

    -- Getting the StartDate if PPM is PSFT
    SELECT @StartDate = HRStartDate from amdPerson as Person where Person.FullName = @Requesteename

    -- Converting date into Varchar
    RETURN CONVERT(VARCHAR,@StartDate,101)
    END
    ELSE IF @ppm='DM'
    BEGIN
    SELECT @StartDate = DMPositionStartDate amdPerson as Person where Person.FullName = @Requesteename
    RETURN CONVERT(VARCHAR,@StartDate,101)
    END
    ELSE
    RETURN 'ERROR --- PRIMARY PROFILE IDENTIFIER IS NEITHER PSFT OR DMU '

    END
    ELSE
    RETURN ' ERROR --- PRIMARY PROFILE IDENTIFIER NOT FOUND '

    END

    Regards,
    Hari

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    • The IF statement is SQL requires a THEN keyword.
    • Errors are raised using the SIGNAL statement.
    • Type conversions are to be done with CAST and not CONVERT.

    This are the major issues I see in your code. Try to adjust that. (If MS SQL Server follows the SQL standard, then the modified code should work there as well. So it may be a good idea to change your source to portable SQL code as well.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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