Association Rules

1) I use the identifier of transaction and attribute in one table.

Do I can to build a association rules structure without the use of the nested tables?

I tried - did not turn out...

2) As it is necessary to use a main and child table, can not build a prediction query.

When I try to add the predict column in a criteria/argument (Field=PredictSupport), i'll given message:

"Nested table column cannot be used as an argument in a data mining function."

I can not use other columns, because they are not predicable.

You have to have a nested table, but you can use the same table as the case and nested tables. Just mark the transaction key as the key for the "case" table, and the transaction item as the key for the "nested" table.

In your prediction query you would predict the nested table ( you are essentially predicting which rows will be there). For example, if your model was like this:

TransID LONG KEY,
Products TABLE PREDICT
(
ProductName TEXT KEY
)

You would issue a statement like

SELECT Predict(Products, 5) FROM MyModel PREDICTION JOIN ....

|||

Yes, turned out to build a model! Thanks!
But at an attempt to take advantage of builder of queries, there is an error:

Unable to retrieve the list of distinct states for mining model column Utp Doc Id.

ADDITIONAL INFORMATION:

Error (Data mining): The specified DMX column was not found in the context at line 1, column 17. (Microsoft SQL Server 2005 Analysis Services)

Without a builder - a query works :)
And yet question - as possible to apply a function, as though, PredictSupport? She needs a column, and for me does not turn out to use her from a table...

P.S.: I am sorry for my English =)

Assigning a variable a table value

Hi I'm new to SQL and I'm trying to build a store procedure. What I'm trying to find out is how to assign a variable a value from a table.

So, I declare a variable:
DECLARE @.variable int
Then I want to assign a single int value to that variable that is already in a table. For instance the id value from a single row in a table.

I've tried SELECT INTO and SET, but nothing has worked so far and I'm lost after searching for a few hours.

Thanks in advance for any help

You need to use Select. You can assign multiple variables at the same time, but you cannot mix assignments with result sets. You can use either Set or Select to assign literal or constant values to a variable. Set only allows one variable at a time.

You can also use variables for values in a select statement.

Here is a full example:

Declare @.variable int,

@.pkey int

Set @.pkey = 42

Select @.variable = col1

From myTable

Where pkCol = @.pkey|||Thanks for your time and help it worked perfect.

ASP/SQL Query Build - Myth Breaking

Hi Guys

I don't want to keep asking for your help all the time on each individual
query, so could you please help me to break the myths on the following:

1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET.
ACCOUNTS includes all of the usual details for customers and suppliers, eg
the account code (as ACCOUNTID), the account name (as NAME), etc.
ACCOUNTBUDGET basically holds a transaction line for each month and it's
corresponding year to store the turnover for that month, eg one row contains
ACCOUNTID, PERIOD (ie the month), YEAR and TURNOVER.

Now a lot of the SQL 6.5 tables that I deal with are in this vein and the
usual query is that I want to list all of the ACCOUNTIDs and NAMEs that
exist in the ACCOUNTS table and then show for example what their TURNOVER is
for a applicable PERIOD and YEAR, which are all held in the ACCOUNTSBUDGET
table.

Now if I do a quick query using MS Query all I get are rows that have
related values in both the ACCOUNTS and ACCOUNTSBUDGET table when I have
specified say a certain PERIOD and YEAR.

The main point of my current reporting problem is that I want to show all
the ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for a
particular PERIOD and YEAR.

I'm positive that I have to create a 2 step query/join, but I don't know how
to do it. What is the method? People in this NG, can rattle one up in
seconds, but I just don't see the logic. Can you help me with this query
and let me know how you manage to fathom it.

2) Are there any good web sites that explain in kiddie form how to do this
sort of thing?

I really appreciate your help on this.

Regards

LaphanPut your Financial Calendar into a table and join that into your query.

CREATE TABLE Accounts (accountno INTEGER PRIMARY KEY, accountname
VARCHAR(30) NOT NULL UNIQUE)

CREATE TABLE FinancialCalendar (yearno INTEGER CHECK (yearno BETWEEN 2000
AND 2100), periodno INTEGER CHECK (periodno BETWEEN 1 AND 12), PRIMARY KEY
(yearno,periodno))

CREATE TABLE Budgets (accountno INTEGER REFERENCES Accounts (accountno),
yearno INTEGER, periodno INTEGER, amount NUMERIC(10,2) NOT NULL, FOREIGN KEY
(yearno,periodno) REFERENCES FinancialCalendar (yearno,periodno))

