[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

What is " INNERALIAS " in SQL ?

I'm taking over a former developer's code, and it has some SQL similar to this which queries a SQL Server 2005 DB.

------------

SELECT       EmployeeID, Loc, DeptID, JobCode, ReportsID                                    
FROM        JOBS                                                                              
WHERE      EffectiveSequence =
                  (              
                  SELECT      MAX(EffectiveSequence)                        
                  FROM         JOBS INNERALIAS                
                  WHERE       EmployeeID  = JOBS.EmployeeID                
                  AND            EffectiveDate   = JOBS.EffectiveDate              
                  )                                                                                        
AND      EffectiveDate =  
                  (        
                  SELECT      MAX(EffectiveDate)
                  FROM         JOBS INNERALIAS
                  WHERE       EmployeeID =  JOBS.EmployeeID
                  AND             EffectiveDate  <= convert(datetime, getdate())
                  )                                                                      
AND      EmpStatus IN ('X1', 'X2', 'X3')                                                        
 
----------

I've never seen the INNERALIAS command before; and I can't find much on it.
I think that the pervious developer had the SQL code generated by a Report Smith application and then used it on an Oracle machine, and now we need to use it on a SQL Server 2005 machine.

The code seems to run fine on SQL Server 2005, but I have some concerns:
1. Is this a command that T-SQL will continue to permit, or might it become depracated?
2. Is there a better way to write that SQL (best practices or better practices)?
3. Is it part of standard SQL, or is it some propietary command that other databases happen to accept?
4. What is INNERALIAS? I mean, what is it formally supposed to do? I can see what it's doing from the above code, but can someone please provide a detailed general explanation?

Yes, I said "detailed general." Sorry.

Thanks in advance.


0
XTO
Asked:
XTO
  • 5
  • 4
  • 4
3 Solutions
 
käµfm³d 👽Commented:
I think that is just what the former developer called the JOBS table in his subquery. He used "INNER" to signify the fact that the alias was referring to the JOBS table in the subquery.
0
 
käµfm³d 👽Commented:
That is not to say that "INNER" is a keyword. I believe it was just a visual cue to the reader.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
its  corrSELECT       EmployeeID, Loc, DeptID, JobCode, ReportsID                                    
FROM        JOBS                                                                              
WHERE      EffectiveSequence =
                  (              
                  SELECT      MAX(j.EffectiveSequence)                        
                  FROM         JOBS j                
                  WHERE       j.EmployeeID  = JOBS.EmployeeID                
                  AND            j.EffectiveDate   = JOBS.EffectiveDate              
                  )                                                                                        
AND      EffectiveDate =  
                  (        
                  SELECT      MAX(j.EffectiveDate)
                  FROM        JOBS j
                  WHERE       j.EmployeeID =  JOBS.EmployeeID
                  AND             j.EffectiveDate  <= convert(datetime, getdate())
                  )                                                                      
AND      EmpStatus IN ('X1', 'X2', 'X3')                                                        

--try this


SELECT a.EmployeeID, Loc, DeptID, JobCode, ReportsID
FROM Jobs a
INNER JOIN (
SELECT      j.EmployeeID, MAX(j.EffectiveSequence) EffectiveSequence  , MAX(j.EffectiveDate) EffectiveDate                
FROM         JOBS j                
WHERE  j.EffectiveDate  <= convert(datetime, getdate())
AND      EmpStatus IN ('X1', 'X2', 'X3')
) B
on a.EmployeeID = b.EmployeeID and a.EffectiveSequence = b.EffectiveSequence and a.EffectiveDate = b.EffectiveDate


>INNERALIAS
there is no such thing in sql server, the closest concept is the Correlated query
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
XTOAuthor Commented:
Thanks for the quick responses.

The thing is, when I run the query without it I get zero results or just a few results.
However, if I run the query with the INNERALIAS word, I get over 3,000 records.
So, the INNERALIAS word is definitely doing something of major importance.

Even though it is not officially a T-SQL word, the SQL Server compiler is definitely recognizing it and altering the results of the query dramatically with it.

I'll try some of the code snippets above.

Any other ideas on what it is?

By the way, the line:
EffectiveDate  <= convert(datetime, getdate())
is actually
EffectiveDate  <= convert(datetime, @theTime)
It comes into the stored procedure as a parameter.

thanks in advance

0
 
käµfm³d 👽Commented:
As should be expected. If you take your query

SELECT       EmployeeID, Loc, DeptID, JobCode, ReportsID                                    
FROM        JOBS                                                                              
WHERE      EffectiveSequence =
                  (              
                  SELECT      MAX(EffectiveSequence)                        
                  FROM         JOBS INNERALIAS                
                  WHERE       EmployeeID  = JOBS.EmployeeID                
                  AND            EffectiveDate   = JOBS.EffectiveDate              
                  )                                                                                        
AND      EffectiveDate =  
                  (        
                  SELECT      MAX(EffectiveDate)
                  FROM         JOBS INNERALIAS
                  WHERE       EmployeeID =  JOBS.EmployeeID
                  AND             EffectiveDate  <= convert(datetime, getdate())
                  )                                                                      
AND      EmpStatus IN ('X1', 'X2', 'X3')

and remove "INNERALIAS", then you are telling the subquery to use the JOBS.EmployeeID from the JOBS table that is referenced in the subquery and not the one that is referenced in the outer query. In effect, removing "INNERALIAS" turns your subquery into

SELECT      MAX(EffectiveSequence)                        
                  FROM         JOBS
                  WHERE       EmployeeID  = EmployeeID                
                  AND            EffectiveDate   = EffectiveDate

which will return the maximum EffectiveSequence stored in the database, and not the maximum for the particular Id you are looking for. Your query would only return data when the Id in question had an EffectiveSequence that was the maximum for the entire table.
0
 
käµfm³d 👽Commented:
>> which will return the maximum EffectiveSequence stored in the database,

which will return the maximum EffectiveSequence stored in the table,
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>Even though it is not officially a T-SQL word, the SQL Server compiler is definitely recognizing it and altering the results of the query dramatically with it.
its treating the "INNERALIAS" as an alias , it will excute even if you change that to any string other than the keywords
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>AND             EffectiveDate  <= convert(datetime, getdate())
you can replace this line with
AND             EffectiveDate  <=  getdate()
0
 
XTOAuthor Commented:
I haven't had a chance to study all the responses yet, but when I tried this code from aneeshattingal:

SELECT a.EmployeeID, Loc, DeptID, JobCode, ReportsID
FROM Jobs a
INNER JOIN (
SELECT      j.EmployeeID, MAX(j.EffectiveSequence) EffectiveSequence  , MAX(j.EffectiveDate) EffectiveDate                
FROM         JOBS j                
WHERE  j.EffectiveDate  <= convert(datetime, getdate())
AND      EmpStatus IN ('X1', 'X2', 'X3')
) B
on a.EmployeeID = b.EmployeeID and a.EffectiveSequence = b.EffectiveSequence and a.EffectiveDate = b.EffectiveDate

I get the following error message from Sql Server:

Msg 8120, Level 16, State 1, Line 2
Column 'Jobs.EmployeeID' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
0
 
XTOAuthor Commented:
aneeshattingal:
its treating the "INNERALIAS" as an alias , it will excute even if you change that to any string other than the keywords

Oh! That makes sense.
Thank you very much.
------------

kaufmed
and remove "INNERALIAS", then you are telling the subquery to use the JOBS.EmployeeID from the JOBS table that is referenced in the subquery and not the one that is referenced in the outer query.

Oh, yes that makes sense also. I see what you're saying.
Thank you very much.

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
try thuis

SELECT a.EmployeeID, Loc, DeptID, JobCode, ReportsID
FROM Jobs a
INNER JOIN (
SELECT      j.EmployeeID, MAX(j.EffectiveSequence) EffectiveSequence  , MAX(j.EffectiveDate) EffectiveDate                
FROM         JOBS j                
WHERE  j.EffectiveDate  <=getdate()
AND      EmpStatus IN ('X1', 'X2', 'X3')
Group by j.EmployeeID
) B
on a.EmployeeID = b.EmployeeID and a.EffectiveSequence = b.EffectiveSequence and a.EffectiveDate = b.EffectiveDate
0
 
XTOAuthor Commented:
aneeshattingal:

Yes, that works.

Thanks to both of you for your very fast assistance.
0
 
XTOAuthor Commented:
Thanks friends
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now