Re: Stored Procedure with different SUMs

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Sounds like you may have been missing a semi-colon somewhere when you
were using static.

Please show the entire statement.

Charles

On Tue, Jul 7, 2009 at 8:55 AM, ibm<ibm@xxxxxxxxxx> wrote:
> AMFLIB.MTHACTE.TERRN is NUMERIC(2,0).
>
> Parameters:
> SELECTION_TERRITORY     NUMERIC(2,0)    IN
> SELECTION_YEAR          INTEGER         IN
> SELECTION_MONTH         INTEGER         IN
>
> I switched to dynamic SQL because once I started declaring my P_xxxxx variables I would get a compile error with static SQL.
>
> For example, the below keeps throwing "Token MYVAR is not valid. Valid tokens: GLOBAL."  SQL0104.
>
> So the only way I was able to figure this out is to switch to dynamic SQL and the error went away.  Is there a way to go back to static?
>
> DECLARE MYVAR INTEGER;
>
> DECLARE C1 CURSOR FOR
> SELECT
> AMFLIB.CUSMAS.COMNO
> FROM AMFLIB.CUSMAS
> WHERE
> AMFLIB.CUSMAS.CUSNO = 55900;
>
> OPEN C1;
>
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
> Sent: Monday, July 06, 2009 3:35 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: Stored Procedure with different SUMs
>
> 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.
> --
> 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.
>
> --
> 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.