SELECT A.accountno, A.accountname, C.yearno, C.periodno,
COALESCE(B.amount,0) AS amount
FROM FinancialCalendar AS C
CROSS JOIN Accounts AS A
LEFT JOIN Budgets AS B
ON A.accountno = B.accountno
AND B.yearno = C.yearno
AND B.periodno = C.periodno
WHERE C.yearno BETWEEN 2001 AND 2004

I recommend Celko's SQL Puzzles and Answers if you want to expand your
repertoire of query techniques:
http://tinyurl.com/353dw

--
David Portas
SQL Server MVP
--

ASP.net newbie trying to build a forum

Hi,
I'm an absolute newbie to asp.net... and recently got a start by visiting the beginner developer learning center at the msdn website. I have gone through all of the videos, and now I'm trying to build my first asp.net website... which would be an imaginary forum. I have the following tables in the database used in my application:
Categories
CategoryID int
CategoryName nvarchar(50)
CategoryDescription nvarchar(200)
Threads
ThreadID bigint(8)
CategoryID int
Subject nvarchar(50)
PostedDate datetime
PostedBy nvarchar(10)
Messages
MessageID bigint(8)
ThreadID bigint(8)
MessageBody nvarchar(200)
ReplyToID bigint(8)
User char(10)
You get the idea about what I'm trying to do.
Now, I have successfully written Categories.aspx, and Threads.aspx to display the sample data which I added. The problem is with the messages.aspx. I have no idea how to output data which would give me a means to sort and indent the messages according to the way the users would reply. I mean... users might reply to a message, and another might reply to the reply of that message... creating one big hierarchical mess. So I'm stuck... I can't build my imaginary forum.
Anyone experienced with writing code for forums with SQL server as the back end?
I am hoping to learn some new things about SQL server... maybe... XML? Which would solve my problem? I am already a little familiar with XML and XSL.
Many thanks in advance.
Mohamed Shafiee.
Hello Mohamed,
While this isn't an ASP.NET focused group, and while I don't believe SQLXML
is going to buy you anything here, here's an example that might get you started.
use scratch
go
-- make the message table
drop table dbo.messages
go
create table dbo.messages(
MessageID bigint primary key,
ThreadID bigint not null,
MessageBodynvarchar(200) not null,
ReplyToID bigint null foreign key references dbo.messages(messageID),
PostedBy varchar(50) not null default suser_sname())
go
set nocount on
go
-- some dummy messages
insert into dbo.messages(messageID,threadID,messageBody,ReplyT oID)
values (1,1,'1',null)
insert into dbo.messages(messageID,threadID,messageBody,ReplyT oID)
values (2,1,'1-1',1)
insert into dbo.messages(messageID,threadID,messageBody,ReplyT oID)
values (3,1,'1-2',1)
insert into dbo.messages(messageID,threadID,messageBody,ReplyT oID)
values (4,1,'1-2-1',3)
insert into dbo.messages(messageID,threadID,messageBody,ReplyT oID)
values (5,1,'1-2-2',3)
insert into dbo.messages(messageID,threadID,messageBody,ReplyT oID)
values (6,1,'1-2-2-1',5)
insert into dbo.messages(messageID,threadID,messageBody,ReplyT oID)
values (7,1,'1-2-2-2',5)
insert into dbo.messages(messageID,threadID,messageBody,ReplyT oID)
values (8,1,'1-2-1-1',4)
go
-- @.tid bigint is the ThreadID selected
-- @.rtm bigint is the Message user wants to reply to
drop procedure dbo.GetThreadForMessage
go
create procedure dbo.GetThreadForMessage(@.tid bigint, @.rtm bigint)
as begin
-- Get all the messages in this thread, "nested" by level
;with cte1 as (
select m1.messageID,m1.threadID,m1.MessageBody,m1.ReplyTo ID,m1.PostedBy,
cast(0 as int) as lvl
from dbo.messages m1
where m1.threadID = @.tid and m1.ReplyToID is null
union all
select mr.messageID,mr.threadID,mr.MessageBody,mr.ReplyTo ID,mr.PostedBy,
cte1.lvl + 1
from dbo.messages mr
join cte1 on mr.ReplyToID = cte1.MessageID
where mr.threadID = @.tid),
-- Get the reponses leading to the replying-to message.
cte2 as (
select replyToID from dbo.messages where messageID = @.rtm
union all
select m2.replyToID from dbo.messages m2
join cte2 on m2.messageID = cte2.replyToID
)
-- Last Message in thread
select * from cte1
where cte1.messageID = @.rtm
union all
-- Other messages in thread
select * from cte1
where cte1.messageID in (select ReplyToID from cte2)
union
-- Base message
select * from cte1 where cte1.replyToID is null
order by cte1.lvl desc
for xml path('message'),root('messages'),type
end
go
exec GetThreadForMessage 1,7
go
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

