ALIAS in where clause not working

Question

I have column RESPONSE_XML and REQUEST_XML which consist of large string. I have used substring function for this large string in order to fetch the SUBSCRIPTION_ID from RESPONSE_XML and orderType from REQUEST_XML. The query is working fine. But now i want to put condition for this query such that it should only return SUBSCRIPTION_ID where orderType='NEW'. I store the result of both the Substring into alias and used these alias in where condition. But its not working and giving error as ORA-01722 Invalid numnber. Here is my query :

    SELECT REPLACE(REPLACE(REGEXP_SUBSTR(RESPONSE_XML, '<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>\d+</ax2130:id>'), 
    '<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>', ''), '</ax2130:id>', '') 
    AS SUBSCRIPTION_ID , 
    REPLACE(REPLACE(REGEXP_SUBSTR(REQUEST_XML, '<ns7:orderType>\d+</ns7:orderType>'), '<ns7:orderType>', ''), '</ns7:orderType>', '') 
    AS order_type
    FROM
    SOAP_MONITORING 
    where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder' 
and order_type='NEW' order by CREATE_DATE desc

I also tried a query in this way but result an error i.e ORA-00932 inconsistent datatype at line 10

SELECT REPLACE(REPLACE(REGEXP_SUBSTR(RESPONSE_XML, '<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>\d+</ax2130:id>'), 
'<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>', ''), '</ax2130:id>', '') 
AS SUBSCRIPTION_ID from SOAP_MONITORING 
where 
REQUEST_XML
in
(
REPLACE(REPLACE(REGEXP_SUBSTR(REQUEST_XML, '<ns7:orderType>\d+</ns7:orderType>'), '<ns7:orderType>', ''), '</ns7:orderType>', '') 
)
and WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder' and REQUEST_XML='NEW'

You can't use alases in the `where` clause. Use a subquery or CTE. — Gordon Linoff, Feb 02 '15 at 14:53
Because the result-set rows are defined (logically executed) before the column alias is defined. — jarlh, Feb 02 '15 at 14:55
Please check in the question another query i wrote but result an error — , Feb 02 '15 at 15:01

score 0 · Accepted Answer · answered Feb 02 '15 at 15:38

In order to do predicates (WHERE) on the column aliases, you can use inline view (also called subquery, though subquery is actually more generally used for different things) or a common table expression (also called CTE or "with" clause).

Inline view you can do something like this:

select iw.order_type
from (
   SELECT REPLACE(REPLACE(REGEXP_SUBSTR(RESPONSE_XML, '<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>\d+</ax2130:id>'), 
   '<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>', ''), '</ax2130:id>', '') 
   AS SUBSCRIPTION_ID , 
   REPLACE(REPLACE(REGEXP_SUBSTR(REQUEST_XML, '<ns7:orderType>\d+</ns7:orderType>'), '<ns7:orderType>', ''), '</ns7:orderType>', '') 
   AS order_type,
   CREATE_DATE
   FROM
   SOAP_MONITORING 
   where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder' 
) iw
where iw.order_type='NEW'
order by iw.CREATE_DATE desc

Common table expression you can do something like this:

with cte as (
   SELECT REPLACE(REPLACE(REGEXP_SUBSTR(RESPONSE_XML, '<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>\d+</ax2130:id>'), 
   '<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>', ''), '</ax2130:id>', '') 
   AS SUBSCRIPTION_ID , 
   REPLACE(REPLACE(REGEXP_SUBSTR(REQUEST_XML, '<ns7:orderType>\d+</ns7:orderType>'), '<ns7:orderType>', ''), '</ns7:orderType>', '') 
   AS order_type,
   CREATE_DATE
   FROM
   SOAP_MONITORING 
   where WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder' 
)
select cte.order_type
from cte
where cte.order_type='NEW'
order by cte.CREATE_DATE desc

Hello Kim thanks for your reply. I think we are almost there. I tried your first query and its giving the above error at line WHERE cte.order_type='NEW' ORA-00932: inconsistent datatypes: expected - got CLOB. The REQUEST_XML column from which we want to extract the result is CLOB datatype — , Feb 02 '15 at 15:40
That hasn't really got anything to do with column aliases, it is rather that `cte.order_type='new'` compares two different datatypes. You might use a substr or a cast inside the inline view to make your CLOB become a VARCHAR2. If you can't make that work, I suggest asking a new question about that, as it would be confusing to mix that with a question about predicates on column aliases. — Kim Berg Hansen, Feb 02 '15 at 15:48
Having said that, I'd also recommend you read about the XML functionality within Oracle database (XMLExtract, XMLTable, XMLExists, etc.) rather than parsing your XML with regular expressions. Depending on amount of data in your table, you might need an XMLIndex on you REQUEST_XML column in order to make this perform well. — Kim Berg Hansen, Feb 02 '15 at 15:51
Hello Kim i used XMLEXTRACT previously to extratct the value from xml but i had some issue with it using database links. It will be really helpful if you can tell in your answer how to cast the CLOB to VARCHAR. I have to wait for 1 hour as per the rule of stackoverflow in order to ask another question. — , Feb 02 '15 at 15:53

ALIAS in where clause not working

1 Answers1