Jared
[EMAIL PROTECTED] 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
/
4. 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
