Solved

SQL0104N An unexpected token "END-OF-STATEMENT" was found following...

Posted on 2010-09-17
12
3,358 Views
Last Modified: 2012-05-10
Hi folks,

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,
[...]
[...]
[...]

Open in new window


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
0
Comment
Question by:SolutionsTI
  • 6
  • 5
12 Comments
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Hi Solutions,

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
0
 

Author Comment

by:SolutionsTI
Comment Utility
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!

Open in new window

0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 500 total points
Comment Utility
Hi Solutions,

That is odd.  It certainly looks correct.

If you're not streaming the creation of the procedure through a batch process, you can drop the final terminator (!).

Also, the *IF* block doesn't contain a default condition.  You could wind up with an odd value for intSorties.


But I don't see anything that would cause the parse error that you're seeing.



Kent
0
 
LVL 8

Expert Comment

by:mustaccio
Comment Utility
I think the RETURN statement must be terminated, and it's not.
0
 

Author Comment

by:SolutionsTI
Comment Utility
mustaccio : What you mean? You would add the termination character right after RETURN?

Thanks
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Hi Solutions,

I think that he means a semicolon after the RETURN SELECT ... statement and not after the word RETURN.


Kent
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:SolutionsTI
Comment Utility
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.intCentreCoutId = pvintCentreCoutId

ORDER BY SD007.Sortie.datSortie DESC

) AS vwsTemp
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
0
 

Author Comment

by:SolutionsTI
Comment Utility
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...
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!

Open in new window

0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 500 total points
Comment Utility
Both of your queries look disgustingly correct, though I'd remove the exclamation point at the end.

There are some very minor differences in SQL between the different versions of DB2.  What is your host operating system and version?



Kent
0
 

Accepted Solution

by:
SolutionsTI earned 0 total points
Comment Utility
The problem was related to a comment in the code. Problem is now fixed. Thanks!
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

Comment?  I didn't see a comment in the code....


Kent
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now