May 7, 2014 at 7:46 am
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!
May 7, 2014 at 7:55 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2014 at 8:09 am
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:
SELECTDISTINCT
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
May 7, 2014 at 10:14 am
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.
May 7, 2014 at 10:20 am
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.
May 7, 2014 at 10:21 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2014 at 10:21 am
UPDATE o SET Order_Number = (SELECT ord_number FROM dbo.orderheader oh
[highlight]WHERE o.mov_number = o.mov_number[/highlight])
FROM #Output o
that is the same as 1 = 1;
maybe you meant to use WHERE [highlight]oh.mov_number = o.mov_number[/highlight]
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply