CODEWITHSUNDEEP
plsql
Peanut
Peanut

Reputation: 3295

PL/SQL embedding SELECT into function

I'm trying to embed a simple SELECT statement inside of a stored function, but it seems to be throwing these errors:

SQL Statement Ignored (line 9)

and

Missing left parenthesis (line 12)

First off, why is it ignoring my select statement? And second off, I have no parentheses on line 12, so I don't understand how that error is even being thrown.

But here is my stored function code nonetheless:

CREATE OR REPLACE FUNCTION tax_calc_sf
(p_basketid IN NUMBER)
RETURN NUMBER
IS
  lv_taxamt_num NUMBER;
  lv_subtotal_num bb_basket.subtotal%TYPE;
  lv_taxrate_num bb_tax.taxrate%TYPE;
BEGIN
  SELECT bb.subtotal, bt.taxrate
  INTO lv_subtotal_num, lv_taxrate_num
  FROM bb_basket bb
  JOIN bb_tax bt USING bt.state = bb.shipstate
  WHERE bb.idbasket = p_basketid;

  lv_taxamt_num := lv_subtotal_num*lv_taxrate_num;
  RETURN lv_taxamt_num;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 0;
END;
/

Upvotes: 0

Views: 1681

Answers (1)

René Nyffenegger
René Nyffenegger

Reputation: 40523

Try

ON bt.state = bb.shipstate

instead of

USING bt.state = bb.shipstate

The error Missing left parantheses is thrown precisly because there are no parantheses. The USING clause requires parantheses. It also requires the join column to be named equally in both tables. Since this is not the case in your tables, you must use the ON clause.

Upvotes: 2

Related Questions