Logically speaking, the two cases below should behave the same. However case 2's output is wrong. Perhaps someone knows what's wrong in case 2.
Case 1:
-
AdomdCommand cmd = new AdomdCommand();
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" UNION SELECT @.var1 as [var] " +
" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";
Case 2
-
AdomdCommand cmd = new AdomdCommand();
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] ";
for(int i=1; i<3; i++)
{
cmd.CommandText = cmd.CommandText +
"UNION SELECT @.var" + i.ToString() + " as [var] ";
}
cmd.CommandText = cmd.CommandText + ") AS [vartable]) AS t";
Mary
Can you get a debug print of the resulting command text?
Actually, the best way to solve this problem is to use rowset parameters. There is a sample here: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx
(look for // Issue a DMX query from a rowset parameter and display a result)
For nested tables you will have to SHAPE the rowset.
|||
I've read this article:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1313.aspx
The problem in my case is that I have to use the "UNION" keyword.
How to use rowset parameters for such case?
My case
--
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" UNION SELECT @.var1 as [var] " +
" UNION SELECT @.var2 as [var]) AS [vartable]) AS t";
The above link has only a solution for this case
-
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" (SELECT " +
" (SELECT @.var0 as [var] " +
" SELECT @.var1 as [var] " +
" SELECT @.var2 as [var]) AS [vartable]) AS t";
// Suggested solution for the above case (not my case):
DataTable table = new DataTable();
table.Columns.Add("term0", System.Type.GetType("System.String"));
table.Columns.Add("term1", System.Type.GetType("System.String"));
table.Columns.Add("term2", System.Type.GetType("System.String"));
...
...
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" @.InputTable as t";
cmd.Parameters.Add("InputTable", table);
Clearly, the provided solution not applicable for my case.
Please assist!
Mary
|||
So, basically, the problem is that your input is nested, right? It contains the [vartable] nested table.
You can try this:
DataTable topTable = new DataTable();
topTable.Columns.Add("K", typeof(System.Int32)); // case key
// Add one row to the case table with K=1
object[] row = new object[1];
row[0] = 1; // top key
topTable.Rows.Add(row);
DataTable nestedTable = new DataTable();
nestedTable.Columns.Add("K", typeof(System.Int32)); // foreign key
table.Columns.Add("var", System.Type.GetType("System.String"));
// Add one row to nested table for each @.var value
// each nested row has 1 as foreign key
row = new object[2];
for( ... )
{
row[0] = 1; // foreign key, alway one to match the topTable single row's key
row[1] = "aaa"; // foreign key
nestedTable.Rows.Add(row);
}
cmd.CommandText = "SELECT Cluster(), PredictCaseLikelihood()" +
" FROM [Data Validation]" +
" NATURAL PREDICTION JOIN" +
" SHAPE {@.topTable} " +
" APPEND( {@.nestedTable} RELATE K TO K) "+
" AS vartable AS T";
cmd.Parameters.Add("topTable", topTable);
cmd.Parameters.Add("nestedTable", nestedTable);
