GROUP BY issue 2

[ljsmith91]

I am new to Oracle SQL but have experience with MS SQL. I am attempting a simple Query but it is failing in the GROUP BY. Below is the sql query along with the error I get back...can anyone help me determine why my GROUP BY fails ? Thanks in advance for any help.

SQL> 
  1  SELECT
  2      apt_v_nbu_job.VENDOR_JOB_TYPE,
  3      apt_v_nbu_job.START_DATE,
  4      apt_v_nbu_job.FINISH_DATE,
  5      SUM(apt_v_nbu_job.KILOBYTES) as TOTAL_KBYTES,
  6      SUM(apt_v_nbu_job.NBR_OF_FILES) as TOTAL_FILES,
  7      SUM(apt_v_nbu_job.TRY_COUNT) as TOTAL_TRYS,
  8      COUNT(apt_v_nbu_job.STREAM_INDEX) as TOTAL_STREAMS,
  9      COUNT(DISTINCT apt_v_nbu_job.NBU_JOB_ID) AS TOTAL_JOBS,
 10      COUNT(DISTINCT apt_v_nbu_job.PARENT_NBU_JOB_ID) AS TOTAL_PARENT_JOBS,
 11      COUNT(DISTINCT apt_v_nbu_job.SUMMARY_STATUS) as TOTAL_SUMMMARY_STATUS,
 12      COUNT(DISTINCT apt_v_nbu_job.VENDOR_STATUS) as TOTAL_NBU_STATUS,
 13      apt_v_server.HOSTNAME AS CLIENT_SERVER,
 14      apt_v_server.IP_ADDRESS AS CLIENT_IPADDRESS,
 15      apt_v_server.OS_VERSION AS CLIENT_VERSION,
 16      apt_v_server.SERVER_TYPE AS CLIENT_TYPE,
 17      apt_v_server.MAKE AS CLIENT_MAKE,
 18          (SELECT apt_v_server.HOSTNAME FROM apt_v_server
 19              WHERE apt_v_nbu_job.SERVER_ID = apt_v_server.SERVER_ID) AS MASTER_SERVER,
 20          (SELECT apt_v_server.OS_VERSION FROM apt_v_server
 21              WHERE apt_v_nbu_job.SERVER_ID = apt_v_server.SERVER_ID) AS MASTER_VERSION
 22  FROM
 23    apt_v_nbu_job
 24  INNER JOIN
 25    apt_v_server
 26  ON
 27     apt_v_nbu_job.CLIENT_ID = apt_v_server.SERVER_ID
 28  WHERE
 29       (apt_v_nbu_job.FINISH_DATE BETWEEN TO_DATE('2006-11-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
 30                                      AND TO_DATE('2006-12-03 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
 31     AND (apt_v_server.HOSTNAME NOT IN('ALL','NULL'))
 32  GROUP BY                                                                                                           
 33     CLIENT_SERVER, CLIENT_IPADDRESS, CLIENT_VERSION, CLIENT_TYPE, CLIENT_MAKE,
 34*    MASTER_SERVER, MASTER_VERSION
                       *
ERROR at line 34:
ORA-00904: "MASTER_VERSION": invalid identifier

 

[Dagon]

You are trying to group by a derived column name (MASTER_VERSION), which is not allowed. The best way to rewrite your query would be to use an inline view (I've missed out a few of the summed columns to simplify it):

SELECT
    VENDOR_JOB_TYPE,
    START_DATE,
    FINISH_DATE,
    SUM(TOTAL_KBYTES),
    sum(TOTAL_FILES),
    SUM(TOTAL_TRYS),
    CLIENT_SERVER,
    CLIENT_IPADDRESS,
    CLIENT_VERSION,
    CLIENT_TYPE,
    CLIENT_MAKE,
    MASTER_SERVER,
    MASTER_VERSION
FROM
(
  SELECT
     apt_v_nbu_job.VENDOR_JOB_TYPE,
     apt_v_nbu_job.START_DATE,
     apt_v_nbu_job.FINISH_DATE,
     apt_v_nbu_job.KILOBYTESas TOTAL_KBYTES,
     apt_v_nbu_job.NBR_OF_FILES as TOTAL_FILES,
     apt_v_nbu_job.TRY_COUNT as TOTAL_TRYS,
     apt_v_server.HOSTNAME AS CLIENT_SERVER,
     apt_v_server.IP_ADDRESS AS CLIENT_IPADDRESS,
     apt_v_server.OS_VERSION AS CLIENT_VERSION,
     apt_v_server.SERVER_TYPE AS CLIENT_TYPE,
     apt_v_server.MAKE AS CLIENT_MAKE,
     (SELECT apt_v_server.HOSTNAME FROM apt_v_server
       WHERE apt_v_nbu_job.SERVER_ID =  
        apt_v_server.SERVER_ID) AS MASTER_SERVER,
      (SELECT apt_v_server.OS_VERSION FROM apt_v_server
        WHERE apt_v_nbu_job.SERVER_ID =  
          apt_v_server.SERVER_ID) AS MASTER_VERSION
    FROM
      apt_v_nbu_job
      INNER JOIN
      apt_v_server
      ON apt_v_nbu_job.CLIENT_ID = apt_v_server.SERVER_ID
     WHERE
     (apt_v_nbu_job.FINISH_DATE BETWEEN 
         TO_DATE('2006-11-27 00:00:00', 
                 'YYYY-MM-DD HH24:MI:SS') AND 
         TO_DATE('2006-12-03 23:59:59', 
                 'YYYY-MM-DD HH24:MI:SS'))
       AND (apt_v_server.HOSTNAME NOT IN('ALL','NULL'))
)
GROUP BY                
    VENDOR_JOB_TYPE,
    START_DATE,
    FINISH_DATE,
    CLIENT_SERVER,
    CLIENT_IPADDRESS,
    CLIENT_VERSION,
    CLIENT_TYPE,
    CLIENT_MAKE,
    MASTER_SERVER,
    MASTER_VERSION

 

[slaing]

Oracle just doesn't like using column aliases in GROUP BYs

Compare the following:

Doesn't Work

select object_type as myalias, count(*) 
from user_objects
group by myalias

and

Works

select object_type as myalias, count(*) 
from user_objects
group by object_type

Steve

 

[slaing]

Rats !!!
Typing too slow again

I guess your solution will be of more immediate, practical use to the OP, Dagon.

 

[ljsmith91]

Thanks for the education....much appreciated. -ljs