ADOMD.NET : dynamic parameters

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);