This week's book giveaway is in the Cloud/Virtualizaton forum.
We're giving away four copies of Mesos in Action and have Roger Ignazio on-line!
See this thread for details.
Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query returning multiple rows by cartesian product.

 
Deepak Bhasin
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic