SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Evaluation of duplicate aliases for multiple objects in a procedure


Evaluation of duplicate aliases for multiple objects in a procedure

Author
Message
SW Skeen
SW Skeen
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 688
I had a submittal this morning for release to production that contains 2 aliases 'o' for a physical table and a temp table. To my knowledge I haven't seen this before and am wondering what is likely or guaranteed to happen if this code were released. The procedure compiles fine so the syntax doesn't appear to cause an issue.

The procedure code snippet here is the representative example:

SELECT 1,2,3,4 from ORDERHEADER O

CREATE TABLE #OUTPUT (1,2,3,4)

INSERT INTO OUTPUT (1,2,3,4)

UPDATE O SET ord_hdrnumber = 5 ---WHAT HAPPENS HERE!
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25994 Visits: 17528
SW Skeen (5/7/2014)
I had a submittal this morning for release to production that contains 2 aliases 'o' for a physical table and a temp table. To my knowledge I haven't seen this before and am wondering what is likely or guaranteed to happen if this code were released. The procedure compiles fine so the syntax doesn't appear to cause an issue.

The procedure code snippet here is the representative example:

SELECT 1,2,3,4 from ORDERHEADER O

CREATE TABLE #OUTPUT (1,2,3,4)

INSERT INTO OUTPUT (1,2,3,4)

UPDATE O SET ord_hdrnumber = 5 ---WHAT HAPPENS HERE!


The alias you used in a previous query has no bearing on the subsequent update statement. What would happen with that update is it would attempt to update the persistent object named O. This could be a table, view or possibly a database alias (not to be confused with the query level alias at the beginning of your snippet). If there is no persistent object with that name it will throw and error about object not found.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
SW Skeen
SW Skeen
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 688
Thanks for the reply. upon reading it I realize my sample was not as complete or accurate as I had hoped. Here is the actual code:

SELECT DISTINCT
l.lgh_number,
o.mov_number

FROM orderheader o
INNER JOIN legheader l ON o.mov_number = l.mov_number
WHERE ord_completiondate BETWEEN @startdate AND @enddate
AND ord_revtype1 = @costcenter
AND ord_tractor = ISNULL(@tractor,ord_tractor)
AND stp_event = 'NBS'

--SELECT * FROM #Routes r

CREATE TABLE #Output (lgh_number INT, mov_number INT, Pay_Miles INT, Bill_Miles INT, ord_hdrnumber INT,
Order_Number CHAR(12), Tractor VARCHAR(8), BOL VARCHAR(30), Invoice_Miles FLOAT, FSC_Rate MONEY,
FSC_Charge MONEY, Pyd_Amount FLOAT, Completion_Date DATETIME, Truck_Type VARCHAR(6))

INSERT INTO #Output
( lgh_number ,
mov_number ,
Pay_Miles ,
Bill_Miles)

SELECT DISTINCT
lgh_number,
mov_number,
SUM(stp_lgh_mileage),
SUM(stp_ord_mileage)

UPDATE o SET ord_hdrnumber = (SELECT ord_hdrnumber FROM dbo.orderheader oh WHERE o.mov_number = o.mov_number)
FROM #Output o

UPDATE o SET Order_Number = (SELECT ord_number FROM dbo.orderheader oh WHERE o.mov_number = o.mov_number)
FROM #Output o

UPDATE o SET Tractor = (SELECT ord_tractor FROM dbo.orderheader o
WHERE o.mov_number = o.mov_number)
FROM #Output o

a4apple
a4apple
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 406
Don't see anything wrong in the query unless there is an issue with my eyes expect that I don't see a FROM clause near the INSERT Command.

Good Luck Smile .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
SW Skeen
SW Skeen
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 688
Thank you both for your replies. I just had never seen an instance where this happened to occur, usually policy states 3 letter aliases. this user was recently brought in from our field operations and I now need to see where this code sources from as I don't think he cobbled this together himself. Just checking my sanity.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25994 Visits: 17528
Agreed with the previous poster. There does not appear to be any issue here. The aliases in here are the same in each query and reference different objects but these are query level aliases and are out of scope for the next query.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28103 Visits: 39938

UPDATE o SET Order_Number = (SELECT ord_number FROM dbo.orderheader oh
WHERE o.mov_number = o.mov_number)
FROM #Output o



that is the same as 1 = 1;

maybe you meant to use WHERE oh.mov_number = o.mov_number

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search