• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Query returning multiple rows by cartesian product.

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
They weren't very bright, but they were very, very big. Ad contrast:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic