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:
or simply using
table_alias.*
, instead of*
: