DB2 Store Procedure with Parameters in Create View

ALezama · #1 (**permalink**) 10-09-11, 22:11

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?

ARWinner · #2 (**permalink**) 10-10-11, 09:42

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

tonkuma · #3 (**permalink**) 10-10-11, 11:40

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')
;

kmbkrishnan · #4 (**permalink**) 10-11-11, 07:55

yes.. as tonkuma... you can create a static view and made change appropriatly...

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

ALezama · #5 (**permalink**) 10-11-11, 12:59

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!!

ALezama · #6 (**permalink**) 10-11-11, 13:26

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech