Predicting in Trees

Hi! I have created a DMM using Trees. But when I go to the Mining Model Predition tab and select a Predict function, I get this in the criteria column: <Scalar column reference>[, EXCLUDE_NULL|INCLUDE_NULL][, INCLUDE_NODE_ID]. When select Result, I get this error: "An incorrect number of arguments are used in the function at line 3, column 3." I'm predicting a continuous variable.

But when I delete everything except <Scalar column reference> I get this error: "Parser: The syntax for '<' is incorrect."

When I delete everything in the criteria column, I get this: "Query execution failed."

If I change the criteria to "<Scalar column reference>,INCLUDE_NULL, INCLUDE_NODE_ID" I get the error again that the query execution failed.

I'm working from a data set I created. I had no problems with predictions using clustering, but can't seem to get Trees to work.

Hello,

<Scalar column reference> is supposed to be a placeholder for the actual column name. For example, if you are building a Decision Tree model to predict, say the [Bike Buyer] column (the example in the sample database coming with SQL Server 2005), the function call may look like: Predict( [Bike buyer]) or Predict( [Bike buyer], EXCLUDE_NULL).

Hope this helps

|||Very helpful! Thanks!

possible to select top 5 * of 2 or more individual criteria?

Hello,
I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
but in the same output I also want to include
select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
I have thousands of rows where fldx starts with 'T' and 'S'. Is it possible
to select the desired rows above in the same output? what is the tsql for
this?
Thanks,
RichTry
select top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
UNION ALL
select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
Keep in mind that the top clause doesn't have much meaning without an ORDER
BY, unless you are relying on the automatic ordering done on a table's
primary key.
"Rich" wrote:

> Hello,
> I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
> but in the same output I also want to include
> select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
> I have thousands of rows where fldx starts with 'T' and 'S'. Is it possib
le
> to select the desired rows above in the same output? what is the tsql fo
r
> this?
> Thanks,
> Rich
>|||use union all.
btw, what does "top 5" mean without an "order by" clause? also, "where fldx
like 'T%'" would much likely produce a more efficient exec plan than the
substring function on the column.
dean
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0D2A3956-4E96-4FDE-BA84-116F27C25856@.microsoft.com...
> Hello,
> I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
> but in the same output I also want to include
> select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
> I have thousands of rows where fldx starts with 'T' and 'S'. Is it
> possible
> to select the desired rows above in the same output? what is the tsql
> for
> this?
> Thanks,
> Rich
>|||SELECT * FROM
(SELECT TOP 5 * -- always use COLUMN LIST!
FROM tbl1
WHERE LEFT(fldx,1) = 'T'
ORDER BY '
) T
UNION ALL
(SELECT TOP 5 * -- always use COLUMN LIST!
FROM tbl1
WHERE LEFT(fldx,1) = 'S'
ORDER BY '
) S
ORDER BY '
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0D2A3956-4E96-4FDE-BA84-116F27C25856@.microsoft.com...
> Hello,
> I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
> but in the same output I also want to include
> select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
> I have thousands of rows where fldx starts with 'T' and 'S'. Is it
> possible
> to select the desired rows above in the same output? what is the tsql
> for
> this?
> Thanks,
> Rich
>|||Thank you all for your replies. I was working with substring earlier on
picking out 3 letters from a word, so that stuck in my brain. And I forgot
about including Order By for Top clause, and I was not even thinking about
Union All.
Thanks all for your help.
Rich
"Rich" wrote:

