| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Inconsistent Query related to ANALYZE and LOWER() (or other function)
Lower is a 'single row character function'.
You cannot aggregate using a 'single row ... function'.
Hence you can't use LOWER in your GROUP BY (or a HAVING clause for that matter).
This is all a bit academic because there is a more elegant and simple way to write the statement. I've also got my suspicions that the SQL parser was getting completely confused with your SQL statements (not surprising really - they are a bit weird if you don't mind me saying so ;-) .
Solution :-
(under SCOTT schema / username)
SQL> COLUMN HDG1 HEADING 'Number of Employees in a grade | where at least 2 share a mgr '
SQL> COLUMN HDG2 HEADING 'Respective Job Grade'
SQL> COLUMN HDG2 FORMAT A20
SQL>
SQL> SELECT count(*) AS HDG1, LOWER(job) AS HDG2
FROM scott.emp
GROUP BY job, mgr
HAVING Count(*)>1;
Number of Employees in a grade
where at least 2 share a mgr Respective Job Grade
------------------------------- --------------------
2 analyst
3 manager
4 salesman
***********************************************************
The ANALYZE problem is a red-herring (i.e. appears to have something to do with the root problem but doesn't in fact).
David P.
Oracle Certified DBA.
From "Monty Python and The Holy Grail"
GALAHAD: Open the door! Open the door!
[pound pound pound]
In the name of King Arthur, open the door!
GIRLS: Hello!
ZOOT: Welcome, gentle Sir Knight. Welcome to the Castle Anthrax.
GALAHAD: The Castle Anthrax?
ZOOT: Yes. Oh, it's not a very good name, is it? Oh, but we are nice
girls and
we will attend to your every, every need!
...
...
LAUNCELOT: Sir Galahad!
GALAHAD: What?
LAUNCELOT: Quick!
GALAHAD: Why?
LAUNCELOT: You are in great peril!
ZOOT: No, he isn't.
LAUNCELOT: Silence, foul temptress!
GALAHAD: You know, she's got a point.
LAUNCELOT: Come on! We will cover your escape!
GALAHAD: Look, I'm fine!
LAUNCELOT: Come on!
GIRLS: Sir Galahad!
GALAHAD: No. Look, I can tackle this lot single-handed!
ZOOT: Yes! Let him tackle us single-handed!
GIRLS: Yes! Let him tackle us single-handed!
LAUNCELOT: No, Sir Galahad. Come on!
GALAHAD: No! Really! Honestly, I can cope. I can handle this lot
easily.
ZOOT: Oh, yes. Let him handle us easily.
GIRLS: Yes. Let him handle us easily.
LAUNCELOT: No. Quick! Quick!
GALAHAD: Please! I can defeat them! There's only a hundred-and-fifty
of
them!
ZOOT: Yes! Yes, he will beat us easily! We haven't a chance.
GIRLS: We haven't a chance. He will beat us easily...
[boom - door closes and LAUNCELOT and GALAHAD leave]
ZOOT: Oh, drat.
...
LAUNCELOT: We were in the nick of time. You were in great peril.
GALAHAD: I don't think I was.
LAUNCELOT: Yes, you were. You were in terrible peril.
GALAHAD: Look, let me go back in there and face the peril. LAUNCELOT: No, it's too perilous. GALAHAD: Look, it's my duty as a knight to sample as much peril as Ican.
 |
 |