Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2010
    Posts
    36

    Question Unanswered: 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 :
    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 are declared at the beginning of my functions.

    Code:
    		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.

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How are you trying to create the UDF?

    Andy

  3. #3
    Join Date
    Jun 2010
    Posts
    36
    With the IBM Control Center
    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

  4. #4
    Join Date
    Jun 2010
    Posts
    36
    Here is the full code :
    Code:
    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!
    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

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What did you set the statement termination character to?

    Andy

  6. #6
    Join Date
    Jun 2010
    Posts
    36
    The statement termination character is !
    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

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try doing this:

    Code:
    RETURN ( SELECT .... )
    Andy

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An idea to narrow source of error is to simplify or remove some code and see if the resulting error message(s) changed.

    For example, try:
    Code:
    .....
    
    RETURN
    SELECT *
     FROM  SD007.Sortie               S
     INNER JOIN
           SD007.SortieDescription    SD
       ON  S.intSortieId     = SD.intSortieId
     WHERE U.intUserId       = pvintUserId
       AND U.intCentreCoutId = pvintCentreCoutId
    ;
    
    END!
    Another idea is
    to execute the create statement from another client software like CLP,
    to confirm specification of "statement termination chacter" is working.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think your procedure body can be written by a "RETURN WITH ... SELECT ... " statement.

    For example:
    Code:
    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
    
    RETRUN
    WITH
     NombreAfficher AS (
    SELECT COUNT(CASE
                 WHEN pvintUserId            IN (0 , S.intUserId)
                  AND U.intCentreCoutId       = pvintCentreCoutId
                  AND TIMESTAMP(S.datSortie) >= CURRENT TIMESTAMP - 24 HOURS THEN
                      0
                 END) AS intNombreAfficher1Jour
         , COUNT(CASE
                 WHEN pvintUserId            IN (0 , S.intUserId)
                  AND U.intCentreCoutId       = pvintCentreCoutId
                  AND TIMESTAMP(S.datSortie) >= CURRENT TIMESTAMP - 7 DAYS   THEN
                      0
                 END) AS intNombreAfficher1Semaine
         , COUNT(CASE
                 WHEN pvintUserId            IN (0 , S.intUserId)
                  AND U.intCentreCoutId       = pvintCentreCoutId
                  AND TIMESTAMP(S.datSortie) >= CURRENT TIMESTAMP - 1 MONTH  THEN
                      0
                 END) AS intNombreAfficher1Mois
         , COUNT(CASE
                 WHEN S.intUserId             =  pvintUserId
                  AND TIMESTAMP(S.datSortie) <= CURRENT TIMESTAMP - 1 MONTH  THEN
                      0
                 END) AS intNombreAfficherApresMois
     FROM  SD007.Sortie  S
     INNER JOIN
           SD007.Users   U
       ON  S.intUserId = U.intUserId 
     WHERE pvintUserId IN (0 , S.intUserId)
    )
    , Sorties AS (
    SELECT CASE
           WHEN intNombreAfficher1Jour     >= 5 THEN
                intNombreAfficher1Jour
           WHEN intNombreAfficher1Semaine  >= 5 THEN
                5
           WHEN intNombreAfficher1Semaine   = 4 THEN
                4
           WHEN intNombreAfficher1Mois     >= 3 THEN
                3
           WHEN intNombreAfficher1Mois      = 2 THEN
                2
           WHEN intNombreAfficherApresMois >= 1 THEN
                1
           ELSE 0
           END  AS intSorties
     FROM  NombreAfficher
    )
    SELECT
           CAST( CHAR(CR.intCentreResponsabiliteId) AS VARCHAR(4) ) || ' - ' ||
           CASE 
           WHEN pvstrLangue ='fr' THEN
                CR.strNomFr
           WHEN pvstrLangue ='en' THEN
                CR.strNomEn
           END  AS strCentreResponsabilite
         , CAST( CHAR(US.intSecteur) AS VARCHAR(3) ) || ' - ' ||
           CASE
           WHEN pvstrLangue ='fr' THEN
                US.strNomFr
           WHEN pvstrLangue ='en' THEN
                US.strNomEn
           END  AS strSecteur
         , TRIM( CAST( CHAR(CC.intGroupe) AS VARCHAR(10) ) ) || ' - ' || 
           TRIM( CAST( CHAR(CC.intUnite)  AS VARCHAR(10) ) )
             AS strGroupeUnite
         , CASE
           WHEN pvintUserId <> 0 THEN
                TRIM(U.strUserLastName) || ', ' ||
                TRIM(U.strUserFirstName)
           ELSE
                'global.all.masc'
           END  AS strEmploye
         , U.intUserId
         , S.intSortieId
         , TRIM(U.strUserLastName) || ', ' ||
           TRIM(U.strUserFirstName)
             AS strUserName
         , VARCHAR_FORMAT(S .datSortie ,   'YYYY-MM-DD HH24:MI')    AS strDateHeure
         , VARCHAR_FORMAT(S .datCreation , 'YYYY-MM-DD HH24:MI:SS') AS datCreation
         , VARCHAR_FORMAT(SD.datCreation , 'YYYY-MM-DD HH24:MI:SS') AS datModification
         , SD.intSortieDescriptionId
         , SD.strDescription
         , CAST(SD.datCreation AS CHAR(10) ) AS strDescriptionDate
         , VARCHAR_FORMAT(SD.datCreation , 'HH24:MI:SS' ) AS strDescriptionHeure
         , SD.strCreationUser AS strDescriptionUser
         , 
         /*
           CASE
           WHEN S.strContactNom IS NOT NULL
            AND LENGTH( TRIM(S.strContactNom) ) > 0 THEN
                TRIM(S.strContactNom) || ', '
           ELSE ''
           END
         */
          COALESCE( NULLIF( TRIM(S.strContactNom) , '' ) || ', ' , '' )
          ||
         /*
           CASE
           WHEN S.strContactPrenom IS NOT NULL
            AND LENGTH( TRIM(S.strContactPrenom) ) > 0 THEN
                TRIM(S.strContactPrenom)
           ELSE ''
           END
         */
           COALESCE( TRIM(S.strContactPrenom) , '' )  AS strContact
         , 
         /*
           CASE
           WHEN S.strTelephoneCode IS NOT NULL
            AND LENGTH( TRIM(S.strTelephoneCode) ) > 0 THEN
                S.strTelephoneCode || '-'
           ELSE ''
           END
         */
           COALESCE( NULLIF(S.strTelephoneCode , '') || '-' , '' )
           ||
         /*
           CASE
           WHEN S.strTelephoneNumero IS NOT NULL
            AND LENGTH( TRIM(S.strTelephoneNumero) ) > 0 THEN
                SUBSTR(S.strTelephoneNumero , 1 , 3) || '-' || SUBSTR(S.strTelephoneNumero , 4 , 4)
           ELSE ''
           END
         */
           COALESCE( LEFT( INSERT( S.strTelephoneNumero || '    ' , 4 , 0 , '-' ) , 8) , '' )
             AS strTelephone
         , CASE
           WHEN S.strTelephonePoste IS NOT NULL
            AND LENGTH( TRIM(S.strTelephonePoste) ) > 0 THEN
                S.strTelephonePoste
           ELSE ''
           END  AS strExtension
     FROM  SD007.Sortie               S
     INNER JOIN
           SD007.SortieDescription    SD
       ON  S .intSortieId               = SD.intSortieId
     INNER JOIN
           SD007.Users                U
       ON  S .intUserId                 = U .intUserId
     INNER JOIN
           SD007.CentreCout           CC
       ON  U .intCentreCoutId           = CC.intCentreCoutId
     INNER JOIN
           SD007.UniteStructurelle    US
       ON  CC.intUniteStructurelleId    = US.intUniteStructurelleId
     INNER JOIN
           SD007.CentreResponsabilite CR
       ON  US.intCentreResponsabiliteId = CR.intCentreResponsabiliteId
     INNER JOIN
           (SELECT *
             FROM  (SELECT row_number() OVER (ORDER BY S.datSortie DESC) AS rowNum
                         , S.intSortieId
                     FROM  SD007.Sortie  S
                     INNER JOIN
                           SD007.Users   U
                       ON  S.intUserId = U.intUserId
                     WHERE /*
                           U.intUserId = CASE WHEN pvintUserId <> 0 THEN pvintUserId ELSE U.intUserId END
                           */
                           pvintUserId IN (0 , U.intUserId)
                       AND U.intCentreCoutId = pvintCentreCoutId
                    /*
                     ORDER BY
                           S.datSortie DESC
                    */
                   ) AS vwsTemp 
             WHERE rowNum <= CASE
                             WHEN pvintLimiter = 1 THEN
                                  (SELECT intSorties FROM Sorties)
                             ELSE 100
                             END
           ) AS vws
       ON  S .intSortieId = vws.intSortieId
     WHERE
     /*
           DATE(S.datCreation) >= pvstrPeriodeDebut
       AND DATE(S.datCreation) <= pvstrPeriodeFin
     */
           DATE(S.datCreation) BETWEEN pvstrPeriodeDebut
                                   AND pvstrPeriodeFin
     ORDER BY
           U .strUserLastName  ASC
         , U .strUserFirstName ASC
         , S .datSortie        DESC
         , SD.datCreation      DESC
    ;
    Last edited by tonkuma; 09-18-10 at 23:34.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is the expression(scalar subselect) for intNombreAfficherApresMois right?

    If "pvintUserId = 0", then it will be number of rows which satisfy conditions "SD007.Sortie.intUserId = 0", "SD007.Users.intCentreCoutId = pvintCentreCoutId" and a condition for "TIMESTAMP(SD007.Sortie.datSortie)".
    That is different from other counters(intNombreAfficher1Jour, intNombreAfficher1Semaine and intNombreAfficher1Mois).

    Code:
    		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
    		);
    Others are number of rows which satisfy conditions "SD007.Users.intCentreCoutId = pvintCentreCoutId" and a condition for "TIMESTAMP(SD007.Sortie.datSortie)",
    if "pvintUserId = 0".(Ignore a condition for SD007.Sortie.intUserId.)

    Code:
    	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
    		);
    The reason I asked this question is...
    if the expression(scalar subselect) for intNombreAfficherApresMois was same as other counters,
    then your query could be a litthle shorter.

Posting Permissions

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