April 4, 2016 at 5:21 pm
I'm currently writing some code for a task at work where I need to return values for individual ID's stating what there value is when being passed some parameters. The problem I have is that parameter list was currently set at 4 and now have asked to increase from 4 to 8 with the possibility of further expansion at a later date.
Previously I had a lookup table with all the 4 values and then used OUTER APPLY for these 4 to give the results I needed. I can expand the 4 to 8 to get the results again but if it jumps to 30 params then I don't want 30 OUTER APPLY's.
I know we can do this dynamically (which I've never written dynamic code this way before so i'll need to learn) but is there any other way this can be achieved or is Dynamic the only way to go.
CREATE TABLE #TestMapping
(
ID INT IDENTITY(1,1),
ValueID INT,
IsEnabled BIT,
CreatedDateTime DATETIME
)
INSERT INTO #TestMapping (ValueID, IsEnabled, CreatedDateTime)
VALUES
(10, 1, GETDATE()),
(20, 1, GETDATE()),
(30, 1, GETDATE()),
(40, 1, GETDATE()),
SELECT TOP 10
table1.ID,
oa1.Result
FROM
dbo.table1
OUTER APPLY (
SELECT TOP 1 Result
FROM dbo.table2
INNER JOIN #TestMapping tm
ON table2.ValueID= tm.ValueID
WHERE
table2.ValueID= 1
) oa1
Sample code above of how I'm currently doing it.
Any help would be appreciated.
April 4, 2016 at 5:27 pm
April 4, 2016 at 5:31 pm
Thanks for the quick reply. I thought it was the case but thought I'll see if anything else could be done.
I'll have a look at that link and revert back if any questions. I'm sure I will.
April 4, 2016 at 5:48 pm
Phil,
I've had a quick read, I'm a little confused... The values of 4,8,30,N... will be passed via the #temp table i created.
Since I'm using an outer apply in this case and I'm selecting a top 1 - I need to pass the ID from the temp table to give me the result i want. If i don't i return the same result.
How can i call that query dynamically by passing through the ID needed.
April 4, 2016 at 5:52 pm
I've updated the code to show what i mean with multiple outers. Sorry for the confusion.
CREATE TABLE #TestMapping
(
ID INT IDENTITY(1,1),
ValueID INT,
IsEnabled BIT,
CreatedDateTime DATETIME
)
INSERT INTO #TestMapping (ValueID, IsEnabled, CreatedDateTime)
VALUES
(10, 1, GETDATE()),
(20, 1, GETDATE()),
(30, 1, GETDATE()),
(40, 1, GETDATE()),
DECLARE @ValueID1 INT = (SELECT ValueID FROM #TestMapping WHERE ID = 1 AND IsEnabled = 1)
DECLARE @ValueID2 INT = (SELECT ValueID FROM #TestMapping WHERE ID = 2 AND IsEnabled = 1)
DECLARE @ValueID3 INT = (SELECT ValueID FROM #TestMapping WHERE ID = 3 AND IsEnabled = 1)
DECLARE @ValueID4 INT = (SELECT ValueID FROM #TestMapping WHERE ID = 4 AND IsEnabled = 1)
SELECT TOP 10
table1.ID,
oa1.Result
oa2.Result
oa3.Result
FROM
dbo.table1
OUTER APPLY (
SELECT TOP 1 Result
FROM dbo.table2
INNER JOIN #TestMapping tm
ON table2.ValueID= tm.ValueID
WHERE
table2.ValueID= @ValueID1
) oa1
OUTER APPLY (
SELECT TOP 1 Result
FROM dbo.table2
INNER JOIN #TestMapping tm
ON table2.ValueID= tm.ValueID
WHERE
table2.ValueID= @ValueID2
) oa2
OUTER APPLY (
SELECT TOP 1 Result
FROM dbo.table2
INNER JOIN #TestMapping tm
ON table2.ValueID= tm.ValueID
WHERE
table2.ValueID= @ValueID3
) oa3
April 4, 2016 at 6:01 pm
Tava (4/4/2016)
Phil,I've had a quick read, I'm a little confused... The values of 4,8,30,N... will be passed via the #temp table i created.
Since I'm using an outer apply in this case and I'm selecting a top 1 - I need to pass the ID from the temp table to give me the result i want. If i don't i return the same result.
How can i call that query dynamically by passing through the ID needed.
You were right to be confused. I answered the question I thought I was reading ... not what you actually wrote!
Having said that, despite your code samples, I do not understand what is required here. Though I am almost certain that there will be a much better way.
What does your proc's current list of arguments look like? Is it just a list of numbers? Do you pass it as a table?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 4, 2016 at 6:07 pm
Phil Parkin (4/4/2016)
Tava (4/4/2016)
Phil,I've had a quick read, I'm a little confused... The values of 4,8,30,N... will be passed via the #temp table i created.
Since I'm using an outer apply in this case and I'm selecting a top 1 - I need to pass the ID from the temp table to give me the result i want. If i don't i return the same result.
How can i call that query dynamically by passing through the ID needed.
You were right to be confused. I answered the question I thought I was reading ... not what you actually wrote!
Having said that, despite your code samples, I do not understand what is required here. Though I am almost certain that there will be a much better way.
What does your proc's current list of arguments look like? Is it just a list of numbers? Do you pass it as a table?
I updated the test sample above, i just wrote it quick and dirty thinking it would be easy to understand. The values come from the Temp (which in fact will be a proper lookup) and I'm passing a local variable to capture that and use it in the outer apply. Hopefully that makes more sense.
pretty much you will be adding a new local variable each time a entry is created (unless you update the table entry) its more when a 5th entry gets added then the Query itself will need to include a futher outer apply.
April 4, 2016 at 6:12 pm
So am I right in thinking that the number of columns which you are returning depends on the number of rows in the temp table? There's some unusual stuff going on here.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 4, 2016 at 6:16 pm
Phil Parkin (4/4/2016)
So am I right in thinking that the number of columns which you are returning depends on the number of rows in the temp table? There's some unusual stuff going on here.
yeap exactly, so imagine there are ValueID's added in the temp, provided all 20 are enabled i would expect to have a 20 outer apply scenario. Returning who the ID belows to and then 20 columns showing each value per ValueID.
if it was locked to 2/3/4 values, 4 outer applys would be sufficient but in this case there could be 20. hence the need to try and write it in a way it will generate regardless of how many Rows there are in the temp table.
April 4, 2016 at 6:16 pm
OK, let me try again with this post.
I have some questions about your sample code. Looking only up to the end of the first APPLY:
select top 10
table1.ID
,oa1.Result
from dbo.table1
outer apply (select top 1
Result
from dbo.table2
inner join #TestMapping tm on table2.ValueID = tm.ValueID
where table2.ValueID = @ValueID1
) oa1;
Firstly: Select top without an ORDER BY means that a semi-random row will be returned.
Next, there is no relation between table1 and the other two tables. So the same results will be repeated for every row in table1.
Finally, 'Result' is not in the temp table, so it must be in table2. But if that's the case, doesn't the inner query become this?
select Result from table2 t2 where t2.ValueId = @ValueId1
I left out the 'TOP 1' because (as mentioned above) it makes little sense without an ORDER BY.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 4, 2016 at 6:51 pm
Firstly to answer your questions.
1. ORDER BY is in my code, i had it left out...
2. The relationship between Table1 & Table2 exists i left out a condition in my sample code as i thought it might not be relevant.
CREATE TABLE #TestMapping
(
ID INT IDENTITY(1,1),
ValueID INT,
ResolutionTypeID INT,
IsEnabled BIT,
CreatedDateTime DATETIME
)
INSERT INTO #TestMapping (ValueID, ResolutionTypeID, IsEnabled, CreatedDateTime)
VALUES
(10,11, 1, GETDATE()),
(20,11, 1, GETDATE()),
(30,12, 1, GETDATE()),
(40,2, 1, GETDATE()),
DECLARE @ValueID1 INT = (SELECT ValueID FROM #TestMapping WHERE ID = 1 AND IsEnabled = 1)
DECLARE @ValueID2 INT = (SELECT ValueID FROM #TestMapping WHERE ID = 2 AND IsEnabled = 1)
DECLARE @ValueID3 INT = (SELECT ValueID FROM #TestMapping WHERE ID = 3 AND IsEnabled = 1)
DECLARE @ValueID4 INT = (SELECT ValueID FROM #TestMapping WHERE ID = 4 AND IsEnabled = 1)
SELECT TOP 10
table1.ID,
oa1.Result
FROM
dbo.table1
OUTER APPLY (
SELECT TOP 1 Result
FROM dbo.table2
INNER JOIN #TestMapping tm
ON table2.ValueID= tm.ValueID
WHERE
table2.ValueID= @ValueID2
AND
table2.SourceID= (
CASE
WHEN tm.ResolutionTypeID = 11 THEN table1.ABC
WHEN tm.ResolutionTypeID = 2 THEN table1.DEF
WHEN tm.ResolutionTypeID = 12 THEN table1.GHI
END
)
ORDER BY
table1.ID
) oa1
does this make more sense?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply