One of the elements involved with the concat (||) is not a char or varchar.
Cast that column to char and you will be good.
David Baker
Taido Ryu Jujitsu
www.TaidoRyu.com
518-210-1000
-----Original Message-----
From: Andresi Attila
[mailto:db2udbdba-ezmlmshield-x47921391.[Email address protected]
Sent: Tuesday, December 12, 2006 10:26 AM
To: LazyDBA Discussion
Subject: Re: SPL question - WITH command
Dear Sir,
I tested the dynamic version, but ...
My error code is:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0440N No authorized routine named "||" of type "FUNCTION" having
compatible arguments was found. LINE NUMBER=32. SQLSTATE=42884
---- There is src with dynamic SQL ----
CREATE PROCEDURE a2dynGetPercent
(IN iv_shopper INT, IN iv_product INT, IN iv_OrdType SMALLINT, OUT
ov_percPen DEC(5,2))
LANGUAGE SQL
BEGIN
DECLARE v_percent DECIMAL(5,2) DEFAULT 0.0;
DECLARE v_dipct DECIMAL(5,2) DEFAULT 0.0;
DECLARE v_penalty DECIMAL(5,2) DEFAULT 0.0;
DECLARE v_puref INT DEFAULT 0;
DECLARE v_priority SMALLINT DEFAULT 0;
DECLARE stmt VARCHAR(1000);
-- v_percent = 0;
-- v_dipct = 0;
-- v_penalty = 0;
-- v_puref = 0;
-- v_priority = 0;
SELECT pu_def_disc_pct, pd_puref, pu_cons_suff
INTO v_percent, v_puref, v_penalty
FROM sb_publisher, sb_product
WHERE pu_puref = pd_puref
AND pd_pdref = iv_product;
IF (iv_OrdType <> 3)
THEN SET v_penalty = 0;
END IF;
-- check discount
SET stmt = 'WITH temp_pct_prio(di_pct, di_priority) AS '||
'(SELECT di_pct, di_priority '||
'FROM sb_discount, sb_product, sb_bookstore '||
'WHERE di_startdate <= CURRENT DATE '||
'AND CURRENT DATE <= di_enddate '||
'AND bs_bsref = ' || iv_shopper ||
'AND pd_pdref = ' || iv_product ||
'AND bs_bsref = di_bsref '||
'AND pd_pdref = di_prrfnbr '||
'UNION '||
'SELECT di_pct, di_priority '||
'FROM sb_discount, sb_product, sb_bookstore '||
'WHERE di_startdate <= CURRENT DATE '||
'AND CURRENT DATE <= di_enddate '||
'AND bs_bsref = ' || iv_shopper ||
'AND pd_pdref = ' || iv_product ||
'AND bs_bsref = di_bsref '||
'AND pd_pgref = di_pgref '||
'UNION '||
'SELECT di_pct, di_priority '||
'FROM sb_discount, sb_product, sb_bookstore '||
'WHERE di_startdate <= CURRENT DATE '||
'AND CURRENT DATE <= di_enddate '||
'AND bs_bsref = ' || iv_shopper ||
'AND pd_pdref = ' || iv_product ||
'AND bs_bgref = di_bgref '||
'AND pd_pdref = di_prrfnbr '||
'UNION '||
'SELECT di_pct, di_priority '||
'FROM sb_discount, sb_product, sb_bookstore '||
'WHERE di_startdate <= CURRENT DATE '||
'AND CURRENT DATE <= di_enddate '||
'AND bs_bsref = ' || iv_shopper ||
'AND pd_pdref = ' || iv_product ||
'AND bs_bgref = di_bgref '||
'AND pd_pgref = di_pgref '||
-- ORDER BY di_priority DESC, di_pct DESC
') '||
'SELECT di_pct, di_priority '||
'INTO '|| v_dipct ||', '|| v_priority ||
'FROM temp_pct_prio '||
'ORDER BY di_priority DESC, di_pct DESC '||
'FETCH FIRST 1 ROW ONLY '
;
PREPARE s1 FROM stmt;
EXECUTE s1;
-- If v_priority > 0, than has a row in last select
IF (v_priority > 0)
THEN SET v_percent = v_dipct;
END IF;
-- result
SET ov_percPen = v_percent - v_penalty;
END
@
Üdvözlettel / Best Regards / Mit freundlichen Gruessen
Andrési Attila / Attila Andresi
<---------------------------------------------------------------------------
->
e-mail: andresi.[Email address protected]
Mobil: (+36) 20 / 9 704 491
----- Original Message -----
From: "DANIEL CALLAHAN "
<db2udbdba-ezmlmshield-x51430582.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Monday, December 11, 2006 11:10 PM
Subject: Re: SPL question - WITH command
> You need to make either a global temp table or try doing that in dynamic
> sql. Its not legal like that in a SP. Global temp
> is the way to go.
>
>
>
>
>
>
> "Andresi Attila "
> <db2udbdba-ezmlms
> hield-x95267942.x To
> [Email Address Removed] "LazyDBA Discussion"
> a.com> <[Email address protected]
> cc
> 12/11/2006 04:59
> PM Subject
> SPL question - WITH command
>
>
>
>
>
>
>
>
>
>
> Hi,
>
> I would like to write an SPL with SQL:
>
>
> My error code is:
>
> SQL0104N An unexpected token "WITH" was found following " ". Expected
>
> tokens may include: "SELECT". LINE NUMBER=30. SQLSTATE=42601
>
> --- SPL ---
>
> CREATE PROCEDURE a2getPercent
> (IN iv_shopper INT, IN iv_product INT, IN iv_OrdType CHAR(1), OUT
> ov_percPen DEC(5,2))
> LANGUAGE SQL
> BEGIN
> DECLARE v_percent DECIMAL(5,2) DEFAULT 0;
> DECLARE v_dipct DECIMAL(5,2) DEFAULT 0;
> DECLARE v_penalty DECIMAL(5,2) DEFAULT 0;
> DECLARE v_puref INT DEFAULT 0;
> DECLARE v_priority SMALLINT DEFAULT 0;
>
> -- v_percent = 0;
> -- v_dipct = 0;
> -- v_penalty = 0;
> -- v_puref = 0;
> -- v_priority = 0;
>
>
> SELECT pu_def_disc_pct, pd_puref, pu_cons_suff
> INTO v_percent, v_puref, v_penalty
> FROM sp_publisher, sb_product
> WHERE pu_puref = pd_puref
> AND pd_pdref = iv_product;
>
> IF (iv_OrdType <> "3")
> THEN SET v_penalty = 0;
> END IF;
>
> -- check discount
>
> WITH temp_pct_prio(di_pct, di_priority) AS
> (
> SELECT di_pct, di_priority
> FROM sb_discount, sb_product, sb_bookstore
> WHERE di_startdate <= CURRENT DATE
> AND CURRENT DATE <= di_enddate
> AND bs_bsref = iv_shopper
> AND pd_pdref = iv_product
> AND bs_bsref = di_bsref
> AND pd_pdref = di_prrfnbr
> UNION
> SELECT di_pct, di_priority
> FROM sb_discount, sb_product, sb_bookstore
> WHERE di_startdate <= CURRENT DATE
> AND CURRENT DATE <= di_enddate
> AND bs_bsref = iv_shopper
> AND pd_pdref = iv_product
> AND bs_bsref = di_bsref
> AND pd_pgref = di_pgref
> UNION
> SELECT di_pct, di_priority
> FROM sb_discount, sb_product, sb_bookstore
> WHERE di_startdate <= CURRENT DATE
> AND CURRENT DATE <= di_enddate
> AND bs_bsref = iv_shopper
> AND pd_pdref = iv_product
> AND bs_bgref = di_bgref
> AND pd_pdref = di_prrfnbr
> UNION
> SELECT di_pct, di_priority
> FROM sb_discount, sb_product, sb_bookstore
> WHERE di_startdate <= CURRENT DATE
> AND CURRENT DATE <= di_enddate
> AND bs_bsref = iv_shopper
> AND pd_pdref = iv_product
> AND bs_bgref = di_bgref
> AND pd_pgref = di_pgref
> -- ORDER BY di_priority DESC, di_pct DESC
> )
>
> SELECT di_pct, di_priority
> INTO v_dipct, v_priority
> FROM temp_pct_prio
> ORDER BY di_priority DESC, di_pct DESC
> FETCH FIRST 1 ROW ONLY
> ;
>
> -- If v_priority > 0, than has a row in last select
>
> IF (v_priority > 0)
> THEN SET v_percent = v_dipct;
> END IF;
>
> -- result
>
> ov_percPen = v_percent - v_penalty;
>
> END
> @
>
> ---
>
> I would like to run with nex command on db2 CLP:
> db2 -[Email Address Removed] -vf file_name.db2
>
>
>
> Üdvözlettel / Best Regards / Mit freundlichen Gruessen
>
> Andrési Attila / Attila Andresi
>
>
<---------------------------------------------------------------------------
->
>
> e-mail: andresi.[Email address protected]
> Mobil: (+36) 20 / 9 704 491
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page