aspose file tools*
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 JavaScript Promises Essentials this week in the JavaScript 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
Author

Query returning multiple rows by cartesian product.

Deepak Bhasin
Greenhorn

Joined: Mar 30, 2010
Posts: 5
Hi,

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 bd11.city, 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;

output------------

EXPENSE_DATE AMOUNT_TRAVEL CITY
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: http://aspose.com/file-tools
 
subject: Query returning multiple rows by cartesian product.