Substring a result and renaming it

oracleoracle-12csubstring

I wrote the below query which works just fine.
The PARAM_ID has a CLC_Q<number> form.

SELECT * FROM DB.TABLE 
    WHERE PARAM_ID LIKE 'CLC_Q%' 
    AND SUBSTR(PARAM_ID,6) BETWEEN 35 AND 45 
    ORDER BY SUBSTR(PARAM_ID,6) ASC;

It might be a logical mistake , but I can't do the below,

SELECT SUBSTR(PARAM_ID,6) PAR_ID FROM DB.TABLE 
    WHERE PARAM_ID LIKE 'CLC_Q%' 
    AND PAR_ID BETWEEN 35 AND 45 
    ORDER BY PAR_ID ASC;

Although it's a different question, I can't also do this,

SELECT SUBSTR(PARAM_ID,6),* FROM DB.TABLE -- The change is in this line
    WHERE PARAM_ID LIKE 'CLC_Q%' 
    AND SUBSTR(PARAM_ID,6) BETWEEN 35 AND 45 
    ORDER BY SUBSTR(PARAM_ID,6) ASC;

Version

Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production

Any further suggestions on how to improve my query would be much appreciated.

Best Answer

Using a CTE will do what you want:

WITH CTE AS
(
    SELECT SUBSTR(PARAM_ID,6) AS PAR_ID, T.* FROM DB.TABLE T
)
SELECT *
FROM CTE
WHERE PARAM_ID LIKE 'CLC_Q%' 
AND PAR_ID BETWEEN 35 AND 45 
ORDER BY PAR_ID ASC;

or simply using table_alias.*, instead of *:

SELECT SUBSTR(PARAM_ID,6) AS PAR_ID, T.* FROM DB.TABLE T
    WHERE PARAM_ID LIKE 'CLC_Q%' 
    AND SUBSTR(PARAM_ID,6) BETWEEN 35 AND 45 
    ORDER BY PAR_ID ASC;
Related Question