Home » SQL & PL/SQL » SQL & PL/SQL » How to use a function (oracle 11g)
How to use a function [message #582949] Tue, 23 April 2013 12:08 Go to next message
ApacheOmega
Messages: 9
Registered: April 2013
Location: Alabama
Junior Member
I created a function and what I'm trying to do with this function is add tax to a particular order in my database. I want it to multiply the quantity and cost of the order then add tax and then insert it into the corresponding column.

for the tax I'm using this function:
Create or replace function tax(v_value in float) return float is
Begin
Return(v_value*.07);
End tax;

select PRODUCT_ID,TOTAL_COST, tax('TOTAL_COST') from PRODUCT;

I get an error saying TOTAL_COST is an invalid identifier.

This is my table structure:


SQL> select *
  2  FROM user_cons_columns
  3  WHERE table_name = 'CUSTOMER';

OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

  POSITION
----------
SYSTEM                         SYS_C0011875
CUSTOMER
CUSTOMER_ID



OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

  POSITION
----------
SYSTEM                         CUSTOMER_PK
CUSTOMER
CUSTOMER_ID
         1


SQL> select * FROM CUSTOMER;

CUSTOMER_ID F_NAME               L_NAME
----------- -------------------- --------------------
ADDRESS                        CITY                           ST ZIP_CODE
------------------------------ ------------------------------ -- ----------
          0 John                 Cuzack
2411 South Bolo Sreet          Boston                         MA 56128

        111 Larry                Bird
8591 East Bagdale Ln.          Charleston                     SC 86111

        222 Barbera              Bigalow
2111 Linsicom St.              Palm Springs                   CA 28351


CUSTOMER_ID F_NAME               L_NAME
----------- -------------------- --------------------
ADDRESS                        CITY                           ST ZIP_CODE
------------------------------ ------------------------------ -- ----------
        333 Allen                Winston
8123 N. Ride Out Rd.           Tucson                         AZ 85730

        444 Ronda                Widlle
9000 E. 22nd St.               Chicago                        IL 71452


SQL> select *
  2  FROM user_cons_columns
  3  WHERE table_name = 'ORDER_INFO';

OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

  POSITION
----------
SYSTEM                         SYS_C0011878
ORDER_INFO
ORDER_ID



OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

  POSITION
----------
SYSTEM                         ORDER_INFO_PK
ORDER_INFO
ORDER_ID
         1


OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

  POSITION
----------
SYSTEM                         ORDER_INFO_FK
ORDER_INFO
CUSTOMER_ID
         1


SQL>
SQL> select * FROM ORDER_INFO;

  ORDER_ID ORDER_DAT CUSTOMER_ID
---------- --------- -----------
       101 01-FEB-13         444
       107 17-NOV-11         333
       102 13-DEC-12         111
       103 10-JUL-12         222
       104 25-MAR-13           0

SQL> select *
  2  FROM user_cons_columns
  3  WHERE table_name = 'PRODUCT';

OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

  POSITION
----------
SYSTEM                         SYS_C0011881
PRODUCT
PRODUCT_ID



OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

  POSITION
----------
SYSTEM                         PRODUCT_PK
PRODUCT
PRODUCT_ID
         1


SQL> select * from PRODUCT;

PRODUCT_ID PRODUCT_DESCRIPTION
---------- --------------------------------------------------
FINISH               LINE_ STANDARD_PRICE
-------------------- ----- --------------
     10001 SAMSUNG, Laptop, 1TB HD, i7 Processor
Metalic Grey         LT001         895.99

     10002 TOSHIBA, Laptop, 500GB HD, i5 Processor
Plastic Black        LT002         680.99

     10003 SONY VIO, Laptop, 750GB HD, i5 Processor
Plastic Pearl White  LT003         650.95


PRODUCT_ID PRODUCT_DESCRIPTION
---------- --------------------------------------------------
FINISH               LINE_ STANDARD_PRICE
-------------------- ----- --------------
     10004 Macintosh, Laptop, 1TB HD, Mac OS
Metalic Flat Grey    LT004        1200.89

     10005 ASUS, Laptop, 1TB HD, i5 Processor
Plastic Black        LT005         689.95


SQL> select *
  2  FROM user_cons_columns
  3  WHERE table_name = 'ORDER_LINE';

OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

  POSITION
----------
SYSTEM                         SYS_C0011884
ORDER_LINE
ORDER_ID



OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

  POSITION
----------
SYSTEM                         ORDER_LINE_PK
ORDER_LINE
ORDER_ID
         1


OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

  POSITION
----------
SYSTEM                         ORDER_LINE_PK
ORDER_LINE
PRODUCT_ID
         2


OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

  POSITION
----------
SYSTEM                         ORDER_LINE_FK
ORDER_LINE
ORDER_ID
         1


OWNER                          CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

  POSITION
----------
SYSTEM                         ORDER_LINE_FK2
ORDER_LINE
PRODUCT_ID
         1


SQL> select * from ORDER_LINE;

  ORDER_ID PRODUCT_ID ORDERD_QUANTITY TOTAL_COST
---------- ---------- --------------- ----------
       101      10001               1
       102      10002               1
       103      10005               2
       104      10003               1
       107      10004               3

SQL>


I want the total cost to be inserted into the total cost column.
Can some one explain to me how I work this out through either a function or procedure or both?
Re: How to use a function [message #582950 is a reply to message #582949] Tue, 23 April 2013 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 57634
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
select PRODUCT_ID,TOTAL_COST, tax('TOTAL_COST') from PRODUCT;

1. This can't work if TOTAL_COST is not a column of PRODUCT, and it is not.
2. tax('TOTAL_COST'), if you want to pass the value of the column TOTAL_COST then remove ' around the name

From your previous topics:

BlackSwan wrote on Tue, 16 April 2013 14:58
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


BlackSwan wrote on Tue, 23 April 2013 02:34
...
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


BlackSwan wrote on Tue, 23 April 2013 05:57
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
...


Michel Cadot wrote on Tue, 16 April 2013 21:30
Post an actual test case; what you say is very very hard to understand.
...


With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And FEEDBACK to those that answer and help you, something you did NEVER do.
If I have no feedback (and thank) I just think it is a waste of time to (try to) help you.
So fulfil the requirements and thank people.
We are waiting you review ALL your previous topics.

Regards
Michel





Re: How to use a function [message #582952 is a reply to message #582949] Tue, 23 April 2013 12:37 Go to previous messageGo to next message
ApacheOmega
Messages: 9
Registered: April 2013
Location: Alabama
Junior Member
Thanks Michel
and sorry about the guidelines. will read asap!!!
Re: How to use a function [message #582953 is a reply to message #582952] Tue, 23 April 2013 12:57 Go to previous messageGo to next message
BlackSwan
Messages: 21964
Registered: January 2009
Senior Member
>select PRODUCT_ID,TOTAL_COST, tax('TOTAL_COST') from PRODUCT;
not as above but as below
select PRODUCT_ID,TOTAL_COST, TOTAL_COST*0.07 TAX from PRODUCT;

there is ZERO need for any function!
Re: How to use a function [message #582954 is a reply to message #582952] Tue, 23 April 2013 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 57634
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ApacheOmega wrote on Tue, 23 April 2013 19:37
Thanks Michel
and sorry about the guidelines. will read asap!!!


Will help as soon as you'll apply the guide lines AND feedback to your previous topics.
For the moment, my opinion has not changed: it is a waste of time to help you.

Regards
Michel

[Updated on: Tue, 23 April 2013 13:15]

Report message to a moderator

Re: How to use a function [message #582959 is a reply to message #582953] Tue, 23 April 2013 15:11 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
BlackSwan wrote on Tue, 23 April 2013 13:57
>select PRODUCT_ID,TOTAL_COST, tax('TOTAL_COST') from PRODUCT;
not as above but as below
select PRODUCT_ID,TOTAL_COST, TOTAL_COST*0.07 TAX from PRODUCT;


Thee does not seem to be TOTAL_COST in PRODUCT table either.
Re: How to use a function [message #582978 is a reply to message #582953] Wed, 24 April 2013 03:34 Go to previous message
ThomasG
Messages: 3065
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
BlackSwan wrote on Tue, 23 April 2013 19:57
there is ZERO need for any function!


Having dealt with taxes myself, the one definite need is that the business requirement "apply tax" is used in hundreds or even thousands of places in your code and your views, but HOW taxes are applied changes quite often.

So having one and only one "apply tax" function for one type of tax that you can change in one places is definitely a good idea in general if you don't want to change thousands of lines of code every time new tax laws come out or the tax rate changes.
Previous Topic: Handeling special charater '&' in oracle stored procedure
Next Topic: best way to send an email in oracle
Goto Forum:
  


Current Time: Wed Apr 23 03:49:57 CDT 2014

Total time taken to generate the page: 0.06659 seconds