how to replace this cursor with set based solution?

  • 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

  • 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/

  • 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

  • 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/

  • 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

  • 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