Home » SQL & PL/SQL » SQL & PL/SQL » Using Fields Again in the Same Script
Using Fields Again in the Same Script [message #358814] Wed, 12 November 2008 10:06 Go to next message
Danzel
Messages: 2
Registered: November 2008
Junior Member
Hi,
Just wondering if anyone could tell me whether I can refer to a field (found in the select part) in the where clause?

Example:

At the moment I have:

SELECT field1, decode()
FROM table
WHERE decode() > 4 OR decode() < -4


I have left the decode function empty for now for keeping it simple for the example but it in fact takes a lot calculating (restricts performance)

So, I would rather the decode only be needed to perform once and then I could refer to this in the where clause:

e.g. (I know it's syntactically incorrect)

SELECT field1, decode() AS field2
FROM table
WHERE field2 > 4 OR field2 < -4


Thanks a lot in advance,

Danzel

[Updated on: Wed, 12 November 2008 10:07]

Report message to a moderator

Re: Using Fields Again in the Same Script [message #358818 is a reply to message #358814] Wed, 12 November 2008 10:14 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Column alias cannot be used in the where clause in the same level. Unfortunately you have to type the whole decode statement everytime.

Regards

Raj
Re: Using Fields Again in the Same Script [message #358825 is a reply to message #358818] Wed, 12 November 2008 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... or change the query or use a subquery.

Regards
Michel
Re: Using Fields Again in the Same Script [message #358826 is a reply to message #358818] Wed, 12 November 2008 10:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do something like this:
SELECT field_1,decode_field
FROM   (SELECT field_1, decode() as decode_field
        FROM   table)
WHERE  decode_field > 4

or
WITH src AS (SELECT field_1, decode() as decode_field
             FROM   table)
SELECT field_1,decode_field
FROM   src
WHERE  decode_field > 4;
Re: Using Fields Again in the Same Script [message #358841 is a reply to message #358814] Wed, 12 November 2008 11:17 Go to previous messageGo to next message
Danzel
Messages: 2
Registered: November 2008
Junior Member
Ahh thanks alot for the feedback everyone.
JRowbottom you've hit the nail on the head there I think. Thanks mate!!
Re: Using Fields Again in the Same Script [message #358843 is a reply to message #358814] Wed, 12 November 2008 11:29 Go to previous message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Column alias can be use in the order clause only in the same level.The column alias cannot be used in where,having,group clause.

Previous Topic: stored package
Next Topic: help with query (merged)
Goto Forum:
  


Current Time: Wed Dec 07 05:03:15 CST 2016

Total time taken to generate the page: 0.23092 seconds