Re: Stored Procedure with different SUMs

[Charles Wilt <charles.wilt@xxxxxxxxx>]

What's the data type for AMFLIB . MTHACTE . TERRN

Also, show the parameter declaration section.

Lastly, why are you using dynamic SQL?

Static SQL would perform better and may give you a better error
message during compile...

declare C1 cursor for
   SELECT
       AMFLIB . CUSMAS . COMNO AS "Company Number" , AMFLIB . CUSMAS .
CUSCL AS "Customer Class" ,
       AMFLIB . CUSMAS . CUSNO AS "Customer Number" , AMFLIB . CUSMAS .
CUSNM AS "Customer Name" ,
       AMFLIB . CUSMAS . SLSNO AS "Salesman Number" , AMFLIB . SLSMAS .
SLSNM AS "Salesman Name" ,
       AMFLIB . MTHACTE . ITCLS AS "Prod. Class" , AMFLIB . MBB0REP .
B0GTTX AS "Product Class Description" ,
               SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_CURRENTMONTHDATESTART AND P_CURRENTMONTHDATEEND THEN
AMFLIB.MTHACTE.JQGLVA ELSE 0) AS "Current Month Booking Value",
               SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_CURRENTMONTHDATESTART AND P_CURRENTMONTHDATEEND THEN
AMFLIB.MTHACTE.COQTY ELSE 0) AS "Current Month Quantity",
               SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_CURRENTFISCALYTDDATESTART AND P_CURRENTFISCALYTDDATEEND THEN
AMFLIB.MTHACTE.JQGLVA ELSE 0) AS "Current Fiscal YTD Booking Value",
               SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_CURRENTFISCALYTDDATESTART AND P_CURRENTFISCALYTDDATEEND THEN
AMFLIB.MTHACTE.COQTY ELSE 0) AS "Current Fiscal YTD Quantity",
               SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_LASTFISCALYTDDATESTART AND P_LASTFISCALYTDDATEEND THEN
AMFLIB.MTHACTE.JQGLVA ELSE 0) AS "Last Fiscal YTD Booking Value",
               SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_LASTFISCALYTDDATESTART AND P_LASTFISCALYTDDATEEND THEN
AMFLIB.MTHACTE.COQTY ELSE 0) AS "Last Fiscal YTD Quantity"
       FROM AMFLIB . CUSMAS
               JOIN AMFLIB . MTHACTE
                       ON AMFLIB . CUSMAS . CUSNO = AMFLIB . MTHACTE .
CUSNO
               JOIN AMFLIB . SLSMAS
                       ON AMFLIB . CUSMAS . SLSNO = AMFLIB . SLSMAS .
SLSNO
               JOIN AMFLIB . MBB0REP
                       ON AMFLIB . MTHACTE . ITCLS = AMFLIB . MBB0REP .
B0GLCD
       WHERE AMFLIB . MTHACTE . TERRN = SELECTION_TERRITORY
                            GROUP BY AMFLIB . CUSMAS . COMNO ,
                       AMFLIB . CUSMAS . TERRN ,
                       AMFLIB . CUSMAS . SLSNO ,
                       AMFLIB . CUSMAS . CUSCL ,
                       AMFLIB . CUSMAS . CUSNO ,
                       AMFLIB . MTHACTE . ITCLS ,
                       AMFLIB . SLSMAS . SLSNM , AMFLIB . CUSMAS .
CUSNM , AMFLIB . MBB0REP . B0GTTX ;

open C1;

HTH,
Charles


