Invalid Identifier trying to use SUBSTR 2

[ljsmith91]

I am fairly new to Oracle SQL. I can't seem to find the reason for the ORA-00904 message I get. I want to Substring on the FINISH_DATE field so that I get ONLY the date and not the time and then GROUP BY on that DATE but this error won't go away for me. Can anyone help? Thanks in advance. Here is the code as it is and error below it:

SELECT
VENDOR_JOB_TYPE,
CLIENT_SERVER,
MASTER_SERVER,
SUM(KILOBYTES) as TOTAL_KBYTES,
SUM(NBR_OF_FILES) as TOTAL_FILES,
SUM(TRY_COUNT) as TOTAL_TRYS,
COUNT(STREAM_INDEX) as TOTAL_STREAMS,
COUNT(DISTINCT NBU_JOB_ID) AS TOTAL_JOBS,
COUNT(DISTINCT PARENT_NBU_JOB_ID) AS TOTAL_PARENT_JOBS,
COUNT(DISTINCT SUMMARY_STATUS) as TOTAL_SUMMMARY_STATUS,
SUBSTR(apt_v_nbu_job.FINISH_DATE,1,10) AS FINISH_DATE,
Sum(Case When SUMMARY_STATUS = 0 Then 1 Else 0 End) As TotalStatusSuccess,
Sum(Case When SUMMARY_STATUS = 1 Then 1 Else 0 End) As TotalStatusWarns,
Sum(Case When SUMMARY_STATUS = 2 Then 1 Else 0 End) As TotalStatusFails
FROM (
SELECT
apt_v_nbu_job.VENDOR_JOB_TYPE,
SUBSTR(apt_v_server.HOSTNAME,1,INSTR(apt_v_server.HOSTNAME||'.','.')-1) CLIENT_SERVER,
apt_v_nbu_job.KILOBYTES,
apt_v_nbu_job.NBR_OF_FILES,
apt_v_nbu_job.TRY_COUNT,
apt_v_nbu_job.STREAM_INDEX,
apt_v_nbu_job.NBU_JOB_ID,
apt_v_nbu_job.PARENT_NBU_JOB_ID,
apt_v_nbu_job.SUMMARY_STATUS,
(SELECT SUBSTR(apt_v_server.HOSTNAME,1,INSTR(apt_v_server.HOSTNAME||'.','.')-1)
FROM apt_v_server
WHERE apt_v_nbu_job.SERVER_ID = apt_v_server.SERVER_ID) AS MASTER_SERVER
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('2008-01-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_DATE('2008-01-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
AND ((apt_v_server.HOSTNAME NOT IN ('ALL','NULL')) OR
(apt_v_nbu_job.VENDOR_JOB_TYPE NOT IN ('')))
)
GROUP BY
FINISH_DATE,
VENDOR_JOB_TYPE,
CLIENT_SERVER
/

ERROR at line 12:
ORA-00904: "APT_V_NBU_JOB"."FINISH_DATE": invalid identifier

 

[jimirvine]

It is saying that you do not have a column called finish_date in the apt_v_nbu_job table.
By the way, use the trunc function rather than substr.

 

[SantaMufasa]

Actually, LJ, You probably do have a column named finish_date in the apt_v_nbu_job table, but since you are accessing that table via an in-line view (i.e., "...FROM <in-line view>...)", all qualifying table names that appear in an in-line view become no longer visible to the outer SELECT.

Therefore, I believe you can resolve your syntax error by simply removing the qualifying table reference. Specifically, change the error line to read:

...SUBSTR(FINISH_DATE,1,10) AS FINISH_DATE,...

Another alternative is to give an alias to the in-line view, then use the alias to refer to "FINISH_DATE":

SELECT
    VENDOR_JOB_TYPE,
    CLIENT_SERVER,
    MASTER_SERVER,
    SUM(KILOBYTES) as TOTAL_KBYTES,
    SUM(NBR_OF_FILES) as TOTAL_FILES,
    SUM(TRY_COUNT) as TOTAL_TRYS,
    COUNT(STREAM_INDEX) as TOTAL_STREAMS,
    COUNT(DISTINCT NBU_JOB_ID) AS TOTAL_JOBS,
    COUNT(DISTINCT PARENT_NBU_JOB_ID) AS TOTAL_PARENT_JOBS,
    COUNT(DISTINCT SUMMARY_STATUS) as TOTAL_SUMMMARY_STATUS,
    SUBSTR([B][I]xyz[/I][/B].FINISH_DATE,1,10) AS FINISH_DATE,
    Sum(Case When SUMMARY_STATUS = 0 Then 1 Else 0 End) As TotalStatusSuccess,
    Sum(Case When SUMMARY_STATUS = 1 Then 1 Else 0 End) As TotalStatusWarns,
    Sum(Case When SUMMARY_STATUS = 2 Then 1 Else 0 End) As TotalStatusFails
FROM (
    SELECT
        apt_v_nbu_job.VENDOR_JOB_TYPE,
        SUBSTR(apt_v_server.HOSTNAME,1,
               INSTR(apt_v_server.HOSTNAME||
                     '.','.')-1) CLIENT_SERVER,
        apt_v_nbu_job.KILOBYTES,
        apt_v_nbu_job.NBR_OF_FILES,
        apt_v_nbu_job.TRY_COUNT,
        apt_v_nbu_job.STREAM_INDEX,
        apt_v_nbu_job.NBU_JOB_ID,
        apt_v_nbu_job.PARENT_NBU_JOB_ID,
        apt_v_nbu_job.SUMMARY_STATUS,
        (SELECT SUBSTR(apt_v_server.HOSTNAME,1,
                INSTR(apt_v_server.HOSTNAME||'.','.')-1)
           FROM apt_v_server
          WHERE apt_v_nbu_job.SERVER_ID
                = apt_v_server.SERVER_ID) AS  MASTER_SERVER
      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('2008-01-31 00:00:00',
                                 'YYYY-MM-DD HH24:MI:SS')
                     AND TO_DATE('2008-01-31 23:59:59',
                                 'YYYY-MM-DD HH24:MI:SS'))
             AND ((apt_v_server.HOSTNAME NOT IN ('ALL','NULL')) OR
                  (apt_v_nbu_job.VENDOR_JOB_TYPE NOT IN ('')))
     ) [B][I]xyz[/I][/B]
GROUP BY FINISH_DATE, VENDOR_JOB_TYPE, CLIENT_SERVER
/
******************************************************************************

Let us know your findings/outcome.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ljsmith91]

