Zaman31502   110 pts.  |   Aug 11 2009  4:08AM GMT

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

Carlosdl   29855 pts.  |   Aug 11 2009  2:10PM GMT

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.

Zaman31502   110 pts.  |   Aug 13 2009  6:38AM GMT

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.

Carlosdl   29855 pts.  |   Aug 13 2009  1:58PM GMT

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 ?

Zaman31502   110 pts.  |   Aug 17 2009  6:10AM GMT

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.

Darryn   370 pts.  |   Aug 17 2009  1:53PM GMT

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)

Zaman31502   110 pts.  |   Aug 18 2009  6:15AM GMT

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.

Carlosdl   29855 pts.  |   Aug 18 2009  2:13PM GMT

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.

Zaman31502   110 pts.  |   Aug 19 2009  2:54AM GMT

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.

Carlosdl   29855 pts.  |   Aug 19 2009  4:29AM GMT

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,

Zaman31502   110 pts.  |   Aug 26 2009  4:37AM GMT

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.

Carlosdl   29855 pts.  |   Aug 26 2009  1:27PM GMT

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

Zaman31502   110 pts.  |   Aug 27 2009  4:23AM GMT

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.

Zaman31502   110 pts.  |   Aug 27 2009  4:43AM GMT

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.

Carlosdl   29855 pts.  |   Aug 27 2009  2:24PM GMT

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.