Selected column alias name in where clause

Question

How to put alias column name(CO_NUMBER) in where clause. I tried with alias name but its not working, do i need to put whole condition mention in column CO_NUMBER ?

Select  A_TBL.ID, A_TBL.EmpId,
        B_TBL.Tans, 
        DBMS_LOB.SubStr( TXT, 
            DBMS_LOB.InStr(TXT, '"', DBMS_LOB.InStr(TXT, '"CompanyNumber":"') + Length('"CompanyNumber":"'), 1  ) - (DBMS_LOB.InStr(TXT, '"CompanyNumber":"') + Length('"CompanyNumber":"')),
            DBMS_LOB.InStr(TXT, '"CompanyNumber":"') + Length('"CompanyNumber":"')
          ) "COMPANY NUMBER"
From
    A_TBL join B_Tbl on A_TBL.id = B_TBL.id  
Where "COMPANY NUMBER" = 'A1'

The Impaler · Answer 1 · 2023-03-20T15:27:14.647

1

To use a named expression in a WHERE clause you need to officially generate it in a subquery. For example:

select *
from ( -- subquery starts here
  select
    ID,
    EmpId,
    DBMS_LOB.SubStr( TXT, 
      DBMS_LOB.InStr(TXT, '"', 
      DBMS_LOB.InStr(TXT, '"CompanyNumber":"') + Length('"CompanyNumber":"'), 
      1  ) - (DBMS_LOB.InStr(TXT, '"CompanyNumber":"') 
      + Length('"CompanyNumber":"')),
      DBMS_LOB.InStr(TXT, '"CompanyNumber":"') +
      Length('"CompanyNumber":"')
    ) as CO_NUMBER
  from A_TBL
) x -- subquery ends here
where CO_NUMBER = 'A1'

Edit -- Adding an alias with extra characters.

You can enclose the alias with double quotes to use it anywhere an alias is valid. For example:

select *
from (
  select a, b, a + b as "A and B" from t
) x
where "A and B" > 6

edited Mar 20 '23 at 15:27

answered Mar 20 '23 at 13:15

The Impaler

45,731
9
39
76

1

I'd rather use common-table-expression, that with tb2 as (select ID, EmpId, DBMS_LOB.SubS... ) – Bogdan Dincescu Mar 20 '23 at 14:01
if alias is "Company Number" then how can i mention in where clause? – VimalSingh Mar 20 '23 at 14:25
@TheImpaler I am getting below error [Error] Execution (35: 30): ORA-00904: "COMPANY NUMBER": invalid identifier – VimalSingh Mar 20 '23 at 15:39
Works well for me in Oracle. See [fiddle](https://dbfiddle.uk/Ty8An8Sl). – The Impaler Mar 20 '23 at 16:36

Selected column alias name in where clause

1 Answers1