Re: SQL question

Jared

Jared.Still_at_radisys.com wrote:
> . never trust Vladimir Begun, check everything what he's saying :)
>
> Trust?
>
> I don't know you well enough to not trust you.

May be 'trust' is not a right word here :) Sorry.

> . never use the sql that looks cool but does not work properly
> . never tune a query that returns wrong result and compare its
> performance with that one that works correctly but slowly.
>
> As you will see in another post, both queries
> return identical results for me on 8.1.7.

Check default NLS_DATE_FORMAT parameters of your session.

To summarize, agregation can be done using:

1. (for this particular case) SELECT COUNT( DISTINCT( RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1)) || RPAD(NVL( job, ' '), 30 + NVL2( job, 0, 1)) || NVL(TO_CHAR(mydate, 'DDMMYYYYHH24MISS'), '*NULL*') ) ) AS l FROM emp / 2. SELECT COUNT(*) FROM ( SELECT DISTINCT ename , job , mydate FROM emp ) / 3. SELECT COUNT(COUNT(*)) FROM emp GROUP BY ename , job , mydate /
2. SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp; unreliable solution (does not handle nulls and dates properly)

SQL> SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))

                                2000

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'; Session altered

SQL> SELECT COUNT(DISTINCT(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))

                                6000

Regards,

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: Vladimir.Begun_at_oracle.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).