Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005
    Posts
    30

    Unanswered: SQL function in Select

    Hi,
    What I want to do is include a function in my select:

    select myFunc(aTable.aFieldValue), aTable.aFieldValue from aTable

    but where myFunc actually returns a Table instead of a scalar. How can I do this please?

    for example, I know:

    select * from myFunc(123) from aTable

    will work fine.

    and

    select myScalarFunc(aTable.aFieldValue) from aTable

    will also work.

    But how can i get the 123 value from a table?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't think you can do that...why not post some code. Read the sticky at the top of the board
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2005
    Posts
    30
    try this:

    /* ================================================== ========== */
    /* DropTable: Example */
    /* ================================================== ========== */
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Example]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    begin
    print 'Dropping table Example ...'
    drop table [dbo].[Example]
    end
    GO

    /* ================================================== ========== */
    /* Table: Example */
    /* ================================================== ========== */
    print 'Creating table Example ...'

    create table dbo.Example
    (
    Id int identity primary key,
    Name varchar(10) not null unique,
    Status varchar(3) not null,
    Amount money null,
    )

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Example]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    print 'Success: created table Example ...'
    else
    print 'Failure: failed to create table Example ...'
    GO

    /* ================================================== ========== */
    /* Security */
    /* ================================================== ========== */
    grant select on [dbo].[Example] to public
    go

    /* ================================================== ========== */
    /* Data */
    /* ================================================== ========== */
    insert into dbo.Example(Name, Status, Amount) values ('Item 1' , 'O', 20)
    insert into dbo.Example(Name, Status, Amount) values ('Item 2' , 'C', 120)

    /* ================================================== ========== */
    /* ExampleFund: returning table */
    /* ================================================== ========== */
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Rules->ExampleFunc]') and xtype in (N'FN', N'IF', N'TF'))
    begin
    print 'Dropping function Rules->ExampleFunc ...'
    drop function [dbo].[Rules->ExampleFunc]
    end
    GO
    print 'Creating function Rules->ExampleFunc ...'
    GO

    create function [dbo].[Rules->ExampleFunc](@Status varchar(3), @Amount decimal)
    Returns @breakdown table (Yesterday money, Today money, Tomorrow money)
    As
    begin
    declare @amt money, @today money, @tomorrow money, @yesterday money

    set @amt = isnull(@Amount, 0)
    set @today = 0
    set @tomorrow = 0
    set @yesterday = 0

    if @Status = 'O'
    set @today = @amt / 3

    if @Status = 'C'
    set @tomorrow = @amt

    set @yesterday = @amt - @tomorrow - @today

    insert into @breakdown(Yesterday, Today, Tomorrow) values (@yesterday, @today, @tomorrow)
    return
    end
    Go

    if ( object_id('dbo.Rules->ExampleFunc') is not null)
    print 'Success: Created function Rules->ExampleFunc.'
    else
    print 'Error: Function Rules->ExampleFunc was not created successfully.'
    GO

    --grant execute on [dbo].[Rules->ExampleFunc] to [public]
    --GO

    /* ================================================== ========== */
    /* ExampleFund: returning scalar */
    /* ================================================== ========== */
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Rules->ExampleScalarFunc]') and xtype in (N'FN', N'IF', N'TF'))
    begin
    print 'Dropping function Rules->ExampleScalarFunc ...'
    drop function [dbo].[Rules->ExampleScalarFunc]
    end
    GO
    print 'Creating function Rules->ExampleScalarFunc ...'
    GO

    create function [dbo].[Rules->ExampleScalarFunc](@Status varchar(3), @Amount decimal)
    Returns money
    As
    begin
    declare @amt money, @today money, @tomorrow money, @yesterday money

    set @amt = isnull(@Amount, 0)

    if @Status = 'O'
    set @amt = @amt / 3

    if @Status = 'C'
    set @amt = @amt

    return @amt
    end
    Go

    if ( object_id('dbo.Rules->ExampleScalarFunc') is not null)
    print 'Success: Created function Rules->ExampleScalarFunc.'
    else
    print 'Error: Function Rules->ExampleScalarFunc was not created successfully.'
    GO


    /* ================================================== ========== */
    /* Example uses: 1 easy */
    /* ================================================== ========== */
    declare @Amount decimal, @Status varchar(3)
    select @Status = status from Example
    select @Amount = amount from Example
    select * from [dbo].[Rules->ExampleFunc](@Status, @Amount)

    /* ================================================== ========== */
    /* Example uses: 1 easy */
    /* ================================================== ========== */
    select [dbo].[Rules->ExampleScalarFunc](Status, Amount) from Example

    /* ================================================== ========== */
    /* Example uses: 1 not so */
    /* ================================================== ========== */

  4. #4
    Join Date
    May 2005
    Posts
    30
    Also, reason I need to do this is because I need to process thousands of items as quickly as possible. i'm taking xml containing thousands of entries from queue, openxml-ing to a temp table, using this to insert/update a summarized view while also applying some user-defined rules to split values into 3 'buckets', etc.

    Point is, cursors, etc. just do not cut it.

    Up to now the 'rules' were boolean (either in one bucket or the other) but now that has changed so that the raw data needs to be split across 3 buckets - with a single value potentially being divided.

    I also don't want to have to call 3 independent functions because the values depend on what rules have been applied. Gets complicated.

    Just would like to be able to select x, y, x, + func(x, y) where func returns more than a single value. Even a join to the results of func would be fine but i absolutely NEED to be able to feed that func with values from the select (i.e. that x and y are picked from from within the select and not set beforehand as in variables).

    Hope this is clear.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •