We help IT Professionals succeed at work.

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

SolutionsTI
SolutionsTI asked
on
5,742 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
Comment
Watch Question

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
I think the RETURN statement must be terminated, and it's not.

Author

Commented:
mustaccio : What you mean? You would add the termination character right after RETURN?

Thanks
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Solutions,

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


Kent

Author

Commented:
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
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

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


Kent

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.