Constructing a singleton query from a relational data source

Discussion in 'microsoft.public.sqlserver.datamining' started by Bohdan Szymanik, Apr 16, 2007.

  1. I can't seem to do the following (in pseudo code)

    select prediction of an attribute
    from a data mining model
    natural prediction join
    (SELECT(
    OPENQUERY(source, some select)
    ) as nested table
    ) as t

    instead I need to manually build up the singleton parameters in a bunch of union'd selects.

    Am I missing something?
     
    Bohdan Szymanik, Apr 16, 2007
    #1
    1. Advertisements

  2. You will need to use the SHAPE clause to use relational queries as nested
    tables:

    select prediction of an attribute
    from a data mining model
    natural prediction join
    SHAPE { OPENQUERY(source, 'SELECT CaseKey ... FROM CaseTable ORDER BY
    CaseKey') }
    APPEND (
    {OPENQUERY(source, 'SELECT ForeignKey, NestedColumns... FROM NestedTable
    ORDER BY ForeignKey') }
    RELATE CaseKey TO ForeignKey
    ) AS NestedTable
    AS T


    --
    --
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Please do not send email directly to this alias. It is for newsgroup
    purposes only.

    thanks,
    bogdan
     
    Bogdan Crivat [MSFT], Apr 16, 2007
    #2
    1. Advertisements

  3. Ah, for a moment then I thought it might not be possible to create the singleton query with the generated nested table because I couldn't see how to create a case - of course you just dummy one up. The working query becomes:


    SELECT
    [Model_NB].[Bucket],
    TopCount(PredictHistogram([Bucket]), $AdjustedProbability, 3)
    From
    [Model_NB]
    NATURAL PREDICTION JOIN
    SHAPE { OPENQUERY(Test, 'SELECT 1 as CaseKey') }
    APPEND (
    { OPENQUERY(Test,'
    select 1 as ForeignKey, Term
    from Terms
    cross apply Matches(''some text with terms embedded within it'',''\b('' + Term + '')\b'')'
    )
    } RELATE [CaseKey] TO [ForeignKey]
    ) AS [Msg Term Vectors]
    AS T

    What I've done is expose the regular expression MatchCollection in the CLR as a TVF to enable me to pick up the terms in the incoming text. Works now, thanks, Bohdan
     
    Bohdan Szymanik, Apr 17, 2007
    #3
  4. That's a pretty cool query!

    --
    --
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Please do not send email directly to this alias. It is for newsgroup
    purposes only.

    thanks,
    bogdan

     
    Bogdan Crivat [MSFT], Apr 18, 2007
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.