4 Replies - 488 Views - Last Post: 05 September 2017 - 07:10 AM Rate Topic: -----

#1 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

[SELECT - 0 row(s), 0.009 secs] [Error Code: 904, SQL State: 42000]

Posted 01 September 2017 - 11:22 AM

14:17:13 [SELECT - 0 row(s), 0.009 secs] [Error Code: 904, SQL State: 42000] ORA-00904: "newColName": invalid identifier

select
(case when num1 is not null THEN (num2 + num3) else (num4 + num5) end) as newColName
from testTable
where newColName > 30



using oracle

This post has been edited by ikhlas06: 01 September 2017 - 11:29 AM

Is This A Good Question/Topic? 0
  • +

Replies To: [SELECT - 0 row(s), 0.009 secs] [Error Code: 904, SQL State: 42000]

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13488
  • View blog
  • Posts: 53,864
  • Joined: 12-June 08

Re: [SELECT - 0 row(s), 0.009 secs] [Error Code: 904, SQL State: 42000]

Posted 01 September 2017 - 11:28 AM

What DB is this?

(typically case when's need an "END" in there)
Was This Post Helpful? 1
  • +
  • -

#3 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 4135
  • View blog
  • Posts: 13,056
  • Joined: 08-June 10

Re: [SELECT - 0 row(s), 0.009 secs] [Error Code: 904, SQL State: 42000]

Posted 02 September 2017 - 03:22 AM

SELECT aliases are usually not accessible in WHERE clauses.
Was This Post Helpful? 1
  • +
  • -

#4 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

Re: [SELECT - 0 row(s), 0.009 secs] [Error Code: 904, SQL State: 42000]

Posted 05 September 2017 - 06:52 AM

got it. if any one needs a solution, this will work for you:

select newColName
from testTable t
left join
        (
        SELECT DISTINCT t.id, 
        (case when num1 is not null THEN (num2 + num3) else (num4 + num5) end) as newColName
        from testTable t 
        )combine on t.id = combine.id 
where newColName > 30


Was This Post Helpful? 0
  • +
  • -

#5 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 4135
  • View blog
  • Posts: 13,056
  • Joined: 08-June 10

Re: [SELECT - 0 row(s), 0.009 secs] [Error Code: 904, SQL State: 42000]

Posted 05 September 2017 - 07:10 AM

why not using the HAVING clause for that?
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1