Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: DB2 Store Procedure with Parameters in Create View

    I'm trying to create a Stored Procedure that receives two parameters (both Date type). The SP must create a View from a Select containing two JOIN and three WHERE conditions involving the two parameters.

    I am working with DB2 9.7 FixPack 2 on Windows 7 32-bit and IBM Data Studio 2.2.

    The problem I have is that by making the deployment of the SP returns the following error:

    COBRANZA.SP_CREATEVIEWMOVIMIENTOSCLIENTES: 17: "FECHAINIMONITOREO" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.61.65


    The code of my SP is as follows:


    Code:
    CREATE PROCEDURE SP_CreateViewMovimientosClientes  (IN FechaIniMonitoreo DATE ,
    					            IN FechaFinMonitoreo DATE )
     
    SPECIFIC SP_CreateViewMovimientosClientes
     
    P1: BEGIN
    	If Exists (Select 1 From SYSIBM.SYSVIEWS Where Name = 'VIEWMOVIMIENTOSCLIENTES' and Creator = 'COBRANZA')  Then
    	   DROP VIEW COBRANZA.VIEWMOVIMIENTOSCLIENTES;
    	End If;
     
    		
    	CREATE view COBRANZA.ViewMOVIMIENTOSCLIENTES (ClienteCod, PolizaCod, MonedaCod, RamoCod, TipoEndosoCod, EmisionEndosoNro, 
    						      EmisionFecha, TipoMovimientoCod, MovPolizaNro) AS
     
    			SELECT CLIENTES.ClienteCod, POLIZAS.PolizaCod, POLIZAS.MonedaCod, POLIZAS.RamoCod, MOVIMIENTOS.TipoEndosoCod, MOVIMIENTOS.EmisionEndosoNro, 
    					MOVIMIENTOS.EmisionFecha, MOVIMIENTOS.TipoMovimientoCod, MOVIMIENTOS.MovPolizaNro	
    			FROM COBRANZA.CLIENTES AS CLIENTES JOIN COBRANZA.POLIZAS AS POLIZAS ON CLIENTES.ClienteCod = POLIZAS.ClienteCod 
    			     			           JOIN COBRANZA.MOVIMIENTOSPOLIZAS AS MOVIMIENTOS ON MOVIMIENTOS.PolizaCod = POLIZAS.PolizaCod and 
                                                                                                                  MOVIMIENTOS.MonedaCod = POLIZAS.MonedaCod and 
    			 										      MOVIMIENTOS.RamoCod = POLIZAS.RamoCod
    			WHERE MOVIMIENTOS.TipoMovimientoCod = 'PAG' and 
    			      MOVIMIENTOS.MovPolizaFecha >= FechaIniMonitoreo and 
    			      MOVIMIENTOS.MovPolizaFecha <= FechaFinMonitoreo ;
    	
    END P1
    I ran some test, changing the SQL parameters for date constants ('2011-09-01 'and '2011-09-07'). In this case, the SP worked perfectly. However, I need the SQL sentence to be dynamic, using the SP parameters.

    I read about a setting "DYNAMICRULES" for dynamic SQL but I am not clear as to how to implement it.

    Anybody can help me? Whats is wrong in this SP code?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You will have to create the view using dynamic SQL, not static. You will need to build the create statement as a string and then execute that string.

    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't find the reason to create the view everytime called the procedure,
    except the Noted below.

    The following way would be easier...

    (1) create a little modified view once which is...
    a) added a column MovPolizaFecha in column-list of the view(and select-list of body of the view).
    b) removed conditions for MOVIMIENTOS.MovPolizaFecha from where clause.

    (2) When using the view, add the conditions for MovPolizaFecha in the view, like...
    Code:
    SELECT ...
    ...
      FROM COBRANZA.ViewMOVIMIENTOSCLIENTES VMC
    ...
     WHERE
    ...
       AND VMC.MovPolizaFecha
           BETWEEN FechaIniMonitoreo
               AND FechaFinMonitoreo
    ;
    or

    Code:
    SELECT ...
    ...
      FROM COBRANZA.ViewMOVIMIENTOSCLIENTES VMC
    ...
     WHERE
    ...
       AND VMC.MovPolizaFecha
           BETWEEN '2011-09-01'
               AND '2011-09-07'
    ;

    Note:
    If you want to make the range of MovPolizaFecha stable(i.e. guarateed the same) for a while,
    an idea is making another table to store the range of MovPolizaFecha, like...
    Code:
    CREATE TABLE COBRANZA.MOVIMIENTOSCLIENTES_parm
    ( FechaIniMonitoreo DATE
    , FechaFinMonitoreo DATE
    );
    
    INSERT INTO COBRANZA.MOVIMIENTOSCLIENTES_parm
    VALUES ('0001-00-01' , '0001-01-01');
    and join the table in the view, like...
    Code:
    CREATE view COBRANZA.ViewMOVIMIENTOSCLIENTES
    (...) AS
    SELECT
    ...
     FROM
    ...
     JOIN  COBRANZA.MOVIMIENTOSCLIENTES_parm AS parm
       ON  MOVIMIENTOS.MovPolizaFecha
           BETWEEN parm.FechaIniMonitoreo
               AND parm.FechaFinMonitoreo
     WHERE
    ...
    After all, update the values in the parm table before using the view, like...
    Code:
    UPDATE COBRANZA.MOVIMIENTOSCLIENTES_parm
       SET (FechaIniMonitoreo , FechaFinMonitoreo)
         = ('2011-09-01'      , '2011-09-07')
    ;
    Last edited by tonkuma; 10-10-11 at 13:00.

  4. #4
    Join Date
    Oct 2011
    Posts
    2
    yes.. as tonkuma... you can create a static view and made change appropriatly...

    better place your requirement here to get solution....

  5. #5
    Join Date
    Oct 2011
    Posts
    3
    Thanks ARWinner, Tonkuma and kmbkrishnan for yours ideas.

    Tha solution for dynamic SQL allow deployment but in runtime give me an error SQL0104N An unexpected token "" was found following "".

    With the ideas of Tonkuma worked as static SQL.


    Thanks again!!

  6. #6
    Join Date
    Oct 2011
    Posts
    3
    Guys,

    In the IBM Data Studio Forum Ruiming replied with the following:

    Hi,

    Currently the CREATE VIEW statement does not support parameters. You may refer to the CREATE VIEW statement section in DB2 LUW 9.7 manuals for more information. Here are some excerpt from the DB2 manual:

    "Defines the view. At any time, the view consists of the rows that would result if the SELECT statement were executed. The fullselect must not reference host variables, parameter markers, or declared temporary tables. However, a parameterized view can be created as an SQL table function."

    Ruiming


    This explains why it failed with different options that I tried.

Tags for this Thread

Posting Permissions

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