07-16-07, 10:28 #1Registered User
- Join Date
- Jul 2007
Unanswered: Doubt in db2 functions can any one help me
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))
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
-- Getting the StartDate if PPM is PSFT
SELECT @StartDate = HRStartDate from amdPerson as Person where Person.FullName = @Requesteename
-- Converting date into Varchar
ELSE IF @ppm='DM'
SELECT @StartDate = DMPositionStartDate amdPerson as Person where Person.FullName = @Requesteename
RETURN 'ERROR --- PRIMARY PROFILE IDENTIFIER IS NEITHER PSFT OR DMU '
RETURN ' ERROR --- PRIMARY PROFILE IDENTIFIER NOT FOUND '
07-16-07, 11:06 #2Registered User
- Join Date
- Jan 2007
- Jena, Germany
- 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