I don't understand what's happening with a query. If I have the following SQL

SELECT
(SELECT _Type FROM Banks WHERE Banks._ID = Transactions._fkBanks) AS _TypeCode,
SUM(_Expenditure)
FROM Transactions
WHERE
_DatePaid IS NULL
GROUP BY _TypeCode

I get one row returned. If I add in a column from Transactions into the result set

SELECT
_fkBanks,
(SELECT _Type FROM Banks WHERE Banks._ID = Transactions._fkBanks) AS _TypeCode,
SUM(_Expenditure)
FROM Transactions
WHERE
_DatePaid IS NULL
GROUP BY _TypeCode

I get what I would have expected - five rows. It doesn't matter what the Transactions column is it just needs one from the table to work, I thought the subselect would have been adequate to achieve what I want but it isn't. I've got a simple workround but I'd like to know why its needed. Anyone with any thoughts?


Roy Lambert

Roy,

<< I get what I would have expected - five rows. It doesn't matter what the Transactions column is it just needs one from the table to work, I thought the subselect would have been adequate to achieve what I want but it isn't. I've got a simple workround but I'd like to know why its needed. >>

You need the extra column in order to prevent EDB from considering the query a single-row select statement.  Without the extra column, you're left with a SUM() expression and a sub-select, and the sub-select is entirely derived from the outer query, so it will simply go along with whatever the outer query generates in terms of rows.

If you want the sub-query to affect the number of rows *by itself*, then you need to use a join and add the sub-query as a derived table that is part of the join.

Tim Young
Elevate Software
www.elevatesoft.com

Tim


Thanks for that. I may have a look at the approach you suggest, not because I'm unhappy at adding the extra column just for interest.

Mind you scrating my head in puzzlement may have caused a bald patch


Roy Lambert