This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Java Interview Guide and have Anthony DePalma on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes Query returning multiple rows by cartesian product. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Query returning multiple rows by cartesian product." Watch "Query returning multiple rows by cartesian product." New topic

Query returning multiple rows by cartesian product.

Deepak Bhasin

Joined: Mar 30, 2010
Posts: 5

I am developing an application and require to retreive fields with sum of differnt head values for a particular date, also i want to retrieve the value of city corresponding to the value of stay if exists in table.

I am getting the sum for all heads correctly but the query is returning multiple values for city. If it dont exist on a particular date it is still showing the values for that date by cartesian product.

Query -----------

select distinct EXPENSE_DATE,(select sum(BASEAMOUNT*exchange_rate) from businessdetails bd1 where bd1.expense_id=bd.expense_id and bd1.DETAILS_HEAD_CODE='T' and bd1.expense_id=7772 and bd.expense_date=bd1.expense_date ) as amount_Travel,(select city from (select, ROW_NUMBER() OVER(ORDER BY bd11.EXPENSE_DATE) as row FROM businessdetails bd11,businessdetails bd10 where bd11.expense_id=7772 and bd11.DETAILS_HEAD_CODE='S' and bd11.expense_id=bd10.expense_id and bd11.expense_date=bd10.expense_date) as emp where row=1) as CITY from businessdetails bd where expense_id=7772;


2009-02-18 6.46400000000000E+003 Bangalore
2009-02-19 6.46400000000000E+003 Bangalore

in this case city only exist in case of expense_date=2009-02-19 but it still getting it for 18th as well.

any suggestions....will be welcome....

Thanks for any help.
I agree. Here's the link:
subject: Query returning multiple rows by cartesian product.
It's not a secret anymore!