Thanks for the replies. FINISH_DATE is indeed a valid date. However, the suggestions didnt work, either of them. Here is what I coded and the result. It now doesn't like the FINISH_DATE in the GROUP BY statement. Any ideas what my issue might be?

SELECT
VENDOR_JOB_TYPE,
CLIENT_SERVER,
MASTER_SERVER,
SUM(KILOBYTES) as TOTAL_KBYTES,
SUM(NBR_OF_FILES) as TOTAL_FILES,
SUM(TRY_COUNT) as TOTAL_TRYS,
COUNT(STREAM_INDEX) as TOTAL_STREAMS,
COUNT(DISTINCT NBU_JOB_ID) AS TOTAL_JOBS,
COUNT(DISTINCT PARENT_NBU_JOB_ID) AS TOTAL_PARENT_JOBS,
COUNT(DISTINCT SUMMARY_STATUS) as TOTAL_SUMMMARY_STATUS,
SUBSTR(xyz.FINISH_DATE,1,10) AS FINISH_DATE,
Sum(Case When SUMMARY_STATUS = 0 Then 1 Else 0 End) As TotalStatusSuccess,
Sum(Case When SUMMARY_STATUS = 1 Then 1 Else 0 End) As TotalStatusWarns,
Sum(Case When SUMMARY_STATUS = 2 Then 1 Else 0 End) As TotalStatusFails
FROM (
SELECT
apt_v_nbu_job.VENDOR_JOB_TYPE,
apt_v_nbu_job.KILOBYTES,
apt_v_nbu_job.NBR_OF_FILES,
apt_v_nbu_job.TRY_COUNT,
apt_v_nbu_job.STREAM_INDEX,
apt_v_nbu_job.NBU_JOB_ID,
apt_v_nbu_job.PARENT_NBU_JOB_ID,
apt_v_nbu_job.SUMMARY_STATUS,
(SELECT SUBSTR(apt_v_server.HOSTNAME,1,INSTR(apt_v_server.HOSTNAME||'.','.')-1)
FROM apt_v_server
WHERE apt_v_nbu_job.SERVER_ID = apt_v_server.SERVER_ID) AS MASTER_SERVER
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('2008-01-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_DATE('2008-01-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
AND ((apt_v_server.HOSTNAME NOT IN ('ALL','NULL')) OR
(apt_v_nbu_job.VENDOR_JOB_TYPE NOT IN ('')))
) xyz
GROUP BY
FINISH_DATE,
VENDOR_JOB_TYPE,
CLIENT_SERVER,
MASTER_SERVER

/
*
ERROR at line 49:
ORA-00904: "FINISH_DATE": invalid identifier

 

[SantaMufasa]

LJ,

Let's concentrate on ensuring that the in-line VIEW works properly. Do you get appropriate results from just the VIEW:

SELECT
    apt_v_nbu_job.VENDOR_JOB_TYPE,
    apt_v_nbu_job.KILOBYTES,
    apt_v_nbu_job.NBR_OF_FILES,
    apt_v_nbu_job.TRY_COUNT,
    apt_v_nbu_job.STREAM_INDEX,
    apt_v_nbu_job.NBU_JOB_ID,
    apt_v_nbu_job.PARENT_NBU_JOB_ID,
    apt_v_nbu_job.SUMMARY_STATUS,
        (SELECT SUBSTR(apt_v_server.HOSTNAME,1,INSTR(apt_v_server.HOSTNAME||'.','.')-1)
            FROM apt_v_server
            WHERE apt_v_nbu_job.SERVER_ID = apt_v_server.SERVER_ID) AS  MASTER_SERVER
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('2008-01-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
                                    AND TO_DATE('2008-01-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
   AND ((apt_v_server.HOSTNAME NOT IN ('ALL','NULL')) OR
         (apt_v_nbu_job.VENDOR_JOB_TYPE NOT IN ('')))
/

If so, whatever are the names of the output results, those are the names to which you should refer in your outer SELECT.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Also, when you receive an error message with a specific line number such as:

   *
ERROR at line 49:
ORA-00904: "FINISH_DATE": invalid identifier

...it is important to copy and paste the code from your SQL*Plus session that results from entering the SQL*Plus "LIST" command:

SQL> LIST

Running the "LIST" command prints our line numbers so that we can correlate your code to the error message. (With the code you listed above, I count only 45 lines of code, thus I cannot determine what Oracle/SQL*Plus believes is "Line 49". <smile>)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ljsmith91]

Mufasa,

Thanks for your replies. The Inner View works fine. This is a routine that has been in place for over a year. However, I need to update it and get the FINISH_DATE from the tables now too... so the only thing I am adding from the working routine is the SUBSTR line for FINISH_DATE and the same column name added in the GROUP BY Section. So ONLY these 2 lines added and all else is a working script. There were some comments in the script that I was removing that made the error line discrepancy. I reran without the comments and the error line changes and here it is...failing in the GROUP BY FINISH_DATE line. WHat do you think?

SQL> list
SQL>
1 SELECT
2 VENDOR_JOB_TYPE,
3 CLIENT_SERVER,
4 MASTER_SERVER,
5 SUM(KILOBYTES) as TOTAL_KBYTES,
6 SUM(NBR_OF_FILES) as TOTAL_FILES,
7 SUM(TRY_COUNT) as TOTAL_TRYS,
8 COUNT(STREAM_INDEX) as TOTAL_STREAMS,
9 COUNT(DISTINCT NBU_JOB_ID) AS TOTAL_JOBS,
10 COUNT(DISTINCT PARENT_NBU_JOB_ID) AS TOTAL_PARENT_JOBS,
11 COUNT(DISTINCT SUMMARY_STATUS) as TOTAL_SUMMMARY_STATUS,
12 SUBSTR(xyz.FINISH_DATE,1,10) AS FINISH_DATE,
13 Sum(Case When SUMMARY_STATUS = 0 Then 1 Else 0 End) As TotalStatusSuccess,
14 Sum(Case When SUMMARY_STATUS = 1 Then 1 Else 0 End) As TotalStatusWarns,
15 Sum(Case When SUMMARY_STATUS = 2 Then 1 Else 0 End) As TotalStatusFails
16 FROM (
17 SELECT
18 apt_v_nbu_job.VENDOR_JOB_TYPE,
19 SUBSTR(apt_v_server.HOSTNAME,1,INSTR(apt_v_server.HOSTNAME||'.','.')-1) CLIENT_SERVER,
20 apt_v_nbu_job.KILOBYTES,
21 apt_v_nbu_job.NBR_OF_FILES,
22 apt_v_nbu_job.TRY_COUNT,
23 apt_v_nbu_job.STREAM_INDEX,
24 apt_v_nbu_job.NBU_JOB_ID,
25 apt_v_nbu_job.PARENT_NBU_JOB_ID,
26 apt_v_nbu_job.SUMMARY_STATUS,
27 (SELECT SUBSTR(apt_v_server.HOSTNAME,1,INSTR(apt_v_server.HOSTNAME||'.','.')-1)
28 FROM apt_v_server
29 WHERE apt_v_nbu_job.SERVER_ID = apt_v_server.SERVER_ID) AS MASTER_SERVER
30 FROM
31 apt_v_nbu_job
32 INNER JOIN
33 apt_v_server
34 ON
35 apt_v_nbu_job.CLIENT_ID = apt_v_server.SERVER_ID
36 WHERE
37 (apt_v_nbu_job.FINISH_DATE BETWEEN TO_DATE('2008-01-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
38 AND TO_DATE('2008-01-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
39 AND ((apt_v_server.HOSTNAME NOT IN ('ALL','NULL')) OR
40 (apt_v_nbu_job.VENDOR_JOB_TYPE NOT IN ('')))
41 ) xyz
42 GROUP BY
43 FINISH_DATE,
44 VENDOR_JOB_TYPE,
45 CLIENT_SERVER,
46* MASTER_SERVER
FINISH_DATE,
*
ERROR at line 43:
ORA-00904: "FINISH_DATE": invalid identifier

 

[SantaMufasa]

If so, whatever are the names of the output results, those are the names to which you should refer in your outer SELECT.

The Inner View works fine.

When you say that it "works fine", do you see a column displayed whose column title is "FINISH_DATE"?...I can't imagine so. And if that column does not appear as an output column of your "working-fine" view, then you will recieve the "ORA-00904: "FINISH_DATE": invalid identifier" error.



Look at lines 16 through 41 in your code, above (your in-line VIEW). Nowhere in the SELECTed expressions does the expression, "FINISH_DATE" appear. If "FINISH_DATE" does not appear amongst those SELECTed expressions, then the outer SELECT cannot "see" FINISH_DATE.

So, the quickest, easiest solution (it seems to me) is to add "FINISH_DATE" to the SELECT-list of expressions in the inner in-line VIEW.

Let us know the outcome of that minor change.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ljsmith91]

Aha...I see your point. I had tried that earlier and it didn't work for me but I wasn't applying your ALIAS logic. It works great now.

I want to thank you for putting up with me. I appreciate the help as I clearly have much to learn but you showed me the way.

Enjoy your day and thanks so much. -LJS

 

[ChrisHunt]

Dave mentioned it in passing above, but this is likely to cause you some trouble:

SUBSTR(xyz.FINISH_DATE,1,10)

That will turn your date field into a string, and have nasty side-effects if you try to sort by the resulting value (it'll be in alphabetical order instead of date order).

If you're intention is to remove the time portion of the date, you should do it like this:

TRUNC(xyz.FINISH_DATE)

You should also probably also be grouping by the TRUNCed value instead of the original date.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[SantaMufasa]

Yes, and to emphasise Chris's assertion ("...grouping by the TRUNCed value instead of the original date"), if you group by the original date (instead of the TRUNCed DATE), it will "group" into a myriad of unwanted clumps broken down by minutes and seconds!...Not a good thing...not what you want.

The TRUNC function, by default, strips off the TIME component of a DATE, leaving just the Day/Month/Year component.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ljsmith91]

Thanks for that valuable input. I was seeing a problem with the Group and knew not why... however, I couldnt get TRUNC to work....the following was working great and fixed GROUP:

SUBSTR(TO_DATE(xyz.FINISH_DATE,'YYYY-MM-DD'),1,10),

This I coded in the SELECT and the GROUP statements. But when I code as suggested:

TRUNC(xyz.FINISH_DATE),

in both SELECT and GROUP, the field comes back NULL or empty. I have no idea why.

 

[SantaMufasa]

The puzzling behaviour that you cite, above, could be caused by your getting "tangled up in your knickers"...Hopefully, FINISH_DATE originates in your table as a DATE expression. But, clouding the issue is if you do a SUBSTR or a TO_CHAR on that expression and then give the resulting character expression an alias of FINISH_DATE, then it muddies things up terribly...The resulting character expression will no longer behave properly as a DATE (which would explain why TRUNC(xyz.FINISH_DATE) comes back as NULL).

The rule of thumb is to ensure that an expression is of datatype DATE when working on it with a DATE function (as you intended with the TRUNC function). Transform a DATE to a character expression only when you are ready to display it or if you are attempting to post-process/evaluate some DATE/TIME fragment such as a YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND separately from the rest of the DATE/TIME value.

If FINISH_DATE is originally a DATE expression (versus a character expression), then TRUNC should work properly against FINISH_DATE, stripping the TIME and yielding just the year, month, and day portions of the expression (and not simply NULL for a NOT NULL source value).

I hope you understand my explanation and that it doesn't confuse the issue further.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ljsmith91]

Mufasa,

I was a bit "tangled up". I looked at the source datatype and it is a DATE... when I attempted same TRUNC logic again, it worked fine. I really don't know why I thought it didn't work or what else I may have been doing to create the situation that gave me the poor results. However, I am happy you replied and I reviewed and retried because all is now as it should be. Thanks so much once again. -LJS