SQL Help with procedures.

Discussion in 'OT Technology' started by Gadget, Nov 6, 2006.

  1. Gadget

    Gadget New Member

    Joined:
    Apr 16, 2004
    Messages:
    668
    Likes Received:
    0
    Posted this in the main forum and got no response. Anyone help?

    Ok here's my situation...

    Code:
      [FONT=&quot]
    CREATE TABLE bb_Tax 
    ([/FONT][FONT=&quot]idState NUMBER(2),[/FONT][FONT=&quot] 
    State char(2),[/FONT][FONT=&quot] 
    TaxRate NUMBER(4,3),[/FONT][FONT=&quot] 
    CONSTRAINT tax_idstate_pk 
    PRIMARY KEY(idState) );[/FONT]
    
    The BB_TAX table contains the states that require taxes to be submitted for sales. If the state is not listed in the table, then no tax should be assessed on the order. The shopper's state and basket subtotal are the inputs into the procedure while the tax amount should be returned.


    Code:
    [FONT=&quot]INSERT INTO bb_tax VALUES(1,'VA',.045);[/FONT][FONT=&quot]
    INSERT INTO bb_tax VALUES(2,'NC',.03);[/FONT][FONT=&quot]
    INSERT INTO bb_tax VALUES(3,'SC',.06);[/FONT]
    
    I have to do the following:
    1.Create a procedure named TAX_COST_SP to accomplish the tax calculation task.
    Keep in mind that the state and subtotal values are inputs into the procedure and the procedure is to return the tax amount.
    Use the attribute types of BB_TAX.STATE for the state parameter. ([FONT=&quot]%TYPE)[/FONT]
    2.Create a host variable named G_SALES_TAX to hold the value returned by the procedure.
    3.Invoke the procedure using the values of "VA" for the state and $100 for the sale amount.
    4.Display the tax amount returned by the procedure (it should be $4.50)

    This is telling me its compiling with errors:
    Code:
    CREATE OR REPLACE PROCEDURE TAX_COST_SP ( statein BB_TAX.STATE%TYPE, subtotal IN NUMBER, taxamt OUT NUMBER) 
    AS
    BEGIN
      taxamt := subtotal * (select taxrate from bb_tax where bb_tax.state = statein);
    
    END;
    

    What am I doing wrong?
     
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    what is the error you are getting? I'm assuming this is Oracle code?
     

Share This Page