Working With Trees And XML...
Oct 26, 2005
Hi, for a new project i'm trying to build a tree structure in SQL using one table with 'Node' & 'ParentNode' fields along with 'title', etc.
Table = Tree
Node : ParentNode : Title : Show_Record
1 0 Root 1
2 1 Child 1
Then i'm trying to get SQL to return that in XML to my Tree Control 'oBout ASP TreeView'.
Now the tree control can accept XML fine as long as it's in a set format, which shouldn't be difficult and should cut my code from 200 lines to one.
However getting SQL to return the table records in XML is proving to be a total nightmare.
I've hunted the web but not getting very far, I've even got a couple of O'Reilly guides but still no luck, so any help would be excellent with this.
I wrote a sql query (basic 'select * from tree for xml raw') which returns the results in RAW XML, but when I run this in Query Analyser it returns the results as one long string broken up with '<' & '>' but gets to the third record and cuts off halfway.
<row node="1" parentnode="0" title="Root" type_image="book.gif" type_expanded="True"/><row node="2" parentnode="1" title="Service Delivery" type_image="page.gif" type_expanded="False"/><row node="3" parentnode="1" title="Business Support" type_image="page.
Anyone know why Query Analyser does that?
Any help in this much appreciated, as you can imagine i'm at my wits end.
:eek:
View 4 Replies
ADVERTISEMENT
May 28, 2008
Hi,
My database looks like:
CategoryID ParentID Title Sort
1 -1 Cars 1
2 1 Honda 1
3 -1 Bikes 2
4 1 Ford 2
5 1 Toyota 3
6 3 Kawasaki 1
How can I retrieve the values in the following order:
1, 2, 4, 5, 3, 6
I have:
WITH MYCTE(categoryID, parentID, Title, Sort)
(
SELECT TOP 1 categoryID, parentID, Title, Sort
FROM Categories
WHERE parentID = -1
ORDER BY Sort ASC
UNION ALL
SELECT c.categoryID, c.parentID, c.title, c.sort
FROM Categories c
INNER JOIN MYCTE cte ON (cte.categoryID = c.parentID)
)
SELECT *
FROM MYCTE
It doesn't seem to work though? Help! hehe
View 4 Replies
View Related
Nov 6, 2006
I am studying the behavior of 200.000 clients. With the use of decision trees I would like to know if my clients will abandon our service or not. I use a training set of 21.822 clients and I use a predict variable "aband" wich is a discrete variable and it can be 0 or 1. In my training set i have 21.597 cases in which aband is 0 and 255 cases in which aband is 1. Looking at the classification matrix obtained using as input table a testing set (unselected data) I can see that my decision tree doesn't recognize the cases in which aband is 1. Here is the Classification Matrix:
Counts for Dati Training on [Aband]
Predicted 0 (Actual) 1 (Actual)
0 21597 225
1 0 0
What should I do?
Chiara
View 3 Replies
View Related
Jun 30, 2006
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.
View 3 Replies
View Related
Mar 6, 2007
hi,
I am using Time series alogorrithm.I just wants to know about the autoregression tree.I am having data like
Studid Date Perf
001 01/01/2007 90
001 02/01/2007 95
001 03/01/2007 89
002 01/01/2007 79
002 02/01/2007 90
002 03/01/2007 95
Like that. when I use my Model Viewer --> Descision Tree --> It shows like
Perf = 90.0084 + 1.02 * Perf(-2) + 0.25 * Perf(-2).
What is this value and how its getting calculated?
View 1 Replies
View Related
May 18, 2006
I would appreciate answers to the following doubts I have regarding Decision trees, CONTAINS and using CONTAINS in a DMX query:
1. Does MS decision tree work only off equality/inequality conditions for the nodes? Is it possible to use a predicate as the branch criteria for a node?
2. Can the T-SQL predicate CONTAINS(...) be used in a DMX query? I need to check if a column-value is a substring of another column and create an intermediate column that will enable me to construct a decision tree with the phrase-present/absent branch.
3. Can CONTAINS(...) be used in a select clause? Like -
SELECT CONTAINS(JAT.column1, '"Good day"')
FROM JustAnotherTable;
4. Does CONTAINS(...) support both arguments to be column references? Or, is it mandatory that the pattern (argument #2) has to be a literal string or a variable? E.g.: I need to know the validity of the following expression -
SELECT * FROM JustAnotherTable JAT
WHERE CONTAINS(JAT.column1, JAT.column3);
View 1 Replies
View Related
Aug 3, 2007
Hi,
I'm new to data mining, and have created an MS decision trees model. The model has the columns age, call outcome, call reason, country name, employee name and gender - all as inputs.
In the mining model viewer, I only get nodes for the age, despite having data for all the other columns.
Can anyone help?
Thanks
Jeremy
View 12 Replies
View Related
Dec 8, 2007
Hi,
I'm interested in understanding how the parametes work in the MS Decision Trees algorithm.
As far as I can tell, the MINIMUM_SUPPORT and COMPLEXITY_PENALTY parameters both control the number of splits and hence the depth of the tree.
Unfortunately the BOL descriptions are very brief - so can anyone tell me the difference between these 2 parameters?
Thanks
Jeremy
View 1 Replies
View Related
Mar 19, 2007
Hello.
I am trying to build a decision tree to predict prices. I have created the tree and looked at the lift charts, but I have not seen any of the traditional statistics I am used to from other programs (R-Squared, F statistics, etc.).
Does anyone have an example of how they calculated R-Squared for a decision tree on a continuous variable?
Thanks,
Brian
View 9 Replies
View Related
Dec 13, 2006
Hello,
I installed the bike buyer example and i am learning the DMX language. Now i wrote the following query (using MS decision trees):
SELECT
T.[Last Name],
[Bike Buyer],
PredictProbability(Predict([Bike Buyer])) AS [Probability]
From
[v Target Mail]
PREDICTION JOIN
OPENQUERY
(....... And so on..)
Now the result is surprising to me. In the resulttabel all the probabilities are equal.
Bike Buyer Probability
1 0.99994590500919611
0 0.99994590500919611
0 0.99994590500919611
0 0.99994590500919611
0 0.99994590500919611
1 0.99994590500919611
and so on.
Now i am wondering what predictProbability means. I thought that PredictProbability meant the probability that the prediction is correct. Now all the probabilities are the same and the input is different. Can somebody tell me what PredictProbability means or am I using it wrong?
Thanx in advance,
Joris Valkonet
View 6 Replies
View Related
Sep 12, 2007
In a decision tree algorithm, is there a known way to force a branch at a top level? For exmaple, I have 30 known decision patterns that are going to be completely different and I don't want them to intermingle. I wanted to force a branch at the top node on one of the 30 patterns so I wouldn't have to create 30 mining models per client.
Brian
View 4 Replies
View Related
May 9, 2006
I have some accounting data, with some transaction attributes and amounts.
I'm using Decision Trees to try and predict the next month's amount for certain combinations of attributes.
I've tried two different structures for the model:
A: one with 9 discrete text input attributes.
B: And another with the same 9 attributes + a avarage Amount for all combinations of the nine attribute for every transaction.
When i've processed them and look in the dependency network, it says that the strongest link for the structure A is attribute "1".
And for the second its the avarage-Amount attribute.
Okey, that seems fine, but the second strongest link in structure B is attribute "2".
Shouldn't it be attribute 1 like in structure A?
Second question, if I run the same data in a Neural Network model, the prediction becomes much worst then the decision tree.
I get many predictions that are negative values even though all training data contains positiv values.
The StDev becomes the same for every row also..
What am I doing wrong with that one. I have alot of transactions and a read somewhere that a Neural Network should work better than a decision tree in a case similar to mine.
The score in the "Lift chart" for the Neural Network model becomes 0,00 and for Decision Trees with the same data I get around 110.
View 1 Replies
View Related
Jul 26, 2007
How is the value of Prediction Probability calculated in the context of decision trees?
View 7 Replies
View Related
Dec 7, 2006
Hi,
I am using MS Decision Trees algorithm and for a specific model i get the above warning.As a result of that i dont get any splits in my tree. Is there anything i can do to avoid this?
Thank you for reading
View 1 Replies
View Related
Jan 5, 2007
Hi,
I am trying to run one of the mining models from the book "Delivering BI using SQl Server 2005" but I am running into "Decision Trees found no splits for model". The mining structure has 4 columns, the fourth one being marked as "Predict Only". My Cube slice for the model has sufficient data in the cube. I am lost.. Help!!
Regards
View 4 Replies
View Related
Dec 12, 2007
While recently working with several mining models, I came across something that struck me as pretty odd - and I'm hoping to find an explanation for the behavior.
Consider the following setup:
A single table in the relational database represents the only case table
A single, continuous column is the predictable
A mining structure has been created
The mining structure contains a single model, based on the MS Decision Trees algorithm
Input columns were selected for the model via the BI Studio wizard (i.e., those provided via the "Suggest" button)
The structure has been fully processed
Now, the interesting parts:
I view the scatterplot for the mining model, under the Mining Accuracy Chart tab
Back on the Mining Structure tab, I delete one of the input columns
I add the same column back into the structure
The structure is fully processed again
When I view the scatterplot for the mining model, under the Mining Accuracy Chart tab, a different set of data points are presented for the model predictions
A different set of decision trees under the Mining Model Viewer tab confirms thisHow could different patterns have been found this second time around, even though all of the input columns were the same (as well as the training cases)?
(Note: I encountered this situation while creating a new mining model that was identical to an existing one. Even though the models received the exact same inputs and training cases, they yielded different results. I was able to reproduce the behavior by using steps 1-6 above, though.)
Can someone provide some insight on this behavior, or some kind of explanation of what may be happening?
Thanks,
Joe Miller
View 3 Replies
View Related
Mar 3, 2006
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
View 5 Replies
View Related
Jan 31, 2005
I would like to find information on Clustered and Non-clustered indexes and how B-trees are used. I know a clustered index is placed into a b-tree which makes sense for fast ordered searching. What data structure does a non-clustered index use and how? I tried to find info. on the web but couldn't get much detail...
View 3 Replies
View Related
Sep 29, 2015
I followed the tutorial posted at [URL] ...
Everything was ok until the last step where I had to process the mining structure which resulted in a warning
"Informational (Data mining): Decision Trees found no splits for model, Tbl Decision Tree Example."
What does this error mean? How do I resolve it? Also, I only see the first level in the Mining Model Viewer, I don't see the levels 2 and 3.
View 2 Replies
View Related
Jan 7, 2004
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
View 2 Replies
View Related
Feb 23, 2008
Original suggestion for my problem was - Select * from TableA where ID not in ( Select ID from TableB)
When I run the query below using the IN operator I get 227 records returned but when I use NOT IN I get zero records when I expect well over 10,000. What am I missing?
using SQL 2000 server
SELECT LinksInfo.L_ID, LinksInfo.C_ID, Companies.C_CompanyName, Companies.C_Email, Companies.C_CompanyEmailFROM LinksInfo INNER JOIN Companies ON LinksInfo.C_ID = Companies.C_IDWHERE (LinksInfo.L_ID IN (SELECT ZL_ID FROM Location_Zip))
ZL_ID is not a primary key in Location_Zip
View 2 Replies
View Related
Mar 25, 2008
Hello again,
I think im missing something here, i just cant find out what it is.
I have a temp table:
CREATE TABLE #tempSearch(tempID BIGINT IDENTITY(1,1) PRIMARY KEY,username NVARCHAR(20) COLLATE Finnish_Swedish_CI_AS,lastlogin DATETIME,signupdate DATETIME)
Now i am trying to retrieve some data for each user that is inside this tempSearch list and have an id over xxx (xxx = the value of the parameter @first_id):
SELECT @sql = 'SELECT profile_publicinfo.username, profile_publicinfo.gender, profile_publicinfo.signupdate, profile_profilephoto.imageurl, profile_profilephoto.alttext, settings_username.color, profile_publicinfo.lastloginFROM #tempSearch INNER JOIN dbo.profile_publicinfo ON profile_publicinfo.username = #tempSearch.usernameINNER JOIN dbo.settings_privateinfo ON settings_privateinfo.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.profile_coolfacts ON profile_coolfacts.username = profile_publicinfo.username FULL OUTER JOIN dbo.profile_profilephoto ON profile_profilephoto.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.settings_username ON settings_username.username = profile_publicinfo.usernameWHERE (profile_publicinfo.username IN (SELECT username FROM #tempSearch))AND #tempSearch.tempID >= @first_id'
SELECT @paramlist = '@first_id int'EXEC sp_executesql @sql, @paramlist, @first_id
I need to get the tempID from the tempSearch table in order to compare it with @first_id
When i run this i get the same username repeated like 30 times then it moves over to the next, when i debug the #tempSearch it looks fine, just the users that are suppose to be there.
View 1 Replies
View Related
Jun 17, 2004
Hi
I think I have installed MSDE sucessfully. The new servie is running, but how can I test ifit is working? Can I place my files anywhere on the system?
All help appreciated
View 1 Replies
View Related
Apr 25, 2006
I am using the following C#...
SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class loader = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
loader.ConnectionString = "PROVIDER=SQLOLEDB; Server=(local); database=TestDB; user id=test; password=password";
loader.ErrorLogFile = "C:log.txt";
loader.SchemaGen = true;
loader.SGDropTables = true;
loader.CheckConstraints = true;
loader.Execute(
"C:xsd.xsd"/* this file below */,
"C:xml.xml"
);
To generate tables in the database using the following xml schema...
(file: xsd.xsd...)
My problems is that it doesn't generate the tables and I am following the SQLXML2.0 documentation as closely as possible.
Does anyone know what I have to do to get it to generate the tables? Running it doesn't even produce an error.
Cheers,
Matt.
View 1 Replies
View Related
May 3, 2002
OK so I'm using dynamic sql to enable me to have a variable for the order by, but now it's giving me the following error
Syntax error converting character string to smalldatetime data type.
Any help is very much appreciated
Code follows
CREATE PROCEDURE spBattingAve1
@cid datetime,
@cid1 datetime,
@sid nvarchar(10)
AS
DECLARE @query nvarchar(4000)
SET @query = 'SELECT MAX(t_batting.runs) AS BestRuns,
COUNT(t_batting.dnb) AS DidNotBat, t_player.surname, t_player.firstname,
t_batting.player_id,
Sum(t_batting.runs) as SumOfruns, COUNT (*) AS Games,
COUNT (t_batting.notout) as nout,
(Sum(runs)/(COUNT(*)-(COUNT(notout)+COUNT(dnb)))) as AverageRun,
(COUNT(*)-COUNT(dnb)) AS Innings,
(SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 50 AND runs <100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overfifty,
(SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overton
FROM t_batting
INNER JOIN t_game ON t_batting.game_id = t_game.game_id
INNER JOIN t_player ON t_batting.player_id = t_player.player_id
WHERE t_batting.player_id = t_player.player_id
AND t_game.date >= '+@cid+' And t_game.date <= '+@cid1+'
GROUP BY t_batting.player_id, t_player.surname, t_player.firstname, t_player.player_id
HAVING (COUNT(*)-(COUNT(notout)+COUNT(dnb))) <> 0
order by '+@sid+'
DESC'
exec (@query)
View 1 Replies
View Related
Jun 21, 2001
In SQL 7.0, when i use the below query in query analyzer, I got an error message. why?
======================
Set Identity_insert on
UPDATE Table1
SET no = 3
WHERE no = 4
Set Identity_insert off
go
Error message
=============
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'off'.
View 2 Replies
View Related
May 1, 2006
Hi,
I am on a box with 4 GB of memory that only runs SQL Server. SQL Server is using 1.7 GB as per task manager. There are a lot of databases on this box. I looked at the boot.ini and /3GB was not set. I have done this at least 50 times before: turn on the /3GB switch and SQL Server will use up to 2.7 GB per task manager. (I know it is actually 2GB and 3GB but it seems that in task manager it shows up as 1.7-1.8 and 2.7-2.8)
Anyway back to the story. So I set /3GB, rebooted the server and SQL Server still only consumes up to 1.7GB. I realize that SQL Server might not actually need more memory, but I have a distinct feeling that it wants more but is being constrained; that is, the the /3GB switch is not working. More relevant information:
1) the boot.ini line:
multi(0)disk(0)rdisk(0)partition(1)WINNT="Microsoft Windows 2000 Advanced Server" /fastdetect /3GB
2) max server memory (MB) = 3000
3) min server memory (MB) = 3000
4) awe enabled = 0
5) @@version = 8.00.2039 which is SQL Server 2000 SP4
Has anyone seen this before? Any ideas on how to troubleshoot this?
View 1 Replies
View Related
Oct 16, 2006
Hi all:
Because me not this functioning the following one query.
simply me not the function respects min, because?, that I am doing badly?
Select Distinct(Min(c.gentime)),
a.CardNumber,a.CardHolderId,a.Deleted,
b.RecordId,b.FirstName,b.LastName,b.Deleted,b.Note 4,
c.param3,c.param2,c.param1,c.recvtime,c.gentime,c. link1,c.link2,c.link3,c.deleted,c.recordid,c.seqid
From Card a,
CardHolder b,
History c
Where ((a.cardholderid = b.recordid)And(b.recordid=c.link3))
And(a.Deleted = 0)And (b.Deleted = 0)And(c.deleted = 0)
And(a.cardnumber Between 1500 And 1600)
And(b.note4 <> 'Mantenimiento')
And(c.RecvTime >= CONVERT(DATETIME, '2006-10-01 00:00:00', 102))And(c.RecvTime <= CONVERT(DATETIME, '2006-10-12 23:59:59', 102))
And(c.Link1=10)
Group By a.CardNumber, a.CardHolderID, a.Deleted,
b.RecordID, b.Deleted, b.FirstName, b.LastName, b.Note4,
c.Deleted, c.RecvTime, c.GenTime, c.Param1, c.Param2, c.Param3, c.Link1, c.Link2, c.Link3, c.Link4,c.recordid,c.seqid
Order By a.CardNumber,c.recvtime
Thanks.:confused:
View 6 Replies
View Related
Mar 2, 2006
hi..why this SP is not working that giving me error like
Server: Msg 2714, Level 16, State 1, Procedure test_E, Line 12
There is already an object named '#emp' in the database.
my one of the developer used this every where... what should i do if i dont wanna to change any of the code...i wanna some how work this SP...
create proc test_E
as
select * into #emp
from (select * from emp) p
select * from #emp
drop table #emp
select * into #emp
from (select * from dept) p
select * from #emp
drop table #emp
T.I.A
Papillon
View 5 Replies
View Related
Apr 11, 2006
select replace(local_notes,char(13),' ') from locals
Not Working ......
View 12 Replies
View Related
Sep 20, 2006
declare @table table(ad_num varchar(20), ad_str1 varchar(20))
insert @table
select '20', '20 apple avenue' union all
select '20', 'apple avenue'
select * from @table
if (select * from @table where substring(ad_str1, 1, 1) like '[0-9]'))
print 'address starts with a number'
else
print 'address starts with a letter'
i want to write a code that prints a msg if the string starts with a number else print another msg.
View 20 Replies
View Related
Sep 26, 2007
In SQL2000 Enterprise manager, there is always an indication of WHICH server & database an object is opened from when it has focus.
So when I'm working on the same table in test and production servers I can tell at a glance what it belongs to.
I cannot seem to duplicate this in any way in SQL Manager 2005 Express which could lead to all sorts of trouble and really slows me down.
Have I missed something really simple in setup or use of the product?
TIA,
Richard
View 2 Replies
View Related
Nov 20, 2007
Dear All,
please explain me what is going inside with this query....
selectchar(94 * RAND() + 33)
i saw this in FAQ, combining several same queries to generate random passwords.
i didn't get what is char(94* rand()+33)
thank you very much for your valuble time
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 10 Replies
View Related