On Mon, Jul 6, 2009 at 4:00 PM, ibm<ibm@xxxxxxxxxx> wrote:
> I've been struggling with this all day.  All values look OK in the
> debugger, but it keeps failing:
>
> SQL State: 22001
> Vendor Code: -303
> Message: [SQL0303] Host variable *N not compatible
>
> Also, another thing I noticed is if I replace the SELECTION_TERRITORY IN
> Parm with a hardcoded integer (e.g. 10), *plus* remove the SUM()s, the
> procedure runs.
>
> BEGIN
>
> DECLARE P_CURRENTYEARENDYEAR INTEGER ;
> DECLARE P_YEARENDMONTH INTEGER ;
> DECLARE P_YEARENDDAY INTEGER ;
> DECLARE P_CURRENTMONTHDATE DATE ;
> DECLARE P_CURRENTYEARENDDATE DATE ;
> DECLARE P_CURRENTMONTHDATESTART DECIMAL ( 7 , 0 ) ;
> DECLARE P_CURRENTMONTHDATEEND DECIMAL ( 7 , 0 ) ;
> DECLARE P_CURRENTFISCALYTDDATESTART DECIMAL ( 7 , 0 ) ;
> DECLARE P_CURRENTFISCALYTDDATEEND DECIMAL ( 7 , 0 ) ;
> DECLARE P_LASTFISCALYTDDATESTART DECIMAL ( 7 , 0 ) ;
> DECLARE P_LASTFISCALYTDDATEEND DECIMAL ( 7 , 0 ) ;
>
> DECLARE SQLSTMT CHAR ( 1024 ) ;
> DECLARE C2 CURSOR FOR S2 ;
>
> SET P_CURRENTMONTHDATE = DATE ( RTRIM ( CHAR ( SELECTION_YEAR ) ) CONCAT
> '-' CONCAT RTRIM ( CHAR ( SELECTION_MONTH ) ) CONCAT '-01' ) ;
>
> SET P_YEARENDMONTH = 09 ;
> SET P_YEARENDDAY = 30 ;
> IF MONTH ( P_CURRENTMONTHDATE ) < P_YEARENDMONTH THEN
> SET P_CURRENTYEARENDYEAR = SELECTION_YEAR - 1 ;
> ELSE
> SET P_CURRENTYEARENDYEAR = SELECTION_YEAR ;
> END IF ;
> SET P_CURRENTYEARENDDATE = DATE ( RTRIM ( CHAR ( P_CURRENTYEARENDYEAR )
> ) CONCAT '-' CONCAT RTRIM ( CHAR ( P_YEARENDMONTH ) ) CONCAT '-' CONCAT
> RTRIM ( CHAR ( P_YEARENDDAY ) ) ) ;
> SET P_CURRENTMONTHDATESTART = ( YEAR ( P_CURRENTMONTHDATE ) - 1900 ) *
> 10000 + ( MONTH ( P_CURRENTMONTHDATE ) * 100 ) + DAY (
> P_CURRENTMONTHDATE ) ;
> SET P_CURRENTMONTHDATEEND = ( YEAR ( P_CURRENTMONTHDATE ) - 1900 ) *
> 10000 + ( MONTH ( P_CURRENTMONTHDATE ) * 100 ) + ( DAY (
> P_CURRENTMONTHDATE + 1 MONTH - 1 DAY ) - DAY ( P_CURRENTMONTHDATE ) ) ;
> SET P_CURRENTFISCALYTDDATESTART = ( YEAR ( P_CURRENTYEARENDDATE ) - 1900
> ) * 10000 + ( P_YEARENDMONTH * 100 ) + P_YEARENDDAY ;
> SET P_CURRENTFISCALYTDDATEEND = P_CURRENTMONTHDATEEND ;
> SET P_LASTFISCALYTDDATESTART = ( YEAR ( P_CURRENTYEARENDDATE - 1 YEAR )
> - 1900 ) * 10000 + ( P_YEARENDMONTH * 100 ) + P_YEARENDDAY ;
> SET P_LASTFISCALYTDDATEEND = ( YEAR ( P_CURRENTMONTHDATE - 1 YEAR ) -
> 1900 ) * 10000 + ( MONTH ( P_CURRENTMONTHDATE ) * 100 ) + ( DAY (
> P_CURRENTMONTHDATE - 1 YEAR + 1 MONTH - 1 DAY ) - DAY (
> P_CURRENTMONTHDATE - 1 YEAR ) ) ;
>
>
> SET SQLSTMT = 'SELECT
>        AMFLIB . CUSMAS . COMNO AS "Company Number" , AMFLIB . CUSMAS .
> CUSCL AS "Customer Class" ,
>        AMFLIB . CUSMAS . CUSNO AS "Customer Number" , AMFLIB . CUSMAS .
> CUSNM AS "Customer Name" ,
>        AMFLIB . CUSMAS . SLSNO AS "Salesman Number" , AMFLIB . SLSMAS .
> SLSNM AS "Salesman Name" ,
>        AMFLIB . MTHACTE . ITCLS AS "Prod. Class" , AMFLIB . MBB0REP .
> B0GTTX AS "Product Class Description" ,
>                SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
> P_CURRENTMONTHDATESTART AND P_CURRENTMONTHDATEEND THEN
> AMFLIB.MTHACTE.JQGLVA ELSE 0) AS "Current Month Booking Value",
>                SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
> P_CURRENTMONTHDATESTART AND P_CURRENTMONTHDATEEND THEN
> AMFLIB.MTHACTE.COQTY ELSE 0) AS "Current Month Quantity",
>                SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
> P_CURRENTFISCALYTDDATESTART AND P_CURRENTFISCALYTDDATEEND THEN
> AMFLIB.MTHACTE.JQGLVA ELSE 0) AS "Current Fiscal YTD Booking Value",
>                SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
> P_CURRENTFISCALYTDDATESTART AND P_CURRENTFISCALYTDDATEEND THEN
> AMFLIB.MTHACTE.COQTY ELSE 0) AS "Current Fiscal YTD Quantity",
>                SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
> P_LASTFISCALYTDDATESTART AND P_LASTFISCALYTDDATEEND THEN
> AMFLIB.MTHACTE.JQGLVA ELSE 0) AS "Last Fiscal YTD Booking Value",
>                SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
> P_LASTFISCALYTDDATESTART AND P_LASTFISCALYTDDATEEND THEN
> AMFLIB.MTHACTE.COQTY ELSE 0) AS "Last Fiscal YTD Quantity"
>        FROM AMFLIB . CUSMAS
>                JOIN AMFLIB . MTHACTE
>                        ON AMFLIB . CUSMAS . CUSNO = AMFLIB . MTHACTE .
> CUSNO
>                JOIN AMFLIB . SLSMAS
>                        ON AMFLIB . CUSMAS . SLSNO = AMFLIB . SLSMAS .
> SLSNO
>                JOIN AMFLIB . MBB0REP
>                        ON AMFLIB . MTHACTE . ITCLS = AMFLIB . MBB0REP .
> B0GLCD
>        WHERE AMFLIB . MTHACTE . TERRN = SELECTION_TERRITORY
>                             GROUP BY AMFLIB . CUSMAS . COMNO ,
>                        AMFLIB . CUSMAS . TERRN ,
>                        AMFLIB . CUSMAS . SLSNO ,
>                        AMFLIB . CUSMAS . CUSCL ,
>                        AMFLIB . CUSMAS . CUSNO ,
>                        AMFLIB . MTHACTE . ITCLS ,
>                        AMFLIB . SLSMAS . SLSNM , AMFLIB . CUSMAS .
> CUSNM , AMFLIB . MBB0REP . B0GTTX' ;
>
> PREPARE S2 FROM SQLSTMT ;
> OPEN C2 ;
>
> END
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.