oracle 10g query

110 pts.
Tags:
Oracle 10g
Oracle queries
attributes of income table are(id,date,income_amount) and of expense table are(expid, expdate,exp_amount,paid,unpaid).i need sum of "income_amount" and sum of "paid" to calculate the profit or loss.

Answer Wiki

Thanks. We'll let you know when a new response is added.

<pre>CREATE OR REPLACE FUNCTION GET_PROFIT(p_StartDate IN DATE, p_EndDate IN DATE) IS
l_income number;
l_paid number;
BEGIN
SELECT SUM(income_amount) INTO l_income
FROM income
WHERE date BETWEEN l_StartDate AND l_EndDate;
SELECT SUM(paid) INTO l_paid
FROM expense
WHERE date BETWEEN l_StartDate AND l_EndDate;
RETURN l_income – l_paid;
END;</pre>

P.S. If you need further help or you want to add some details about your query, <b>please </b><i>do not create a new question. There is a discussion section below, in which you can add details or ask for clarification if needed.</i>

Discuss This Question: 16  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Zaman31502
    thank you for this best answer. if i want to use the query behind the report to show tha profit or loss how can i create the report using this code. plz tell me the way because i have to show the profit or loss report
    110 pointsBadges:
    report
  • carlosdl
    Are you talking about an Oracle Developer report ? If so, you could create a "formula column" in the data model of your report, and use the above function as the "PL/SQL formula". Then you create a field in the layout section, with the previously created formula column as its source. Feel free to ask if you need more help.
    69,175 pointsBadges:
    report
  • Zaman31502
    thanks dear to reponse me. if i create a field with this formula as its source, then i think it will show only one result either for sum of expense or sum of income but i need both two sums. Actually i have to display the profit or loss in the form of oracle developer report which 's name "income report". dear sir plz tell me the code or query for report in which i retrieve the data from the two tables income and expense and get the profit or loss. dear sir if u dont mind give me some detail with the code. because i have done every thing which i know for this but i could not succeeded. plz sir give me the solution which is report based because i dont show the result in the form. i will wait for ur reply.
    110 pointsBadges:
    report
  • carlosdl
    No. The above function doesn't return income or expense sums, it returns the difference between both, which is actually the profit (or loss, when negatvie). You might need to provide more deatils about the report, because I don't think you are creating a report which will show just one single amount. Could you please describe the report with more detail ? what other information are you going to show ? how many rows will be included ?
    69,175 pointsBadges:
    report
  • Zaman31502
    the columns i want to add in my report are: 1- total income, 2- total expense, 3-profit basically i m designing a database dealing with the sale and purchase of the products. therefore i need to calculate the profit or loss on the sale and expenses. i want these three three columns in my report and i want a query due to which my report display the total income and total expenses betwee two dates(on user desire). this report will consist of only one row which contains total income , total expense and profit or loss. plz help me designing such report.
    110 pointsBadges:
    report
  • Darryn
    select sum(income) total_income, sum(expense) total_expense, (sum(income) - sum(expense)) total_profit_loss from (select sum(income_amount) income, 0 expense, 0 profit_loss from income where date between var_start_date and var_end_date union select 0 income, sum(paid) expense, 0 profit_loss from expense where date between var_Start_date and var_end_date)
    765 pointsBadges:
    report
  • Zaman31502
    thank u dear sir for sending me such a strong query, dear sir if u dont mind plz explaing this query . i dont understand the use of "0" in the query . plz explain me why u use (0 expense, 0 profit_loss etc) in the query . what is the function of this "0" and union. plz explain this little bit.
    110 pointsBadges:
    report
  • carlosdl
    Darryn is using 0 because his sub-queries return 3 columns (income, expense and profit_loss), but the first one gets data from INCOME only, and the second one gets data from EXPENSE only, so the first sub-query will provide the sum of INCOME and has to return 0 for expense so that it does not affect the result of the SUM(expense) in the main query. The same applies for the second sub-query. It is returning the sum of EXPENSE, and it has to return 0 for income so that it does not affect the result of the SUM(income) in the main query. As for the profit_loss in the sub-queries, I don't think it is necessary. Hope this helps.
    69,175 pointsBadges:
    report
  • Zaman31502
    thanks to both of u. if u dont mind if i face further problem than i will again ask a question. "select sum(income) total_income, sum(expense) total_expense, (sum(income) - sum(expense)) total_profit_loss" in this select statement, "sum(income)" this income is table name or an attribute from the table likewise "sum(expense)" this word expense is table name or a column from the expense table. what is the function of union? is this union unite the two queries? plz sir explain it in little detail.
    110 pointsBadges:
    report
  • carlosdl
    In this case, the query is not getting data directly from tables, but from another SELECT (which works like a temporary table or view), and income and expense are columns from that view.
    (select sum(income_amount) income, 0 expense, 0 profit_loss
    from income
    where date between var_start_date and var_end_date
    union
    select 0 income, sum(paid) expense, 0 profit_loss
    from expense
    where date between var_Start_date and var_end_date)
    The UNION operator is used to combine the results of two or more queries into a single result. I would recommend you read some SQL tutorial like this to get some basic knowledge of the SQL language. Regards,
    69,175 pointsBadges:
    report
  • Zaman31502
    hi dear i check this query select sum(income) total_income, sum(expense) total_expense, (sum(income) - sum(expense)) total_profit_loss from (select sum(income_amount) income, 0 expense, 0 profit_loss from income where date between var_start_date and var_end_date union select 0 income, sum(paid) expense, 0 profit_loss from expense where date between var_Start_date and var_end_date) in the SQL server 2000 . it give me the following error Server: Msg 170, Level 15, State 1, Line 10 Line 10: Incorrect syntax near ')'. plz dear tell me how can i resolve this error. actually i did not apply this code in the oracl 10g yet. but i make a practice of SQL by using SQL server 2000 with the help of material u give me. plz sir check this query and correct my syntax error which i mention above.
    110 pointsBadges:
    report
  • carlosdl
    In SQL server you will probably need to assign an alias for the view. Something like this:
    select sum(income) total_income, sum(expense) total_expense, (sum(income) - sum(expense)) total_profit_loss
    from
    (select sum(income_amount) income, 0 expense, 0 profit_loss
    from income
    where date between var_start_date and var_end_date
    union
    select 0 income, sum(paid) expense, 0 profit_loss
    from expense
    where date between var_Start_date and var_end_date) AS income_expense
    69,175 pointsBadges:
    report
  • Zaman31502
    dear sir i try the code u send me it also give a number of errors about my column names. it gives me error like this unknown column name income, unknown column name paid etc all the column i use in my query it treat them as error. dear sir can the following code works fine in the oracle 10g with out any errors or it also show me the errors. select sum(income) total_income, sum(expense) total_expense, (sum(income) - sum(expense)) total_profit_loss from (select sum(income_amount) income, 0 expense, 0 profit_loss from income where date between var_start_date and var_end_date union select 0 income, sum(paid) expense, 0 profit_loss from expense where date between var_Start_date and var_end_date) how the parameters var_Start_date and var_end_date works, Is when i run the report these parameters will come to get value separately for income table and expense table or they will get value oncely. plz sir tell me this. sir i try the following code in sql server 2000 to get data from two tables it works but i cannot succeed to get profit from this plz sir also tell me how can i use parameters with this code . the code is : select distinct income_amount = (select sum(income_amount) from income ), paid=(select sum(paid)from expense ) from income, expense plz sir check this code and plz make amy ammendments in it how to calculate profit with it. thank you sir for responding me. because ur suggestions are very helpfull for me.plz sir also tell me about the first code will it work with oracle 10g without errors and also about its parameters how they will work. i m waiting for ur reply.
    110 pointsBadges:
    report
  • Zaman31502
    dear sir i try the code u send me it also give a number of errors about my column names. it gives me error like this unknown column name income, unknown column name paid etc all the column i use in my query it treat them as error. dear sir can the following code works fine in the oracle 10g with out any errors or it also show me the errors. select sum(income) total_income, sum(expense) total_expense, (sum(income) - sum(expense)) total_profit_loss from (select sum(income_amount) income, 0 expense, 0 profit_loss from income where date between var_start_date and var_end_date union select 0 income, sum(paid) expense, 0 profit_loss from expense where date between var_Start_date and var_end_date) how the parameters var_Start_date and var_end_date works. Is when i run the report these parameters will prompt to get start date and end date separately for income table and expense table or they will prompt oncely for both income and expense subqueries? if sir this query prompt for both subqueries separately then how can it possible to prompt once for start date and end date for both subqueries. plz sir tell me this. sir i try the following code in sql server 2000 to get data from two tables it works but i cannot succeed to get profit from this plz sir also tell me how can i use parameters with this code . the code is : select distinct income_amount = (select sum(income_amount) from income ), paid=(select sum(paid)from expense ) from income, expense plz sir check this code and plz make any ammendments in it how to calculate profit with it. thank you sir for responding me. because ur suggestions are very helpfull for me.plz sir also tell me about the first code will it work with oracle 10g without errors and also about its parameters how they will work. i m waiting for ur reply.
    110 pointsBadges:
    report
  • carlosdl
    The first code will work in Oracle if: You have a table named INCOME, with the following columns (at least): -income_amount -date And you have a table named EXPENSE, with the following columns (at least): -paid -date If you are going to run your query from SQL*Plus, you should prefix the parameters with '&'
    where date between &var_Start_date and &var_end_date
    If you are going to put the query in a Developer report, I think you have to prefix them with ':' In Sql Server, I think you will have to write a stored procedure if you want to use the dates as parameters. You could, however, try your queries with specific dates. As for you second query, it would be more efficient writing a stored procedure or function, similar to the one suggested in the original answer, but with SQLServer syntax.
    69,175 pointsBadges:
    report
  • helloworldwecome
    I have an array of coordinates. For this I have created a type "create or replace type coo as varchar(1000) of number(10)" . all I want to is to read each and every value of this
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following