June 15, 2010 at 9:38 am
Hello Forum,
I have what I thing is a very basic Stored Proc question, i think. I am trying to do a join on the results from a stored proc.
Here is a little more specific information.
I have this Stored procedure in SQL 2005. - and I need to rewrite it to be compatible with SQL 2000 which does not support the With statement. any Ideas how to approach this? I was thinking of using a view, and doing a select and a join on the results, but I could really use useful suggestions from this forum. Thanks in advance,
Greg
WITH tAdjustmentEx
(Id, AdjustmentHeaderId, AdjustmentDetailId,
TypeCode, MeasurementId, Value, Row)
AS
(
SELECT
A.Id, D.AdjustmentHeaderId, A.AdjustmentDetailId,
D.TypeCode, A.MeasurementId, A.Value, A.Row
FROM tAdjustment AS A
INNER JOIN tAdjustmentDetail AS D ON
A.AdjustmentDetailId=D.Id AND D.AdjustmentHeaderId=@AdjustmentId
)
SELECT
I.Id AS InitId, I.Id AS DispId, I.AdjustmentHeaderId, I.AdjustmentDetailId, I.MeasurementId,
I.Value AS InitAmount, D.Value AS DispAmount
FROM tAdjustmentEx AS I
LEFT OUTER JOIN tAdjustmentEx AS D ON
I.AdjustmentHeaderId=D.AdjustmentHeaderId AND
I.MeasurementId=D.MeasurementId AND
D.TypeCode='D'
WHERE I.TypeCode IN ('I', 'S')
ORDER BY I.Row
June 15, 2010 at 10:50 am
The WITH statement is part of a CTE (common table expression).
A cte (at least the type you're using) is nothing but a subquery, just easier to read (for some of us...).
The SQL2000 compliant version would look like:
SELECT
I.Id AS InitId, I.Id AS DispId, I.AdjustmentHeaderId, I.AdjustmentDetailId, I.MeasurementId,
I.Value AS InitAmount, D.Value AS DispAmount
FROM
(
SELECT
A.Id, D.AdjustmentHeaderId, A.AdjustmentDetailId,
D.TypeCode, A.MeasurementId, A.Value, A.Row
FROM tAdjustment AS A
INNER JOIN tAdjustmentDetail AS D ON
A.AdjustmentDetailId=D.Id AND D.AdjustmentHeaderId=@AdjustmentId
)
tAdjustmentEx AS I
LEFT OUTER JOIN tAdjustmentEx AS D ON
I.AdjustmentHeaderId=D.AdjustmentHeaderId AND
I.MeasurementId=D.MeasurementId AND
D.TypeCode='D'
WHERE I.TypeCode IN ('I', 'S')
ORDER BY I.Row
Did you notice how the CTE part simply is moved to the FROM section of the query, even keeping the same name?
June 15, 2010 at 11:48 am
Oh, OK. I understand by looking at your code. Thank you so much for your help with this.
It was very helpful!:-D
June 15, 2010 at 1:51 pm
Having some difficulty figuring out how to address the alias portion of your answer. For example
In my problem I have:
Select i.field1, i.field2, i.field3
From
(Select Statement....) Alias? I
Where I.Field1 IN ('I', 'S,)
I am receiving an error when trying to set the Alias I
Do you know how I can resolve this? It is not letting me just Use parenthesis followed by Alias name. I am receiving an error. Can you help me understand why?
June 15, 2010 at 2:06 pm
My fault. Sorry....
I didn't realize the alias within your original query.
Change FROM (...) tAdjustmentEx AS I
to FROM (...) I.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply