Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Inconsistent Query related to ANALYZE and LOWER() (or other function)

Re: Inconsistent Query related to ANALYZE and LOWER() (or other function)

From: DNP <High.Flight_at_btinternet.com>
Date: 2000/05/04
Message-ID: <39110359.242A@btinternet.com>#1/1

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;

...
...
...

-- and as if by magic you get :--

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 I
can.
LAUNCELOT: No, we've got to find the Holy Grail. Come on! GALAHAD: Oh, let me have just a little bit of peril? LAUNCELOT: No. It's unhealthy.
GALAHAD: I bet you're gay..... Received on Thu May 04 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US