XTO
asked on
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.
------------
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.
kaufmed
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.
That is not to say that "INNER" is a keyword. I believe it was just a visual cue to the reader.
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> which will return the maximum EffectiveSequence stored in the database,
which will return the maximum EffectiveSequence stored in the table,
which will return the maximum EffectiveSequence stored in the table,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>AND EffectiveDate <= convert(datetime, getdate())
you can replace this line with
AND EffectiveDate <= getdate()
you can replace this line with
AND EffectiveDate <= getdate()
ASKER
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.
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.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
aneeshattingal:
Yes, that works.
Thanks to both of you for your very fast assistance.
Yes, that works.
Thanks to both of you for your very fast assistance.
ASKER
Thanks friends