ORA-00904 ERROR

[LaurenM]

I need some clarification on the ORA-00904 error documentation. I have a column that is a case stmt, and I'm trying to use that column in the WHERE clause of my sql.
Code:
SELECT
.
.
.
CASE
WHEN YTD < YMPE THEN 100
WHEN YTD > YMPE THEN 200
ELSE 0
END AS CORRECTED_EARNINGS
.
.
.
WHERE
.
.
.
AND CORRECTED_EARNINGS > 100
End of Code
I get a 904 error in the WHERE clause stating that CORRECTED_EARNINGS is an invalid identifier.
Some help would be greatly appreicated.

Lauren McDonald

 

[SantaMufasa]

Lauren,

Unfortunately, in the Oracle SQL World, the only place that you can refer to a column alias (e.g., "CORRECTED_EARNINGS") within the same SELECT statement is in an ORDER BY clause.

But, (lucky you), there is a workaround that I use frequently, to great advantage: I place (and alias) any long-winded expression (such as your CASE statement) within an in-line view. Here is an example, using your code:

Select SELECT [b]CORRECTED_EARNINGS[/b]
.
.
.
[b]from (SELECT [/b] <-- In-Line view definition
CASE
WHEN YTD < YMPE THEN 100
WHEN YTD > YMPE THEN 200
ELSE 0
END AS CORRECTED_EARNINGS
[b]from <your original table> <optional alias>)[/b] <- end of in-line VIEW
.
.
.
WHERE
.
.
.
AND CORRECTED_EARNINGS > 100

This will work for you, where your original code won't.

Let us know how you like it, Lauren.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LaurenM]

Thanks!

Lauren

 

[djbjr]

I ran into a bug when running in parallel while using a similar in line view. Be careful. You could always turn that into a DECODE in your where clause. Here are details of the bug. Have you run into this at all Dave?

Bug 4544805 ORA-918 from PQ slave
This note gives a brief overview of bug 4544805.

Affects:
Product (Component)
Oracle Server (Rdbms)

Range of versions believed to be affected
Versions < 10.1.0.2

Versions confirmed as being affected
9.2.0.6

Platforms affected
Generic (all / most platforms affected)


Fixed:
This issue is fixed in
9.2.0.8 (Server Patch Set)


Symptoms: Related To:

Error May Occur

ORA-918
Parallel Query (PQO)

Description
PQ slaves can fail with ORA-918 while parsing in slaves.


Workaround:
Prevent view merging.

 

[SantaMufasa]

DJB,

Sorry...I'm not familiar with that issue.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]