Select statement aberations

[dickiebird]

Guys.....
If I run this on Sybase 11.5

select
l.batch_number,
l.batch_position,
l.document_number,
l.currency_code
from
sales_advice_line s, loose_sales_advice l
where
l.batch_number = s.batch_number and
l.batch_position = s.batch_position and
l.document_number = s.document_number and
l.batch_number = 1 and
l.batch_position = 1 and
l.currency_code = 11

I get one row returned - as I hoped and expected.
eg 1 1 2 11

But if I add some calculations .....
select
l.batch_number,
l.batch_position,
l.document_number,
l.currency_code,
sum((s.end_cheque - s.start_cheque)*s.denomination)
from
sales_advice_line s, loose_sales_advice l
where
l.batch_number = s.batch_number and
l.batch_position = s.batch_position and
l.document_number = s.document_number and
l.batch_number = 1 and
l.batch_position = 1 and
l.currency_code = 11

then I pick up another row as well (different in currency code)
eg
1 1 1 12 100
1 1 2 11 100

which now doesn't obey the where condition 'and l.currency_code = 11'
Any ideas why ???
Thanks in advance ;-) Dickie Bird
db@dickiebird.freeserve.co.uk

 

[JMCraig]

Hey Dickie!

I think you need a GROUP BY clause to go with your aggregate functions. Oddly, Sybase sometimes returns bogus data when you have aggregate functions without a proper GROUP BY--give it a shot, it may work.

BOL,

J M Craig
nsjmcraig@netscape.net