CODEWITHSUNDEEP
sqloracle-database
peter
peter

Reputation: 8652

How to use alias column name in oracle query

I have a two table one is Mn_Fdd_Cong and another is test_t .Here i have to split a column value in table Mn_Fdd_Cong based on character underscore and take first part and join with second table. But in my query alias is not working

select regexp_substr(a.Ids, '[^_]+', 1, 1) as "Id" ,a.*,b.*
from Mn_Fdd_Cong a 
left join test_t b on a.Id = b.Site_Id where b.G_VAL='U'

Upvotes: 0

Views: 2665

Answers (2)

Justin Cave
Justin Cave

Reputation: 231651

You can't use an alias you define in your select in the join (logically, the join happens before the select). You could duplicate the calculation

select regexp_substr(a.Ids, '[^_]+', 1, 1) as "Id" ,a.*,b.*
from Mn_Fdd_Cong a 
left join test_t b on regexp_substr(a.Ids, '[^_]+', 1, 1) = b.Site_Id 
where b.G_VAL='U'

Or you could nest the calculation. Personally, I'd put the mn_fdd_cong manipulation into a common table expression (CTE). Note that if you put an identifier like Id in double-quotes, you're creating a case-sensitive identifier so subsequent references to it would need to use the same case-sensitive identifier and would need to use the double quotes.

with new_a as (
  select regexp_substr(a.Ids, '[^_]+', 1, 1) as "Id" ,a.*
    from Mn_Fdd_Cong a 
)
select a.*, b.*
  from new_a a
       join test_t b on a."Id" = b.Site_Id 
 where b.G_VAL='U'

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

You can't use it that way; either extract "new" ID in a subquery (or a CTE) and then re-use it in "main" query, or - simpler - use it directly in JOIN operation.

I used simple substr + instr combination instead of regular expressions (performs better on large data sets).

select a.*, b.*
from md_fdd_cong a join test_t b on b.site_id = substr(a.ids, 1, instr(a.ids, '_') - 1)
where b.g_val = 'U'

Here's the 1st option I suggested (a CTE):

select x.*, b.*
from (select a.*,
             substr(a.ids, 1, instr(a.ids, '_') - 1) id  --> this is ID ...
      from md_fdd_cong a
     ) x
     join test_t b on b.site_id = x.id                   --> ... which is then used here (as "x.id")
where b.g_val = 'U';

Upvotes: 2

Related Questions