Problem with a compless query on Oracle 8i..

From: HeavyBrain <actis_at_heavybrain.net>
Date: Thu, 03 Jul 2003 19:24:20 +0200
Message-ID: <gcp8gv4fskmsb2k0q2rhlg3tjb5s8mbsj6@4ax.com>

Hi,

I must write a query on a schema designed by a programmer ( bad designed on my opinion, but i can redesign it) for a one shoot report for management.

The DB is a Oracle 8i (8.1.7.4) on Linux...

This first query work as requested and extract the required data

SELECT
e.id_utente,e.nome_utente,e.cognome_utente,e.id_campagna,e.num_msg
, (select count(*) from destinatario f where f.id_campagna =
e.id_campagna

           AND f.stato_destinatario = 3) AS msg_riusciti
, (select count(*) from escalation g where g.id_destinatario IN

     (SELECT h.id_destinatario FROM DESTINATARIO h WHERE h.id_campagna = e.id_campagna)

      AND g.tipo_terminale = 'VOCE') AS NUM_MSG_VOCE
, (select count(*) from escalation g where g.id_destinatario IN

     (SELECT h.id_destinatario FROM DESTINATARIO h WHERE h.id_campagna = e.id_campagna)

      AND g.tipo_terminale = 'SMS') AS NUM_MSG_SMS
, (select count(*) from escalation g where g.id_destinatario IN

     (SELECT h.id_destinatario FROM DESTINATARIO h WHERE h.id_campagna = e.id_campagna)

      AND g.tipo_terminale = 'EMAIL') AS NUM_MSG_EMAIL
, (select count(*) from escalation g where g.id_destinatario IN

     (SELECT h.id_destinatario FROM DESTINATARIO h WHERE h.id_campagna = e.id_campagna)

      AND g.tipo_terminale = 'FAX') AS NUM_MSG_FAX  FROM (
 SELECT
  a.id_utente,a.nome_utente,a.cognome_utente,b.id_campagna,count(*) as num_msg
  FROM utente_tmp a,campagna b,destinatario c   WHERE b.id_utente = a.id_utente AND c.id_campagna = b.id_campagna   GROUP BY a.id_utente,a.nome_utente,a.cognome_utente,b.id_campagna  ) e;

the time required for extraction is about 45 seconds, acceptable for a one time report.

But logically the manager has a big idea, why not add a field to report if some repository need escalation to be contacted...

I have tried to extend the query to address that problem and the query become:

SELECT
e.id_utente,e.nome_utente,e.cognome_utente,e.id_campagna,e.num_msg
, (select count(*) from destinatario f where f.id_campagna =
e.id_campagna

           AND f.stato_destinatario = 3) AS msg_riusciti
, (select count(*) from escalation g where g.id_destinatario IN

     (SELECT h.id_destinatario FROM DESTINATARIO h WHERE h.id_campagna = e.id_campagna)

      AND g.tipo_terminale = 'VOCE') AS NUM_MSG_VOCE
, (select count(*) from escalation g where g.id_destinatario IN

     (SELECT h.id_destinatario FROM DESTINATARIO h WHERE h.id_campagna = e.id_campagna)

      AND g.tipo_terminale = 'SMS') AS NUM_MSG_SMS
, (select count(*) from escalation g where g.id_destinatario IN

     (SELECT h.id_destinatario FROM DESTINATARIO h WHERE h.id_campagna = e.id_campagna)

      AND g.tipo_terminale = 'EMAIL') AS NUM_MSG_EMAIL
, (select count(*) from escalation g where g.id_destinatario IN

     (SELECT h.id_destinatario FROM DESTINATARIO h WHERE h.id_campagna = e.id_campagna)

      AND g.tipo_terminale = 'FAX') AS NUM_MSG_FAX -- THE PROBLEM ARISE ON FOLLOWING LINES --
, (select SUM(prova)/COUNT(*) FROM

      (select g.id_destinatario,count(*) AS prova
       from escalation g where g.id_destinatario IN
        (SELECT  h.id_destinatario FROM DESTINATARIO h
         WHERE h.id_campagna = e.id_campagna) -- LINE 21 HERE
        GROUP BY id_destinatario)) AS PROVA

-- HERE THE QUERY IS THE SAME AS THE WORKING ONE  FROM (
 SELECT
  a.id_utente,a.nome_utente,a.cognome_utente,b.id_campagna,count(*) as num_msg
  FROM utente_tmp a,campagna b,destinatario c   WHERE b.id_utente = a.id_utente AND c.id_campagna = b.id_campagna   GROUP BY a.id_utente,a.nome_utente,a.cognome_utente,b.id_campagna  ) e;

But the Oracle SQLPLUS give me the following error in line 21

         WHERE h.id_campagna = e.id_campagna) -- LINE 21 HERE
                                                       *

ERROR at line 21:
ORA-00904: invalid column name

Anyone as a idea of what happen ???
The query is a terrible one of course but seem logically correct for me...

Best Regards and good work to all

PS I apologize for my horrible english.. i am a poor italian guy

--
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.

Voster semper Voster
 HeavyBrain aka .Andrea Actis.

Received on Thu Jul 03 2003 - 12:24:20 CDT