Re: Subselect in a select clause of statement

Hi, I think you to use temporaries table. 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', into tabletemp1. After that you can execute a select with all your tables. Regards "Stewart Cambridge London, UK" <cambridgestewart@hotmail.com> a 'crit dans le message de news: 3f78ef6f.0410080408.44876870@posting.google.com... > 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

Jane Hunt's suggestion of losing the quote marks and adding columns 2 & 3 to the GROUP BY worked very well, so thank you Jane. However Serge's creative use of DECODE gives the same result and runs much faster on a table with 000's of rows. Thank you both. Stewart