SolutionsTI
asked on
SQL0104N An unexpected token "END-OF-STATEMENT" was found following...
Hi folks,
I am having problems creating a user defined function in DB2 9.5. I'm always getting the following error message :
Here is the code where it looks to be crashing :
** variables used in the code below have been declared at the beginning of my function.
Any thoughts?
Check my sig for all information regarding the platform used. I tried executing that code on my DEV server with IBM Control Center, and my Statement Termination Character is !
Regards
Charles M.
DB2 DEV server : DB2 Express-C / Windows 2008 Server Standard Edition
DB2 PROD server : DB2 9.5 Workgroup / Windows 2008 Server Standard Edition
I am having problems creating a user defined function in DB2 9.5. I'm always getting the following error message :
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "ND IF; RETURN SELECT". Expected tokens may include: "<delim_semicolon> ".
Here is the code where it looks to be crashing :
** variables used in the code below have been declared at the beginning of my function.
IF intNombreAfficher1Jour >= 5 THEN
SET intSorties = intNombreAfficher1Jour;
ELSEIF intNombreAfficher1Semaine >= 5 THEN
SET intSorties = 5;
ELSEIF intNombreAfficher1Semaine >= 4 THEN
SET intSorties = 4;
ELSEIF intNombreAfficher1Mois >= 3 THEN
SET intSorties = 3;
ELSEIF intNombreAfficher1Mois >= 2 THEN
SET intSorties = 2;
ELSEIF intNombreAfficherApresMois >= 1 THEN
SET intSorties = 1;
END IF;
RETURN
SELECT
(CAST(CHAR(SD007.CentreResponsabilite.intCentreResponsabiliteId) AS VARCHAR(4)) || ' - ' ||
CASE WHEN pvstrLangue ='fr' THEN
SD007.CentreResponsabilite.strNomFr
WHEN pvstrLangue ='en' THEN
SD007.CentreResponsabilite.strNomEn
END) AS strCentreResponsabilite,
[...]
[...]
[...]
Any thoughts?
Check my sig for all information regarding the platform used. I tried executing that code on my DEV server with IBM Control Center, and my Statement Termination Character is !
Regards
Charles M.
DB2 DEV server : DB2 Express-C / Windows 2008 Server Standard Edition
DB2 PROD server : DB2 9.5 Workgroup / Windows 2008 Server Standard Edition
ASKER
Here you go:
CREATE FUNCTION SD007.fncDatesSorties_Get_Rows(
pvstrLangue VARCHAR(2),
pvintCentreCoutId INTEGER,
pvintUserId INTEGER,
pvstrPeriodeDebut DATE,
pvstrPeriodeFin DATE,
pvintLimiter INTEGER
)
RETURNS TABLE ( strCentreResponsabilite VARCHAR(273),
strSecteur VARCHAR(262),
strGroupeUnite VARCHAR(23),
strEmploye VARCHAR(514),
intUserId INTEGER,
intSortieId INTEGER,
strUserName VARCHAR(514),
strDateHeure VARCHAR(20),
datCreation VARCHAR(20),
datModification VARCHAR(20),
intSortieDescriptionId INTEGER,
strDescription VARCHAR(2000),
strDescriptionDate VARCHAR(20),
strDescriptionHeure VARCHAR(10),
strDescriptionUser VARCHAR(20),
strContact VARCHAR(514),
strTelephone VARCHAR(12),
strExtension VARCHAR(10)
)
LANGUAGE SQL
READS SQL DATA
CALLED ON NULL INPUT
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE intNombreAfficher1Jour INTEGER;
DECLARE intNombreAfficher1Semaine INTEGER;
DECLARE intNombreAfficher1Mois INTEGER;
DECLARE intNombreAfficherApresMois INTEGER;
DECLARE intSorties INTEGER;
DECLARE dat1Jour TIMESTAMP;
DECLARE dat1Semaine TIMESTAMP;
DECLARE dat1Mois TIMESTAMP;
SET intSorties = 0;
SET dat1Jour = (SELECT CURRENT TIMESTAMP - 24 HOURS FROM SYSIBM.sysdummy1);
SET dat1Semaine = (SELECT CURRENT TIMESTAMP - 7 DAYS FROM SYSIBM.sysdummy1);
SET dat1Mois = (SELECT CURRENT TIMESTAMP - 1 MONTH FROM SYSIBM.sysdummy1);
SET intNombreAfficher1Jour = (
SELECT COUNT(*)
FROM SD007.Sortie
INNER JOIN SD007.Users ON
SD007.Sortie.intUserId = SD007.Users.intUserId
WHERE SD007.Sortie.intUserId = CASE WHEN pvintUserId <> 0 THEN pvintUserId ELSE SD007.Users.intUserId END AND
SD007.Users.intCentreCoutId = pvintCentreCoutId AND
TIMESTAMP(SD007.Sortie.datSortie) >= dat1Jour
);
SET intNombreAfficher1Semaine = (
SELECT COUNT(*)
FROM SD007.Sortie
INNER JOIN SD007.Users ON
SD007.Sortie.intUserId = SD007.Users.intUserId
WHERE SD007.Sortie.intUserId = CASE WHEN pvintUserId <> 0 THEN pvintUserId ELSE SD007.Users.intUserId END AND
SD007.Users.intCentreCoutId = pvintCentreCoutId AND
TIMESTAMP(SD007.Sortie.datSortie) >= dat1Semaine
);
SET intNombreAfficher1Mois = (
SELECT COUNT(*)
FROM SD007.Sortie
INNER JOIN SD007.Users ON
SD007.Sortie.intUserId = SD007.Users.intUserId
WHERE SD007.Sortie.intUserId = CASE WHEN pvintUserId <> 0 THEN pvintUserId ELSE SD007.Users.intUserId END AND
SD007.Users.intCentreCoutId = pvintCentreCoutId AND
TIMESTAMP(SD007.Sortie.datSortie) >= dat1Mois
);
SET intNombreAfficherApresMois = (
SELECT COUNT(*)
FROM SD007.Sortie
INNER JOIN SD007.Users ON
SD007.Sortie.intUserId = SD007.Users.intUserId
WHERE SD007.Sortie.intUserId = pvintUserId AND
TIMESTAMP(SD007.Sortie.datSortie) <= dat1Mois
);
IF intNombreAfficher1Jour >= 5 THEN
SET intSorties = intNombreAfficher1Jour;
ELSEIF intNombreAfficher1Semaine >= 5 THEN
SET intSorties = 5;
ELSEIF intNombreAfficher1Semaine >= 4 THEN
SET intSorties = 4;
ELSEIF intNombreAfficher1Mois >= 3 THEN
SET intSorties = 3;
ELSEIF intNombreAfficher1Mois >= 2 THEN
SET intSorties = 2;
ELSEIF intNombreAfficherApresMois >= 1 THEN
SET intSorties = 1;
END IF;
RETURN
SELECT * FROM SD007.Sortie
END!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think the RETURN statement must be terminated, and it's not.
ASKER
mustaccio : What you mean? You would add the termination character right after RETURN?
Thanks
Thanks
Hi Solutions,
I think that he means a semicolon after the RETURN SELECT ... statement and not after the word RETURN.
Kent
I think that he means a semicolon after the RETURN SELECT ... statement and not after the word RETURN.
Kent
ASKER
Do I need to put semicolon in subqueries?
ie:
INNER JOIN
(SELECT * FROM
(SELECT row_number() OVER (ORDER BY SD007.Sortie.datSortie DESC) AS rowNum, SD007.Sortie.intSortieId
FROM SD007.Sortie
INNER JOIN SD007.Users ON
SD007.Sortie.intUserId = SD007.Users.intUserId
WHERE SD007.Users.intUserId = CASE WHEN pvintUserId <> 0 THEN pvintUserId ELSE SD007.Users.intUserId END AND
SD007.Users.intCentreCoutI d = pvintCentreCoutId
ORDER BY SD007.Sortie.datSortie DESC
) AS vwsTemp
ie:
INNER JOIN
(SELECT * FROM
(SELECT row_number() OVER (ORDER BY SD007.Sortie.datSortie DESC) AS rowNum, SD007.Sortie.intSortieId
FROM SD007.Sortie
INNER JOIN SD007.Users ON
SD007.Sortie.intUserId = SD007.Users.intUserId
WHERE SD007.Users.intUserId = CASE WHEN pvintUserId <> 0 THEN pvintUserId ELSE SD007.Users.intUserId END AND
SD007.Users.intCentreCoutI
ORDER BY SD007.Sortie.datSortie DESC
) AS vwsTemp
Hi Solutions,
No. Only at the end of a statement. The next token (after the semicolon) must be a verb, command, or control word. (DECLARE, SELECT, IF, etc.)
Kent
No. Only at the end of a statement. The next token (after the semicolon) must be a verb, command, or control word. (DECLARE, SELECT, IF, etc.)
Kent
ASKER
Here is the full function.
At first I tried to put SELECT * FROM SD007.Sortie in order to see where the error was located. Do you see anything bad in that one? I don't...
At first I tried to put SELECT * FROM SD007.Sortie in order to see where the error was located. Do you see anything bad in that one? I don't...
CREATE FUNCTION SD007.fncDatesSorties_Get_Rows(
pvstrLangue VARCHAR(2),
pvintCentreCoutId INTEGER,
pvintUserId INTEGER,
pvstrPeriodeDebut DATE,
pvstrPeriodeFin DATE,
pvintLimiter INTEGER
)
RETURNS TABLE ( strCentreResponsabilite VARCHAR(273),
strSecteur VARCHAR(262),
strGroupeUnite VARCHAR(23),
strEmploye VARCHAR(514),
intUserId INTEGER,
intSortieId INTEGER,
strUserName VARCHAR(514),
strDateHeure VARCHAR(20),
datCreation VARCHAR(20),
datModification VARCHAR(20),
intSortieDescriptionId INTEGER,
strDescription VARCHAR(2000),
strDescriptionDate VARCHAR(20),
strDescriptionHeure VARCHAR(10),
strDescriptionUser VARCHAR(20),
strContact VARCHAR(514),
strTelephone VARCHAR(12),
strExtension VARCHAR(10)
)
LANGUAGE SQL
READS SQL DATA
CALLED ON NULL INPUT
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE intNombreAfficher1Jour INTEGER;
DECLARE intNombreAfficher1Semaine INTEGER;
DECLARE intNombreAfficher1Mois INTEGER;
DECLARE intNombreAfficherApresMois INTEGER;
DECLARE intSorties INTEGER;
DECLARE dat1Jour TIMESTAMP;
DECLARE dat1Semaine TIMESTAMP;
DECLARE dat1Mois TIMESTAMP;
SET intSorties = 0;
SET dat1Jour = (SELECT CURRENT TIMESTAMP - 24 HOURS FROM SYSIBM.sysdummy1);
SET dat1Semaine = (SELECT CURRENT TIMESTAMP - 7 DAYS FROM SYSIBM.sysdummy1);
SET dat1Mois = (SELECT CURRENT TIMESTAMP - 1 MONTH FROM SYSIBM.sysdummy1);
SET intNombreAfficher1Jour = (
SELECT COUNT(*)
FROM SD007.Sortie
INNER JOIN SD007.Users ON
SD007.Sortie.intUserId = SD007.Users.intUserId
WHERE SD007.Sortie.intUserId = CASE WHEN pvintUserId <> 0 THEN pvintUserId ELSE SD007.Users.intUserId END AND
SD007.Users.intCentreCoutId = pvintCentreCoutId AND
TIMESTAMP(SD007.Sortie.datSortie) >= dat1Jour
);
SET intNombreAfficher1Semaine = (
SELECT COUNT(*)
FROM SD007.Sortie
INNER JOIN SD007.Users ON
SD007.Sortie.intUserId = SD007.Users.intUserId
WHERE SD007.Sortie.intUserId = CASE WHEN pvintUserId <> 0 THEN pvintUserId ELSE SD007.Users.intUserId END AND
SD007.Users.intCentreCoutId = pvintCentreCoutId AND
TIMESTAMP(SD007.Sortie.datSortie) >= dat1Semaine
);
SET intNombreAfficher1Mois = (
SELECT COUNT(*)
FROM SD007.Sortie
INNER JOIN SD007.Users ON
SD007.Sortie.intUserId = SD007.Users.intUserId
WHERE SD007.Sortie.intUserId = CASE WHEN pvintUserId <> 0 THEN pvintUserId ELSE SD007.Users.intUserId END AND
SD007.Users.intCentreCoutId = pvintCentreCoutId AND
TIMESTAMP(SD007.Sortie.datSortie) >= dat1Mois
);
SET intNombreAfficherApresMois = (
SELECT COUNT(*)
FROM SD007.Sortie
INNER JOIN SD007.Users ON
SD007.Sortie.intUserId = SD007.Users.intUserId
WHERE SD007.Sortie.intUserId = pvintUserId AND
TIMESTAMP(SD007.Sortie.datSortie) <= dat1Mois
);
IF intNombreAfficher1Jour >= 5 THEN
SET intSorties = intNombreAfficher1Jour;
ELSEIF intNombreAfficher1Semaine >= 5 THEN
SET intSorties = 5;
ELSEIF intNombreAfficher1Semaine >= 4 THEN
SET intSorties = 4;
ELSEIF intNombreAfficher1Mois >= 3 THEN
SET intSorties = 3;
ELSEIF intNombreAfficher1Mois >= 2 THEN
SET intSorties = 2;
ELSEIF intNombreAfficherApresMois >= 1 THEN
SET intSorties = 1;
END IF;
RETURN
SELECT
(CAST(CHAR(SD007.CentreResponsabilite.intCentreResponsabiliteId) AS VARCHAR(4)) || ' - ' ||
CASE WHEN pvstrLangue ='fr' THEN
SD007.CentreResponsabilite.strNomFr
WHEN pvstrLangue ='en' THEN
SD007.CentreResponsabilite.strNomEn
END) AS strCentreResponsabilite,
(CAST(CHAR(SD007.UniteStructurelle.intSecteur) AS VARCHAR(3)) || ' - ' ||
CASE WHEN pvstrLangue ='fr' THEN
SD007.UniteStructurelle.strNomFr
WHEN pvstrLangue ='en' THEN
SD007.UniteStructurelle.strNomEn
END) AS strSecteur,
(TRIM(CAST(CHAR(SD007.CentreCout.intGroupe) AS VARCHAR(10))) || ' - ' ||
TRIM(CAST(CHAR(SD007.CentreCout.intUnite) AS VARCHAR(10)))
) AS strGroupeUnite,
(CASE WHEN pvintUserId <> 0 THEN
TRIM(SD007.Users.strUserLastName) || ', ' ||
TRIM(SD007.Users.strUserFirstName)
ELSE
'global.all.masc'
END
) AS strEmploye,
SD007.Users.intUserId,
SD007.Sortie.intSortieId,
(TRIM(SD007.Users.strUserLastName) || ', ' ||
TRIM(SD007.Users.strUserFirstName)
) AS strUserName,
(VARCHAR_FORMAT(SD007.Sortie.datSortie,'YYYY-MM-DD HH24:MI')
) AS strDateHeure,
(VARCHAR_FORMAT(SD007.Sortie.datCreation,'YYYY-MM-DD HH24:MI:SS')
) AS datCreation,
(VARCHAR_FORMAT(SD007.SortieDescription.datCreation,'YYYY-MM-DD HH24:MI:SS')
) AS datModification,
SD007.SortieDescription.intSortieDescriptionId,
SD007.SortieDescription.strDescription,
CAST(SD007.SortieDescription.datCreation AS CHAR(10)
) AS strDescriptionDate,
VARCHAR_FORMAT(SD007.SortieDescription.datCreation,'HH24:MI:SS'
) AS strDescriptionHeure,
SD007.SortieDescription.strCreationUser
AS strDescriptionUser,
((CASE WHEN SD007.Sortie.strContactNom IS NOT NULL AND LENGTH(TRIM(SD007.Sortie.strContactNom)) > 0 THEN
TRIM(SD007.Sortie.strContactNom) || ', '
ELSE
''
END) ||
(CASE WHEN SD007.Sortie.strContactPrenom IS NOT NULL AND LENGTH(TRIM(SD007.Sortie.strContactPrenom)) > 0 THEN
TRIM(SD007.Sortie.strContactPrenom)
ELSE
''
END
)) AS strContact,
((CASE WHEN SD007.Sortie.strTelephoneCode IS NOT NULL AND LENGTH(TRIM(SD007.Sortie.strTelephoneCode)) > 0 THEN
SD007.Sortie.strTelephoneCode || '-'
ELSE
''
END
) ||
(CASE WHEN SD007.Sortie.strTelephoneNumero IS NOT NULL AND LENGTH(TRIM(SD007.Sortie.strTelephoneNumero)) > 0 THEN
SUBSTR(SD007.Sortie.strTelephoneNumero,1,3) || '-' || SUBSTR(SD007.Sortie.strTelephoneNumero,4,4)
ELSE
''
END
))
AS strTelephone,
(CASE WHEN SD007.Sortie.strTelephonePoste IS NOT NULL AND LENGTH(TRIM(SD007.Sortie.strTelephonePoste)) > 0 THEN
SD007.Sortie.strTelephonePoste
ELSE
''
END
) AS strExtension
FROM SD007.Sortie
INNER JOIN SD007.SortieDescription ON
SD007.Sortie.intSortieId = SD007.SortieDescription.intSortieId
INNER JOIN SD007.Users ON
SD007.Sortie.intUserId = SD007.Users.intUserId
INNER JOIN SD007.CentreCout ON
SD007.Users.intCentreCoutId = SD007.CentreCout.intCentreCoutId
INNER JOIN SD007.UniteStructurelle ON
SD007.CentreCout.intUniteStructurelleId = SD007.UniteStructurelle.intUniteStructurelleId
INNER JOIN SD007.CentreResponsabilite ON
SD007.UniteStructurelle.intCentreResponsabiliteId = SD007.CentreResponsabilite.intCentreResponsabiliteId
INNER JOIN
(SELECT * FROM
(SELECT row_number() OVER (ORDER BY SD007.Sortie.datSortie DESC) AS rowNum, SD007.Sortie.intSortieId
FROM SD007.Sortie
INNER JOIN SD007.Users ON
SD007.Sortie.intUserId = SD007.Users.intUserId
WHERE SD007.Users.intUserId = CASE WHEN pvintUserId <> 0 THEN pvintUserId ELSE SD007.Users.intUserId END AND
SD007.Users.intCentreCoutId = pvintCentreCoutId
ORDER BY SD007.Sortie.datSortie DESC
) AS vwsTemp
WHERE rowNum <= CASE WHEN pvintLimiter = 1 THEN intSorties ELSE 100 END
) AS vws ON
SD007.Sortie.intSortieId = vws.intSortieId
WHERE DATE(SD007.Sortie.datCreation) >= pvstrPeriodeDebut AND DATE(SD007.Sortie.datCreation) <= pvstrPeriodeFin
ORDER BY TRIM(SD007.Users.strUserLastName) ASC,
TRIM(SD007.Users.strUserFirstName) ASC,
SD007.Sortie.datSortie DESC,
SD007.SortieDescription.datCreation DESC;
END!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Comment? I didn't see a comment in the code....
Kent
Can you post the entire procedure? The parser is apparently lost and has given up somewhere close to the code you show, but the item that sent it astray is elsewhere.
It's probably a begin/end mismatch or terminator character issue.
Kent