> Hello,
> I want to select the top 5 * from tbl1 where substring(fldx, 1, 1) = 'T'
> but in the same output I also want to include
> select top 5 * from tbl1 where substring(fldx, 1, 1) = 'S'
> I have thousands of rows where fldx starts with 'T' and 'S'. Is it possib
le
> to select the desired rows above in the same output? what is the tsql fo
r
> this?
> Thanks,
> Rich
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ez$bhXSIGHA.1180@.TK2MSFTNGP09.phx.gbl...
> SELECT * FROM
> (SELECT TOP 5 * -- always use COLUMN LIST!
> FROM tbl1
> WHERE LEFT(fldx,1) = 'T'
> ORDER BY '
> ) T
> UNION ALL
> (SELECT TOP 5 * -- always use COLUMN LIST!
> FROM tbl1
> WHERE LEFT(fldx,1) = 'S'
> ORDER BY '
> ) S
> ORDER BY '
Have you done a search for SELECT * in 2005 bol?
:)|||Do 800 hits make it a good practice? I don't think so.
ML
http://milambda.blogspot.com/|||> Have you done a search for SELECT * in 2005 bol?
Microsoft does plenty of things that violate best practices. Doesn't mean
you should do it too, and it certainly doesn't mean that I should advocate
it either.|||> Have you done a search for SELECT * in 2005 bol?
Microsoft does plenty of things that violate best practices. Doesn't mean
you should do it too, and it certainly doesn't mean that I should advocate
it either.|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:B77D3202-71FC-412D-A0BB-E8EEE669A599@.microsoft.com...
> Do 800 hits make it a good practice? I don't think so.
Perhaps the next time you advocate someone reading BOL
to get an intro to sql server you should use a asterick :)

Possible to access no. rows in groups outside the table?

Hi there,

I'm currently grouping data on some criteria, the way the data works basically means that there are between 2-3 groups guaranteed (no more). In the Group Header I have CountRows(table1_group) thus giving me the total number of rows in each group at the top of each grouping. However, I need to do a calculation in a textbox above the table using these row counts. The method of grouping is not too difficult when using the VB syntax in the grouping expression but more difficult to get the same effect from the SQL side and hence I don't want to have separate datasets calling different queries to get the information that way. Is there anyway to get access to these row counts on the groups? Reporting Services can't know how many groups there will be before processing so I'm not sure that this is possible? Ideally I suppose if there was a Group CountRows collection of some kind that could be accessed in an expression in the textbox or custom code then this might be possible. I could also add an invisible column and set the values to something specific depending on the group if there was a way to count the number of values in the table (unique values repeated in columns in a group, but unique to that group).

Any help is much appreciated,
Thanks.

Sorry, but it would also be feasible to place the textbox within the table by moving the headings and other table data downward leaving a gap at the top. However, the textbox although in the table is presumably still outside the scope of the group. Just thought I'd mention it incase it sparked an idea by anyone.

Thanks again.

|||

Have an invisible list above your table and add group the list by the same fields as your table and use Count or any other aggregate functions in textboxes inside that list.

Shyam

|||

Thanks for your response, but I'm not sure how to implement that. If I have say 3 groupings in my table. The RowCount at the top of the group headers gives the following at the top of each grouping in the table:

Group1 Total: 4

Group2 Total: 7

Group3 Total: 22

Then a single textbox at the top of the page would say

Group1 + Group2 = 11

Group1 + Group2 = 50% of Group3.

How would I accomplish this using the invisible list?

Thanks again.

|||

Have a hidden table at the top of your report and have the same 3 groupings and then delete all the rows except the group header rows. Write 3 functions in report code which will increment a public variable (3 ublic variables declared at the top of the report) whenever it is called. Say, the function names are CountGroup1, CountGroup2, CountGroup3.

In each of your 3 group headers, call each of the corresponding functions in the code to count the group records. Have the expression in the group headers like this:

=Code.CountGroup1(CountDistinct(Fields!Field1.Value, "table1_Group1"))

=Code.CountGroup2(CountDistinct(Fields!Field1.Value, "table1_Group2"))

=Code.CountGroup3(CountDistinct(Fields!Field1.Value, "table1_Group3"))

And you can access the counts by referring to the public variables at the top of your main table using an expression something like this Code.VariableName

Shyam