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

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