adding Aliases values in sql

I am trying to add Alias values, it allows me to add them up in sub query but as soon as I put its name in outer query it throws error.
 I am trying to add up values in aliases across the rows , its not allowing, don't know why
 Wrong_Name+MISSING_REGION as Total_Errors

 I would appreciate if you could provide some advice. Thanks!


 My query is below:

 select enitity_name, guts_desc, region,
 case
 when ( REGEXP_match(enitity_name, r'^Robo' ))
 then 0
 when ( REGEXP_match(enitity_name, r'^GUTS' ))
 then 0
 when ( REGEXP_match(enitity_name, r'^IC' ))
 then 0
 else 1
 end as Wrong_Name,

 case
 when REGEXP_match(guts_desc, '[[:alpha:]]')
 --LIKE '%[^0-9]%'
 then 1 ELSE 0
 END AS GUTS_ID_INCORRECT,

 case
 when REGEXP_match(guts_desc, '[[:alpha:]]')
 --then (REGEXP_replace(guts_desc, '[[:alpha:]]', '[^[:digit:]]' ))
 then (REGEXP_replace(guts_desc, '[^0-9 ]', '' ))
 else guts_desc
 end as Guts_Norm,
 guts_ref,
 case
 when guts_ref is null
 then 'MISSING' ELSE 'POPULATED'
 END AS GUTS_REFERENCE,

 case
 when guts_desc is null
 then 'MISSING' ELSE 'POPULATED'
 END AS GUTS_DESCRIPTION,

 MISSING_REGION_VENDOR,
 case
 when MISSING_REGION_VENDOR is null
 then '1' ELSE '0'
 END AS MISSING_REGION,
 --as soon as put this column name Total_Errors I get error saying
 --missing region and Wrong_name not found.
 Total_Errors,

 from
 (SELECT region, creator, enitity_name,entity_status,guts_desc,guts_ref,case when region is null
 then 'MISSING' ELSE 'POPULATED'
 END AS MISSING_REGION_VENDOR,
 Wrong_Name+MISSING_REGION as Total_Errors

 From XYZ);
Tulip_23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
The execution sequence is:

From
Select
Where
Order By.

So, have

Wrong_Name+MISSING_REGION as Total_Errors

in Select clause and move its related case/end into From Clause.

 select enitity_name, guts_desc, region, 
 case 
 when REGEXP_match(guts_desc, '[[:alpha:]]') 
 --LIKE '%[^0-9]%' 
 then 1 ELSE 0 
 END AS GUTS_ID_INCORRECT, 

 case 
 when REGEXP_match(guts_desc, '[[:alpha:]]') 
 --then (REGEXP_replace(guts_desc, '[[:alpha:]]', '[^[:digit:]]' )) 
 then (REGEXP_replace(guts_desc, '[^0-9 ]', '' )) 
 else guts_desc 
 end as Guts_Norm, 
 guts_ref,
  
 case 
 when guts_ref is null 
 then 'MISSING' ELSE 'POPULATED' 
 END AS GUTS_REFERENCE, 

 case 
 when guts_desc is null 
 then 'MISSING' ELSE 'POPULATED' 
 END AS GUTS_DESCRIPTION, 

 MISSING_REGION_VENDOR, 
 
 Wrong_Name+MISSING_REGION as Total_Errors, 
 --as soon as put this column name Total_Errors I get error saying 
 --missing region and Wrong_name not found. 
 --Total_Errors

 from 
 (SELECT region, creator, enitity_name,entity_status,guts_desc,guts_ref,case when region is null 
 then 'MISSING' ELSE 'POPULATED' 
 END AS MISSING_REGION_VENDOR, 
 case 
 when ( REGEXP_match(enitity_name, r'^Robo' )) 
 then 0 
 when ( REGEXP_match(enitity_name, r'^GUTS' )) 
 then 0 
 when ( REGEXP_match(enitity_name, r'^IC' )) 
 then 0 
 else 1 
 end as Wrong_Name, 
 case 
 when MISSING_REGION_VENDOR is null 
 then '1' ELSE '0' 
 END AS MISSING_REGION 

 From XYZ);

Open in new window


Mike
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

I think the problem is Total_Errors,  comma after the last column Total_Errors.

And Table Alias name is also missing

select * from

(select .....) tbl
0
Mike EghtebasDatabase and Application DeveloperCommented:
Correction...

From
Where
Select
Order By.

even after correcting some other syntax error, there is no way this query will work because Select clause unlike MS Access, doesn't allow all-at-once calculation. meaning an alias used in select clause has to be calculated before hand.

So, what is processed prior yo Select clause is from clause,
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

awking00Commented:
What database management system are you actually using?
0
Tulip_23Author Commented:
I am using BigQuery.
Thanks eghtebas
but the Problem is Missing_Region is dependent on Missing_Region_Vendor and even if i move it to end of SubQuery,
It throws error saying Missing_Region_Vendor not found.
It is tricky situation.

Any ideas??
0
Mike EghtebasDatabase and Application DeveloperCommented:
What you have now is one derived table like

Select A, B + 1 As B2, AA ...
From    
(
Select A, B, A + B As AA, ...    -- (what you have inside () is a derived table. A + B As AA will error
From Table1) As t   -- <-- btw, this alias t is not necessary

One option is to have nested derived table

Select A, B + 1 As B2, AA, C2 ... -- Order of execution is 3
From     -- outer derived table starting
(
Select A, B, AA, Case AA Then 'CC' ... End as C2, ...       -- here AA will be ready to be used in its outer portion.
                                                                                               -- Order of execution is 2
From    -- inner derived table starting
(
Select A, B, A + B As AA      -- Order of execution is 1
From Table1
)    -- inner derived table closing
)    -- outer derived table closing

This could be done also using multiple CTEs which is more common way of doing this. For sake of gaining confidence in the process, you way want to try the derived table method first. The CTE solution will be similar, I think, to another question you accepted from me lately.

Mike

This post has been modified...
0
Mike EghtebasDatabase and Application DeveloperCommented:
See http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28674561.html

Where order of execution is as follows:

1. CTE1,
2. CTE2,
3. The outer Query
0
Mike EghtebasDatabase and Application DeveloperCommented:
re:> From Table1) As t   -- <-- btw, this alias t is not necessary

I was wrong. we need to have alias with a derived table. Vikas Garg is correct on this aspect.

Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.