ASP.net newbie trying to build a forum

Hi,
I'm an absolute newbie to asp.net... and recently got a start by visiting th
e beginner developer learning center at the msdn website. I have gone throug
h all of the videos, and now I'm trying to build my first asp.net website...
which would be an imaginary forum. I have the following tables in the datab
ase used in my application:
Categories
CategoryID int
CategoryName nvarchar(50)
CategoryDescription nvarchar(200)
Threads
ThreadID bigint(8)
CategoryID int
Subject nvarchar(50)
PostedDate datetime
PostedBy nvarchar(10)
Messages
MessageID bigint(8)
ThreadID bigint(8)
MessageBody nvarchar(200)
ReplyToID bigint(8)
User char(10)
You get the idea about what I'm trying to do.
Now, I have successfully written Categories.aspx, and Threads.aspx to displa
y the sample data which I added. The problem is with the messages.aspx. I ha
ve no idea how to output data which would give me a means to sort and indent
the messages according to the way the users would reply. I mean... users mi
ght reply to a message, and another might reply to the reply of that message
.. creating one big hierarchical mess. So I'm stuck... I can't build my ima
ginary forum.
Anyone experienced with writing code for forums with SQL server as the back
end?
I am hoping to learn some new things about SQL server... maybe... XML? Which
would solve my problem? I am already a little familiar with XML and XSL.
Many thanks in advance.
Mohamed Shafiee.Hello Mohamed,
While this isn't an ASP.NET focused group, and while I don't believe SQLXML
is going to buy you anything here, here's an example that might get you star
ted.
use scratch
go
-- make the message table
drop table dbo.messages
go
create table dbo.messages(
MessageID bigint primary key,
ThreadID bigint not null,
MessageBody nvarchar(200) not null,
ReplyToID bigint null foreign key references dbo.messages(messageID),
PostedBy varchar(50) not null default suser_sname())
go
set nocount on
go
-- some dummy messages
insert into dbo. messages(messageID,threadID,messageBody,
ReplyToID)
values (1,1,'1',null)
insert into dbo. messages(messageID,threadID,messageBody,
ReplyToID)
values (2,1,'1-1',1)
insert into dbo. messages(messageID,threadID,messageBody,
ReplyToID)
values (3,1,'1-2',1)
insert into dbo. messages(messageID,threadID,messageBody,
ReplyToID)
values (4,1,'1-2-1',3)
insert into dbo. messages(messageID,threadID,messageBody,
ReplyToID)
values (5,1,'1-2-2',3)
insert into dbo. messages(messageID,threadID,messageBody,
ReplyToID)
values (6,1,'1-2-2-1',5)
insert into dbo. messages(messageID,threadID,messageBody,
ReplyToID)
values (7,1,'1-2-2-2',5)
insert into dbo. messages(messageID,threadID,messageBody,
ReplyToID)
values (8,1,'1-2-1-1',4)
go
-- @.tid bigint is the ThreadID selected
-- @.rtm bigint is the Message user wants to reply to
drop procedure dbo.GetThreadForMessage
go
create procedure dbo.GetThreadForMessage(@.tid bigint, @.rtm bigint)
as begin
-- Get all the messages in this thread, "nested" by level
;with cte1 as (
select m1.messageID,m1.threadID,m1.MessageBody,m1.ReplyToID,m1.PostedBy,
cast(0 as int) as lvl
from dbo.messages m1
where m1.threadID = @.tid and m1.ReplyToID is null
union all
select mr.messageID,mr.threadID,mr.MessageBody,mr.ReplyToID,mr.PostedBy,
cte1.lvl + 1
from dbo.messages mr
join cte1 on mr.ReplyToID = cte1.MessageID
where mr.threadID = @.tid),
-- Get the reponses leading to the replying-to message.
cte2 as (
select replyToID from dbo.messages where messageID = @.rtm
union all
select m2.replyToID from dbo.messages m2
join cte2 on m2.messageID = cte2.replyToID
)
-- Last Message in thread
select * from cte1
where cte1.messageID = @.rtm
union all
-- Other messages in thread
select * from cte1
where cte1.messageID in (select ReplyToID from cte2)
union
-- Base message
select * from cte1 where cte1.replyToID is null
order by cte1.lvl desc
for xml path('message'),root('messages'),type
end
go
exec GetThreadForMessage 1,7
go
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

ASP.NET connection to SQL Server

I have a page that runs a stored procedure in SQL server. It works fine when I run the app through Visual Studio but when I build the site and try to run it from the page, it will not work. Any ideas?What's the meaning of "it will not work"? Is it a connection failure? Or incorrect result? Or Command Timeout, ect.?