PL/SQL embedding SELECT into function

Question

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;
/

René Nyffenegger · Accepted Answer · 2012-11-09 21:12:47Z

2

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.

answered Nov 9, 2012 at 21:12

René Nyffenegger

40.9k36 gold badges180 silver badges321 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peanut Over a year ago

Wow what a silly mistake. xDDD I can't believe I did that :P I had using to begin with, but then I had to change it and forgot to change that. Thanks.. just needed another pair of eyes I guess

Collectives™ on Stack Overflow

PL/SQL embedding SELECT into function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related