September 27, 2013 at 1:26 pm
is it possible to put the right side of the OUTER APPLY into a TVF, given that the input values are coming from an array type variable?
--Quote me
September 27, 2013 at 1:34 pm
polkadot (9/27/2013)
is it possible to put the right side of the OUTER APPLY into a TVF, given that the input values are coming from an array type variable?
Sure. I assume you mean you need to parse this parameter? Take a look at the link in my signature about splitting strings.
_______________________________________________________________
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/
September 27, 2013 at 1:47 pm
before I go there, I want to clarify. The parameter @benches is called out in the left query as well as the right.
I tried this:
created tvf
CREATE FUNCTION GetBench (@benches varchar(20))
RETURNS TABLE
AS RETURN
SELECT r.createdate
FROM Sandbox.dbo.Runs r
INNER JOIN Sandbox.dbo.runs_machines rm ON rm.runid = r.runid
INNER JOIN Sandbox.dbo.Machines m ON m.machineid = rm.machineid
WHERE m.NAME LIKE (@benches + '[0-9][0-9][0-9]')
AND r.createdate > dateadd(M, - 3, getdate());
incorporated in APPLY
DECLARE @benches TABLE (Bench varchar(20));
INSERT @benches
values
('LabC1'),
('LabC2'),
('LabC3'),
('LabC4');
INSERT INTO RunsByBench
SELECT Bench AS BenchName,
COUNT(CreateDate) AS NumberOfRunsOnBench,
MAX(CreateDate) AS LastRun
FROM @benches
OUTER APPLY
GetBench (@benches)
group by Bench;
select * from RunsByBench;
got error:
Msg 206, Level 16, State 2, Line 10
Operand type clash: table is incompatible with varchar
So, you say Jeff Moden's splitter article contains the answer, yes? I'm not ready for that article unless it addresses this problem. I currently can make no sense of it and it's long. If you can provide more pointed guidance to whether I should be able to encapsulate the right query into TVF, given that it relies on the same variable as the left side of query, I would appreciate.
--Quote me
September 27, 2013 at 2:37 pm
polkadot (9/27/2013)
before I go there, I want to clarify. The parameter @benches is called out in the left query as well as the right.I tried this:
created tvf
CREATE FUNCTION GetBench (@benches varchar(20))
RETURNS TABLE
AS RETURN
SELECT r.createdate
FROM Sandbox.dbo.Runs r
INNER JOIN Sandbox.dbo.runs_machines rm ON rm.runid = r.runid
INNER JOIN Sandbox.dbo.Machines m ON m.machineid = rm.machineid
WHERE m.NAME LIKE (@benches + '[0-9][0-9][0-9]')
AND r.createdate > dateadd(M, - 3, getdate());
incorporated in APPLY
DECLARE @benches TABLE (Bench varchar(20));
INSERT @benches
values
('LabC1'),
('LabC2'),
('LabC3'),
('LabC4');
INSERT INTO RunsByBench
SELECT Bench AS BenchName,
COUNT(CreateDate) AS NumberOfRunsOnBench,
MAX(CreateDate) AS LastRun
FROM @benches
OUTER APPLY
GetBench (@benches)
group by Bench;
select * from RunsByBench;
got error:
Msg 206, Level 16, State 2, Line 10
Operand type clash: table is incompatible with varchar
So, you say Jeff Moden's splitter article contains the answer, yes? I'm not ready for that article unless it addresses this problem. I currently can make no sense of it and it's long. If you can provide more pointed guidance to whether I should be able to encapsulate the right query into TVF, given that it relies on the same variable as the left side of query, I would appreciate.
The problem here is that you are mixing a table and a scalar value.
First you create your iTVF GetBench. It receives a varchar(20) as the only parameter. However, in your query you are mixing the references.
I would recommend making this a bit simpler until you get the hard parts ironed out. Specifically, don't do an insert until you get the select working. Drop all the grouping and just pull select *.
Let's start here:
DECLARE @benches TABLE (Bench varchar(20));
INSERT @benches
values
('LabC1'),
('LabC2'),
('LabC3'),
('LabC4');
SELECT *
FROM @benches
So far so good. You have a table variable, it is populated with some rows and you can select them.
So now you want to get some additional results by joining to your iTVF. However, in the code you posted you are passing it a table variable, this is NOT a varchar(20) as defined by your function.
I think that you want to pass a single row's value here but I am not entirely sure.
That would be something like this.
SELECT *
FROM @benches b
OUTER APPLY dbo.GetBench(b.Bench)
Does that help?
From your earlier post you could change your function to use schema binding by simply using 2 part naming instead of 3...
CREATE FUNCTION GetBench
(
@benches varchar(20)
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT r.createdate
FROM dbo.Runs r
INNER JOIN dbo.runs_machines rm ON rm.runid = r.runid
INNER JOIN dbo.Machines m ON m.machineid = rm.machineid
WHERE m.NAME LIKE (@benches + '[0-9][0-9][0-9]')
AND r.createdate > dateadd(M, - 3, getdate());
_______________________________________________________________
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/
September 27, 2013 at 5:37 pm
Very nice of you Sean.
Aliased the table variable, and referenced the values in the table variable with column name.
worked:
DECLARE @benches TABLE (Bench varchar(20));
INSERT @benches
values
('LabC1'),
('LabC2'),
('LabC3'),
('LabC4');
INSERT INTO RunsByBench
SELECT
Bench as BenchName,
COUNT(CreateDate) AS NumberOfRunsOnBench,
MAX(CreateDate) AS LastRun
FROM @benches b
OUTER APPLY dbo.GetBench(b.bench)
group by Bench;
--Quote me
September 30, 2013 at 7:32 am
polkadot (9/27/2013)
Very nice of you Sean.Aliased the table variable, and referenced the values in the table variable with column name.
worked:
DECLARE @benches TABLE (Bench varchar(20));
INSERT @benches
values
('LabC1'),
('LabC2'),
('LabC3'),
('LabC4');
INSERT INTO RunsByBench
SELECT
Bench as BenchName,
COUNT(CreateDate) AS NumberOfRunsOnBench,
MAX(CreateDate) AS LastRun
FROM @benches b
OUTER APPLY dbo.GetBench(b.bench)
group by Bench;
Glad that worked you and thanks for letting me know. 😀
_______________________________________________________________
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/
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply