"where" in DTS

i try use "where" to filter record for result but when i run , there is a message error task.

select flattened

(select * from predict ([model name].[table name], include_statistic) where [$support] >0 ) as ph

from

[model name]

prediction join

.....

can onebody help me?

What is the error?|||It could be that you need to say "INCLUDE_STATISTICS" rather than "INCLUDE_STATISTIC"|||

error that it messesage: " there an error occur in task". no more information to know what error and where error in query.

what's different between INCLUDE_STATSTICS and INCLUDE_STATSTIC?

|||

The difference is one is a keyword, and the other is an error.

You can try to execute your query in SQL Management Studio to see the full error message, although it should be available from SSIS as well.

If you are doing this on the pipeline and you want to validate your DMX syntax simply eliminate the PREDICTION JOIN clause,

e.g.

select flattened

(select * from predict ([model name].[table name], include_statisticS) where [$support] >0 ) as ph

from

[model name]

|||

sory, but first, i writed like that but still have error. and it didn't message with an clear error to know what error. how can i do now?

i try a model to show other products when someone choose a product. my result have many record that same.

e.g:

productid otherProductid

1 34

1 74

1 89

2 34

2 74

2 89

3 34

3 74

3 89

4 56

4 101

4 73

....

what wrong with my model and how i should do to my event?

thanks

|||

This is not an error, rather a feature. This occurs when your model doesn't contain rules for the data in your input. The system is designed that if you ask for 5 recommendations, you will get 5 recommendations. If the input you provide doesn't resolve into any rules (or enough rules) you will get the most popular results. I.e. it looks like item 34 is the most popular in your training set.

There are a few ways to alleviate this issue. One is to change the minimum_support and minimum_probabilty such that you generate more rules. Another is to filter results that are not based on rules. A description on how to do this is at http://blogs.msdn.com/jamiemac/archive/2006/04/19/579409.aspx

Thanks

"distinct" in select query

when i use select :

select flattened

(select productid, $support from [model name].[table] )......

i have result with many record same.

so i write :

select flattened

(select distinct productid, $support from [model name].[table] )......

but when i run, it's error. ( don't know what error).

how can i do to get table record with not same record (loop)

note : it write in DTS and i use sql 2000

I assume you mean an Analysis Services query.

Try: SELECT DISTINCT [model name].[table].[productid] FROM [model name]

This should return the distinct list of products

|||

i can't use query like you show.

i want you "distinct " in DTS syntax like that:

select flattened

(select productid, productname, categoryid ,[$probability] from predict([model name].[table name], incluse_statistics) where productid > 1000)

from [model name]

prediction join

shape{....}

appen{....}

on....

How can i put Distinct to have different record?

|||

You can't use DISTINCT in that context. However, I think what you want is to be able to discriminate the records by the user. In this case, the selection will return the product id at most once per input case, but since you don't include an external id, there's no way to discriminate which prediction is for which customer.

I think you need something like

SELECT FLATTENED t.CustomerID, (select ....

This query will return the id from the source data query along with the results of the prediction.

%LIKE% GETDATE() query

Hi,
I'm building an ASP application in Dreamweaver MX2004 on the application the
user is aloud to inout a record. The following page has two recordsets, one
that returns the data that the user enterred in the previuos record and
another that identifies users who have requested that kind of information.
The first recordset however is where the problem lies as currently the
recordset is built based on two values - USERID and ADREFERENCE. The problem
with this is that it could return more than one record and then everything
becomes . So I've thought about adding an additional parameter which
would be and Dateposted = GETDATE() obviously this wont work though as it
will be looking for a record posted the exact time, the question is would -
Dateposted %LIKE% GETDATE work, or is therer an alternative - ideally withou
t
any complex SQL programming as i'm totaaly new to SQL
thanksYou can use DateDiff(dd, Dateposted , getdate())= 0 to do it.
Perayu
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:21F267E8-E6C6-4B78-AF25-CDB0B68B7137@.microsoft.com...
> Hi,
> I'm building an ASP application in Dreamweaver MX2004 on the application
> the
> user is aloud to inout a record. The following page has two recordsets,
> one
> that returns the data that the user enterred in the previuos record and
> another that identifies users who have requested that kind of information.
> The first recordset however is where the problem lies as currently the
> recordset is built based on two values - USERID and ADREFERENCE. The
> problem
> with this is that it could return more than one record and then everything
> becomes . So I've thought about adding an additional parameter
> which
> would be and Dateposted = GETDATE() obviously this wont work though as it
> will be looking for a record posted the exact time, the question is
> would -
> Dateposted %LIKE% GETDATE work, or is therer an alternative - ideally
> without
> any complex SQL programming as i'm totaaly new to SQL
> thanks|||You can't use the LIKE comparison operator with datetime values, but you can
use BETWEEN. For example, if you wanted to find records within the last 5
minutes
SELECT ...
WHERE [Dateposted] BETWEEN DATEADD(mi, -5, GETDATE()) AND GETDATE()
For other ideas, take a look at the DATEADD and DATEDIFF functions in Books
Online.
"GTN170777" wrote:

> Hi,
> I'm building an ASP application in Dreamweaver MX2004 on the application t
he
> user is aloud to inout a record. The following page has two recordsets, on
e
> that returns the data that the user enterred in the previuos record and
> another that identifies users who have requested that kind of information.
> The first recordset however is where the problem lies as currently the
> recordset is built based on two values - USERID and ADREFERENCE. The probl
em
> with this is that it could return more than one record and then everything
> becomes . So I've thought about adding an additional parameter whi
ch
> would be and Dateposted = GETDATE() obviously this wont work though as it
> will be looking for a record posted the exact time, the question is would
-
> Dateposted %LIKE% GETDATE work, or is therer an alternative - ideally with
out
> any complex SQL programming as i'm totaaly new to SQL
> thanks|||> The first recordset however is where the problem lies as currently the
> recordset is built based on two values - USERID and ADREFERENCE. The
> problem
> with this is that it could return more than one record and then everything
> becomes .
If you are using IDENTITY then after the insert you could select
SCOPE_IDENTITY() and keep track of that. Now, just
SELECT UserID, AdReference, DatePosted
FROM your_table
WHERE identity_column = whatever;
If you are not using IDENTITY and the key is UserID/AdReference/DateTime
then you could say:
SELECT TOP 1 UserID, AdReference, DatePosted
FROM your_table
ORDER BY DatePosted DESC;

> the question is would -
> Dateposted %LIKE% GETDATE work
No, there are all kinds of issues with this. First off, GETDATE() returns a
date, not a string. What you see when you SELECT GETDATE() or PRINT is not
how it's stored internally. So, you'd have to convert both sides to a
string, and this is not going to work well at all. Additionally, GETDATE()
when you run the select afterward is not going to contain the same value as
GETDATE() was when the insert happened. You could restrict the comparison
to the hour or to the day, but if they added two posts in that timeframe,
you would still get multiple rows back.
A|||"Perayu" wrote:
> You can use DateDiff(dd, Dateposted , getdate())= 0 to do it.
> Perayu
>
--The problem than can happen with this is
SELECT DATEDIFF(dd, '2006-01-24 23:59:59', '2006-01-25 00:00:01')
/*Returns 1, even though the two dates are only 2 seconds apart. Not a bug;
DATEDIFF counts the number of dividing boundaries crossed going from the
first date to the second date for the specified type of date differential */|||Thanks Mark, should have thought about that!!
"Mark Williams" wrote:
> You can't use the LIKE comparison operator with datetime values, but you c
an
> use BETWEEN. For example, if you wanted to find records within the last 5
> minutes
> SELECT ...
> WHERE [Dateposted] BETWEEN DATEADD(mi, -5, GETDATE()) AND GETDATE()
> For other ideas, take a look at the DATEADD and DATEDIFF functions in Book
s
> Online.
> --
>
> "GTN170777" wrote:
>|||But what if they had multiple posts in the last 5 minutes? You should be
designing your system so that you can identify a single row by more than the
fact that it fell within a certain time range. No matter how narrow you
make your window, it is possible that either (a) multiple rows will match or
(b) your window is too small to even capture the last insert.
> Thanks Mark, should have thought about that!!
> "Mark Williams" wrote:
>|||To compare datetime fields and ignore the time, do this
WHERE CONVERT(CHR(8), Dateposted ,112) = CONVERT(CHAR(8),GetDate(),112)
this would be the same as the following if Dateposted = 1/24/2006
WHERE '20060124' = '20060125'
But this probably would not solve your problem if the user could add more
than one record per day. The best solution is to use SCOPE_IDENTITY() as a
previous poster suggested.
kevin
"GTN170777" wrote:

> Hi,
> I'm building an ASP application in Dreamweaver MX2004 on the application t
he
> user is aloud to inout a record. The following page has two recordsets, on
e
> that returns the data that the user enterred in the previuos record and
> another that identifies users who have requested that kind of information.
> The first recordset however is where the problem lies as currently the
> recordset is built based on two values - USERID and ADREFERENCE. The probl
em
> with this is that it could return more than one record and then everything
> becomes . So I've thought about adding an additional parameter whi
ch
> would be and Dateposted = GETDATE() obviously this wont work though as it
> will be looking for a record posted the exact time, the question is would
-
> Dateposted %LIKE% GETDATE work, or is therer an alternative - ideally with
out
> any complex SQL programming as i'm totaaly new to SQL
> thanks|||That sounded interesting, so I added IsLike(pattern) to my TDate UDT at
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
Here is sample of usage:
DECLARE @.t table(
Date datetime
);
insert @.t SELECT '1/1/2005'
insert @.t select '1/2/2005'
insert @.t select '9/1/2005'
insert @.t select '4/4/2005'
insert @.t select '4/12/2005'
insert @.t select '10/20/2005'
insert @.t select '11/22/2005'
insert @.t select '12/25/2005'
-- Select any date in 2005 that has a day in the 20's.
select Date
from @.t
where TDate::FromSqlDateTime(Date).IsLike(''/2?/2005') = 1
-- Select any date that only has 1 digit in the month and 1 digit in the
day.
select Date
from @.t
where TDate::FromSqlDateTime(Date).IsLike('?/?/'') = 1
OUTPUT
Date
2005-10-20 00:00:00.000
2005-11-22 00:00:00.000
2005-12-25 00:00:00.000
Date
2005-01-01 00:00:00.000
2005-01-02 00:00:00.000
2005-09-01 00:00:00.000
2005-04-04 00:00:00.000
Kinda .
--
William Stacey [MVP]
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:21F267E8-E6C6-4B78-AF25-CDB0B68B7137@.microsoft.com...
| Hi,
| I'm building an ASP application in Dreamweaver MX2004 on the application
the
| user is aloud to inout a record. The following page has two recordsets,
one
| that returns the data that the user enterred in the previuos record and
| another that identifies users who have requested that kind of information.
| The first recordset however is where the problem lies as currently the
| recordset is built based on two values - USERID and ADREFERENCE. The
problem
| with this is that it could return more than one record and then everything
| becomes . So I've thought about adding an additional parameter
which
| would be and Dateposted = GETDATE() obviously this wont work though as it
| will be looking for a record posted the exact time, the question is
would -
| Dateposted %LIKE% GETDATE work, or is therer an alternative - ideally
without
| any complex SQL programming as i'm totaaly new to SQL
|
| thanks