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

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