Change

" WITH temp_pct_prio(di_pct, di_priority) AS "

to

"WITH temp_pct_prio AS "



David Baker
UDB World site admin and bad DBA
www.UDBWorld.com
[Email address protected]


-----Original Message-----
From: Andresi Attila
[mailto:db2udbdba-ezmlmshield-x95267942.[Email address protected]
Sent: Monday, December 11, 2006 4:59 PM
To: LazyDBA Discussion
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



DB2 & UDB email list listserv db2-l LazyDBA home page