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.
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;
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.