Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Evaluation of duplicate aliases for multiple objects in a procedure Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 7:46 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 2:31 PM
Points: 62, Visits: 480
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!
Post #1568476
Posted Wednesday, May 7, 2014 7:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's 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)
Post #1568484
Posted Wednesday, May 7, 2014 8:09 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 2:31 PM
Points: 62, Visits: 480
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
Post #1568491
Posted Wednesday, May 7, 2014 10:14 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
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 :) .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Post #1568574
Posted Wednesday, May 7, 2014 10:20 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 2:31 PM
Points: 62, Visits: 480
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.
Post #1568579
Posted Wednesday, May 7, 2014 10:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's 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)
Post #1568581
Posted Wednesday, May 7, 2014 10:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 12,903, Visits: 32,143
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1568582
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse