Subselect in a select clause of statement

This is a similar enquiry as the "Subselect in a Select Statement" one made by Joe (eyebyte@yahoo.com) on 2004-04-07. I have a log with transactions with various statuses, each one timestamped. I'm trying to create a summary so that you'd see something like: Committed Errors 2004-07-09 21 3 2004-07-10 24 5 I'm trying to do it with subselects in the select clause like this, but Informix throws a syntax error. Is there another/better way to achieve this or is it not possible? Any advice or enlightenment on this SQL is very appreciated... (Currently I'm getting by with manually running each subselect and copy'n'paste the results into a speadsheet.....) SELECT EXTEND(creation_date, YEAR TO DAY ), (SELECT COUNT(*) FROM transaction_log WHERE transaction_status = 'Committed' AND EXTEND(creation_date, YEAR TO DAY ) = EXTEND(tl.creation_date, YEAR TO DAY)) AS 'Committed', (SELECT COUNT(*) FROM transaction_log WHERE transaction_status <> 'Committed' AND EXTEND(creation_date, YEAR TO DAY ) = EXTEND(tl.creation_date, YEAR TO DAY)) AS 'Errors' FROM transaction_log tl GROUP BY 1 ORDER BY 1

"Stewart Cambridge London, UK" <cambridgestewart@hotmail.com> wrote: > This is a similar enquiry as the "Subselect in a Select Statement" one > made by Joe (eyebyte@yahoo.com) on 2004-04-07. > > I have a log with transactions with various statuses, each one > timestamped. > I'm trying to create a summary so that you'd see something like: > > Committed Errors > 2004-07-09 21 3 > 2004-07-10 24 5 > > I'm trying to do it with subselects in the select clause like this, > but Informix throws a syntax error. Is there another/better way to > achieve this or is it not possible? Any advice or enlightenment on > this SQL is very appreciated... > (Currently I'm getting by with manually running each subselect and > copy'n'paste the results into a speadsheet.....) > > SELECT EXTEND(creation_date, YEAR TO DAY ), > (SELECT COUNT(*) > FROM transaction_log > WHERE transaction_status = 'Committed' > AND EXTEND(creation_date, YEAR TO DAY ) = > EXTEND(tl.creation_date, YEAR TO DAY)) AS 'Committed', > (SELECT COUNT(*) > FROM transaction_log > WHERE transaction_status <> 'Committed' > AND EXTEND(creation_date, YEAR TO DAY ) = > EXTEND(tl.creation_date, YEAR TO DAY)) AS 'Errors' > FROM transaction_log tl > GROUP BY 1 > ORDER BY 1 A couple of very simple changes to your statement worked fine for my tests: SELECT EXTEND(creation_date, YEAR TO DAY ), (SELECT COUNT(*) FROM transaction_log WHERE transaction_status = 'Committed' AND EXTEND(creation_date, YEAR TO DAY ) = EXTEND(tl.creation_date, YEAR TO DAY)) AS Committed, (SELECT COUNT(*) FROM transaction_log WHERE transaction_status <> 'Committed' AND EXTEND(creation_date, YEAR TO DAY ) = EXTEND(tl.creation_date, YEAR TO DAY)) AS Errors FROM transaction_log tl GROUP BY 1, 2, 3 ORDER BY 1 I removed the single quote marks from 'Committed' and 'Errors' after each AS, and added 2, 3 to the GROUP BY... -- June Hunt

This should be equivalent... SELECT EXTEND(creation_date, YEAR TO DAY ), SUM(DECODE(transaction_status, 'Committed', 1, 0)) AS "Commited", SUM(DECODE(transaction_status, 'Committed', 0, 1)) AS "Errors" FROM transaction_log tl GROUP BY 1 ORDER BY 1 Cheers Serge

Serge Rielau wrote: > This should be equivalent... > > SELECT EXTEND(creation_date, YEAR TO DAY ), > SUM(DECODE(transaction_status, 'Committed', 1, 0)) AS "Commited", > SUM(DECODE(transaction_status, 'Committed', 0, 1)) AS "Errors" > FROM transaction_log tl > GROUP BY 1 > ORDER BY 1 Lose the double quotes around the display labels and it works very nicely. -- June Hunt