3939 there is a mismatch between the number of parameters specified and the number of parameters required

Database
Enthusiast

3939 there is a mismatch between the number of parameters specified and the number of parameters required

I need last 2 years year and month part as an integer with descending rank

in DBC.SYSEXECSQL

for example :

YEAR_MONTH_ID RELATIVE_MONTH

201304   1

201303   2

201302   3

201301   4

201212   5

201211   6

201210   7

201209   8

201208   9 

201207   10

201206   11

201205   12

201204   13

201203   14

201202   15

201201   16

201112   17

201111   18

201110   19

201109   20

201108   21

201107   22

201106   23

201105   24

201104   25

I am using the following Stored procedure for the same

REPLACE PROCEDURE DB1.TEMP_SP(#IN_DATE DATE)

BEGIN

DECLARE TOD DATE;

DECLARE FOD DATE;

SET FOD = CAST((#IN_DATE-(365*2)-1) AS DATE);

SET TOD = CAST(#IN_DATE AS DATE);

CALL DBC.SYSEXECSQL('REPLACE VIEW DB1.TEMP_VIEW

AS

SELECT

CAST(CAST(CAST( CALENDAR_DATE AS FORMAT ''YYYYMM'') AS CHAR(6)) AS INTEGER) AS YEAR_MONTH_ID,

RANK() OVER( ORDER BY YEAR_MONTH_ID DESC ) AS "PERIOD_RANK"

FROM

SYS_CALENDAR.CALENDAR

WHERE CALENDAR_DATE BETWEEN :FOD AND :TOD

GROUP BY YEAR_MONTH_ID'

);

END;

Here i get an error :

3939 there is a mismatch between the number of parameters specified and the number of parameters required

When i replace the where clause in DBC.SYSEXECSQL 

WHERE CALENDAR_DATE BETWEEN ''2011-04-01'' AND ''2013-04-01''

 it works fine

Why is it so ?

2 REPLIES
Enthusiast

Re: 3939 there is a mismatch between the number of parameters specified and the number of parameters required

Here :FOD and :TOD are considered as strings.

DBC.SYSEXECSQL doesnot replace :FOD and :TOD with their date values while running the Replace view query.

Now its working fine

REPLACE PROCEDURE DB1.TEMP_SP(#IN_DATE DATE)
                        BEGIN
                        DECLARE TOD CHAR(10);
                        DECLARE FOD CHAR(10);
                       
                         SET FOD = CAST(CAST((#IN_DATE-(365*2)-1) AS FORMAT 'YYYY-MM-DD')AS CHAR(10));
                        SET TOD = CAST(CAST(#IN_DATE AS FORMAT 'YYYY-MM-DD') AS CHAR(10));
                       
                         CALL DBC.SYSEXECSQL('REPLACE VIEW DB1.TEMP_VIEW
                        AS
                        SELECT
                                                            CAST(CAST(CAST( CALENDAR_DATE AS FORMAT ''YYYYMM'') AS CHAR(6)) AS INTEGER) AS YEAR_MONTH_ID,
                                                            RANK() OVER( ORDER BY YEAR_MONTH_ID DESC ) AS "PERIOD_RANK"
                                                            FROM
                                                            SYS_CALENDAR.CALENDAR
                                                            WHERE CALENDAR_DATE BETWEEN '''||:FOD||''' AND '''||:TOD||'''
                                               
                                                GROUP BY YEAR_MONTH_ID');
                                    END;
Enthusiast

Re: 3939 there is a mismatch between the number of parameters specified and the number of parameters required

hi,

I just tried it in the normal SQL, Hope this helps to you!

sel a, rank(a) from

(sel (add_months(dob,-24) (format 'yyyymm'))(varchar(6)) a from tb_name

where

dob between add_months(current_date,-24)+1 and current_date group by a)dt

Regards,

Mohan