May 3, 2018 at 5:39 am
Hello,
Is there anyway I can replicate this within an INSERT/OUTPUT statement:
declare @ID as table(ID uniqueidentifier);
insert intotbl_Link_Update_History
outputinserted.ID into @ID
values(newid(), @LinkID, getdate(), @user-id);
selectdbo.fnc_FormatDate(luh.Updated_Date, 106) + ' (' + u.Forename + ' ' + u.Surname + ')'
fromtbl_Link_Update_History luh join
tbl_Users u on u.[User_ID] = luh.Updated_By_ID
whereluh.ID =
(
selectID
from@ID
);
The needed result is a scalar value: 1 January 1900 (Firstname Lastname)
Thanks
May 8, 2018 at 6:58 am
lanky_doodle - Thursday, May 3, 2018 5:39 AMHello,Is there anyway I can replicate this within an INSERT/OUTPUT statement:
declare @ID as table(ID uniqueidentifier);
insert intotbl_Link_Update_History
outputinserted.ID into @ID
values(newid(), @LinkID, getdate(), @user-id);
selectdbo.fnc_FormatDate(luh.Updated_Date, 106) + ' (' + u.Forename + ' ' + u.Surname + ')'
fromtbl_Link_Update_History luh join
tbl_Users u on u.[User_ID] = luh.Updated_By_ID
whereluh.ID =
(
selectID
from@ID
);
The needed result is a scalar value: 1 January 1900 (Firstname Lastname)
Thanks
Part of the problem is that you are asking for a SCALAR result from a SELECT from a table. If you ever have more than one row in that table, it's not going to work. If you were to just change the = character to the word IN, you'd have a functioning query, but we don't know for sure if that's what you actually need.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 8, 2018 at 7:01 am
lanky_doodle - Thursday, May 3, 2018 5:39 AMHello,Is there anyway I can replicate this within an INSERT/OUTPUT statement:
declare @ID as table(ID uniqueidentifier);
insert intotbl_Link_Update_History
outputinserted.ID into @ID
values(newid(), @LinkID, getdate(), @user-id);
selectdbo.fnc_FormatDate(luh.Updated_Date, 106) + ' (' + u.Forename + ' ' + u.Surname + ')'
fromtbl_Link_Update_History luh join
tbl_Users u on u.[User_ID] = luh.Updated_By_ID
whereluh.ID =
(
selectID
from@ID
);
The needed result is a scalar value: 1 January 1900 (Firstname Lastname)
Thanks
And now that I look a little closer, you have a different kind of problem. You are expecting to limit a query result to values in a table that is only getting inserted into by the output of that very query. It's NEVER going to work. I'm not even sure what you think the result of this will be....
EDIT: I have to retract this... I missed the values clause and that matters. Just change the = to IN and you should be good to go, assuming that the other tables do have data in them that match the criteria specified.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 8, 2018 at 9:58 am
sgmunson - Tuesday, May 8, 2018 7:00 AMlanky_doodle - Thursday, May 3, 2018 5:39 AMHello,Is there anyway I can replicate this within an INSERT/OUTPUT statement:
declare @ID as table(ID uniqueidentifier);
insert intotbl_Link_Update_History
outputinserted.ID into @ID
values(newid(), @LinkID, getdate(), @user-id);
selectdbo.fnc_FormatDate(luh.Updated_Date, 106) + ' (' + u.Forename + ' ' + u.Surname + ')'
fromtbl_Link_Update_History luh join
tbl_Users u on u.[User_ID] = luh.Updated_By_ID
whereluh.ID =
(
selectID
from@ID
);
The needed result is a scalar value: 1 January 1900 (Firstname Lastname)
Thanks
And now that I look a little closer, you have a different kind of problem. You are expecting to limit a query result to values in a table that is only getting inserted into by the output of that very query. It's NEVER going to work. I'm not even sure what you think the result of this will be....
The only problem I see with the second query is the use of the '=' sign instead of using IN.
But then again, I could be wrong.
May 8, 2018 at 3:07 pm
Lynn Pettis - Tuesday, May 8, 2018 9:58 AMsgmunson - Tuesday, May 8, 2018 7:00 AMlanky_doodle - Thursday, May 3, 2018 5:39 AMHello,Is there anyway I can replicate this within an INSERT/OUTPUT statement:
declare @ID as table(ID uniqueidentifier);
insert intotbl_Link_Update_History
outputinserted.ID into @ID
values(newid(), @LinkID, getdate(), @user-id);
selectdbo.fnc_FormatDate(luh.Updated_Date, 106) + ' (' + u.Forename + ' ' + u.Surname + ')'
fromtbl_Link_Update_History luh join
tbl_Users u on u.[User_ID] = luh.Updated_By_ID
whereluh.ID =
(
selectID
from@ID
);
The needed result is a scalar value: 1 January 1900 (Firstname Lastname)
Thanks
And now that I look a little closer, you have a different kind of problem. You are expecting to limit a query result to values in a table that is only getting inserted into by the output of that very query. It's NEVER going to work. I'm not even sure what you think the result of this will be....
The only problem I see with the second query is the use of the '=' sign instead of using IN.
But then again, I could be wrong.
It will operate once you change = to IN. The @ID table is defined, but I'm doubtful any rows would get selected. That @ID table starts with no rows, and is the subject of inserts only from the results of that same query, which should provide no rows because it goes after rows that match a value in an empty table. Thus there's no way for data to make it into that table. If SQL didn't see the 0 rows there and shortcut the query execution to return 0 rows in the result set, I'd be very surprised.
I just realized that I missed the values clause entirely. I'm retracting my post... my bad...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply