Any Way To Ignore Data On Inserts That Violate A Rule?

Apr 18, 2012

I am currently importing tick data for a stock. Let's say my table structure is like this:

CREATE TABLE tick
(
tickId bigint identity(1,1) primary key
, tickTime datetime
, price money
)

If the stream of data I get resembles:

'4/17/12 2:00:00.000', 10.00
'4/17/12 2:00:02.000', 10.02
'4/17/12 2:00:01.000', 10.01
'4/17/12 2:00:03.000', 10.03

I want my table to look like this:

1, '4/17/12 2:00:00.000', 10.00
2, '4/17/12 2:00:02.000', 10.02
3, '4/17/12 2:00:03.000', 10.03

Essentially ignoring the out of place '4/17/12 2:00:01.000' record. What is the least expensive way to accomplish this?

View 6 Replies


ADVERTISEMENT

Differences Between Mined Data And Queried Data : Association Rule

Sep 22, 2006

Dear all,

I have a table containing call records, and made a mining model from that table only. The model has 3 columns : calling_number, called_number, and target_operator, using Association Rule algorithm. The key is calling_number, input was operator, and predicted column called_number.

The result shows no rule, but there are results with item-set size of 1 (column) and 2 (column). On the top record of the result, SQL Server says there are 1891 support for called_number = 1891 and operator = 'INDOSAT'.

I queried the table with this query

SELECT DISTINCT calling_number
FROM call_records
WHERE called_number = '07786000815'
AND target_operator = 'INDOSAT';

It returns 2162 records instead of 1891. If I removed the DISTINCT qualifier, SQL Server returns 2159 records. Why is this differences with the result of mining?

Thank you,

Bernaridho

View 8 Replies View Related

Rule Of Thumb About Number Of Attributes And Data Points?

Nov 16, 2006

This is a general question about data modeling. I'm more curious than anything else.

There is much talk about over-training data model, and I'm sure there are under training as well. As a rule of thumb, depending on the algorithm, what is a good ratio of attributes vs data points?

-Young K

View 4 Replies View Related

Master Data Services :: Maintaining Contiguous Code IDs By Applying Business Rule?

Feb 29, 2012

I deleted some records out of an entity, I'd like to keep the Codes as contiguous and incremental, meaning no breaks between the code numbers.I created a business rule and applied it but codes remain the same.

I used the "Default to a generated Value" action, then selected the Code attrib. --Saved.

Then back to the Entity, I applied business rules. But nothing seemed to have happened. As there was no change in codes.

There are no validation errors either.

View 2 Replies View Related

BCP To Ignore Newline Characters Within The Data?

Mar 9, 2000

I exporting a table of comments. There are some line returns in the comments. Some of these data are paragraphs of data! For some reason, when I am exporting the data, it treats the line return within the comment column as a new record. I am using a -c character data type so (newline character) is the row terminator. How do I get the BCP OUT to ignore a newline character within a record?

For example:
ID~Comment
-- -------
1~This is a comment
2~Hi,how are (user hit carriage)
you (you is part of next row in bcp out)
3~Next record

Thanks!

Joyce

View 1 Replies View Related

Ignore First X Rows In Excel Data Source

Aug 31, 2006

Hi all,

Is it possible to ignore the first x rows of the Excel file in an Excel Data Source component ??

Séb.

View 3 Replies View Related

SQL 2012 :: Ignore ANSI Characters In Data Comparison?

Aug 18, 2014

I am comparing two fields one from our legacy table and one in our new table structure that should have identical text data. The new field has an assortment of ANSI characters where the legacy data did not have these. Is there anything I can do that will ignore all ansi character differences? The only route I can think of is just do a replace on each ANSI type on the new column but there are quite a few character types.

View 4 Replies View Related

T-SQL (SS2K8) :: How To Ignore String Or Binary Data Would Be Truncated

Jan 16, 2015

I have a script to be used to backup a specific table in a weekly basis, here is the approach what I take:

1. script the source table's schema to a create
2. the new script:

If not exists (select * from sysobjects where name='EventlogHistory' and xtype='U')
CREATE TABLE [dbo].[EventlogHistory](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[EventType] [varchar](10) NOT NULL,
[IP] [varchar](50) NOT NULL,
[UserLogon] [varchar](30) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
ArchivedOn datetime default getdate())
insert into EventlogHistory

It throws me the error message saying "String or binary data would be truncated" which is nonsense to me, I need to let sql ignore the error, it is ridiculous to do a max(len()) to find out as it should never happen, right?

View 9 Replies View Related

T-SQL (SS2K8) :: Ignore First Row In Data File In Bulk Insert?

Feb 17, 2015

I use bulk insert to fill a table

I use code bellow

bulk insert dbo.test
from 'c: est.txt'
with(FIRSTROW=2,FORMATFILE='c: est.xml'
go

but data inserted to table start from 3throw.

View 2 Replies View Related

Integration Services :: How To Ignore Eventual Data Flow Failure

May 11, 2015

Data flow A take data from the Excel File A, Data B from Excel File B, Data C from Excel File C. What I'd like to do is that if something goes wrong on Data Flow A I would be alerted but the package should continue to running. The same for the DataFlow B, if A it's ok go on, if B fail send me the mail but continue until the end (so running the Data Flow C).

View 2 Replies View Related

How To Do Multiple Inserts For One Row Of Data?

Aug 21, 2006

I need to insert multiple rows for input rows that meet certain conditions.

My input data is as follows.

ZIPCode, Plus 4, Range, City, State

What I need to happen is that if there is a value in the range column that is > 0 then I need to insert a row for every range item.

For instance say I have the following data.

54952, 1001, 10, Menasha, WI

What I need imported is :

54952, 1001, Menasha, WI
54952, 1002, Menasha, WI
54952, 1003, Menasha, WI
54952, 1004, Menasha, WI
54952, 1005, Menasha, WI
54952, 1006, Menasha, WI
54952, 1007, Menasha, WI
54952, 1008, Mensaha, WI
54952, 1009, Mensaha, WI
54952, 1010, Mensaha, WI
54952, 1011, Mensaha, WI

Any help in pointing me in the right direction would be great. Thanks for your help in advance.

View 2 Replies View Related

SqlDataSource1.Insert() Inserts Data Twice

Aug 22, 2006

Hi there,
I'm having a problem where the data taken from the textboxes is being inserted into a table twice. I looked online and someone on another forum was experiencing the same problem. His solution was making the "submitdsabledcontrols" attribute of the form false. That hasn't fixed the issue for me.
Below is my code. I'm still learning all this so this is just a test page, hence the wacky naming conventions.
When the button click calls Button1_Click, all it runs is "SqlDataSource1.Insert()"
Any ideas?
<%@ Page EnableEventValidation="false" Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Default3.aspx.vb" Inherits="Default3" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="mainContent" Runat="Server">
<form id="form1" submitdisabledcontrols=false>
<br />
<asp:Label ID="Label1" runat="server">Name</asp:Label>
<asp:TextBox ID="NameTextBox" runat="server"></asp:TextBox>
<br />
<br />
<asp:Label ID="Label2" runat="server" Text="Label">Number</asp:Label>
<asp:TextBox ID="NumberTextBox" runat="server"></asp:TextBox>
<br />
<br />
<asp:Label ID="Label3" runat="server" Text="Label">Salary</asp:Label>
<asp:TextBox ID="SalaryTextBox" runat="server"></asp:TextBox>&nbsp;
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_click" />
</form>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:pubsConnectionString1 %>" DeleteCommand="DELETE FROM [Table1] WHERE [TableID] = @original_TableID" InsertCommand="INSERT INTO [Table1] ([Name], [Number], [Salary]) VALUES (@Name, @Number, @Salary)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Table1]" UpdateCommand="UPDATE [Table1] SET [Name] = @Name, [Number] = @Number, [Salary] = @Salary WHERE [TableID] = @original_TableID">
<DeleteParameters>
<asp:Parameter Name="original_TableID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="Number" Type="String" />
<asp:Parameter Name="Salary" Type="Decimal" />
<asp:Parameter Name="original_TableID" Type="Int32" />
</UpdateParameters>

<InsertParameters>
<asp:ControlParameter Name="Name" Type="String" ControlID="NameTextBox" />
<asp:ControlParameter Name="Number" Type="String" ControlID="NumberTextBox" />
<asp:ControlParameter Name="Salary" Type="decimal" ControlID="SalaryTextBox" />
</InsertParameters>

</asp:SqlDataSource>
 
</asp:Content>

View 1 Replies View Related

Textbox Inserts Data Into Database

Apr 13, 2008

I have a VERY simple program, it contains a datagrid, textbox and button
 I want it so when I insert something into the textbox and press the button it puts it into the database that is connected to the datagrid and displays it
 I have the following code and I cannot get it to work:
 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<script runat="server">
 Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs)
SqlDataSource1.InsertParameters.Add("@Name", TextBox1.Text)
End Sub
 </script>
<html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server">
<title>Untitled Page</title></head>
<body><form id="Form1" action="Default.aspx" runat="server">
<table>
<tr>
<td>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" SortExpression="ID" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString1 %>"DeleteCommand="DELETE FROM [Table] WHERE [ID] = @ID"
InsertCommand="INSERT INTO [Table] ([Name]) VALUES (@Name)"
ProviderName="<%$ ConnectionStrings:DatabaseConnectionString1.ProviderName %>"SelectCommand="SELECT [ID], [Name] FROM [Table]"
UpdateCommand="UPDATE [Table] SET [Name] = @Name WHERE [ID] = @ID">
<InsertParameters>
<asp:Parameter Name="Name" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="ID" Type="Int32" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="ID" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>
</td>
</tr>
<tr>
<td align="center">
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
<td>
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click1" />
</td></tr>
</table></form>
</body>
</html>

View 2 Replies View Related

Export Data From Table As SQL Inserts

Jul 21, 2005

Hi,

I have data in an old database I would like to capture for my new
system but I dont have the original insert scripts.  Is there a
tool (in SQL Server 2000 or thirdparty) that will help me export the
data as SQL inserts?

Thanks

jr.

View 1 Replies View Related

Data Getting Truncated As I Insert I.e. Instead Of Inserting Hello It Inserts 'H'

Jul 25, 2005

m inserting some data in big-sized field
and small-sized fields, data of varchar type, int's, dateTime, and
others... i am using something called a stored procedure to add data
into a table.. now when i execute the stored procedure my data gets
truncated (although i made the sizes for my fields ridiculously big
like 1000 just in case) for example if i want to enter Jasmine it only
inserts 'J' in the field

I am sure there's something wrong in the stored procedure, and I am
guessing it's a problem of using single vs. double quotes and stuff
like that within my insert statement...
the following is my stored procedure:

CREATE procedure addLabor
@lName varchar,
@fName varchar,
@mName varchar,
@title varchar,
@craft varchar,
@lastFour varchar,
@SSN varchar,
@dateOfHire varchar,
@currentProj varchar,
@status tinyInt,
@project_id int,
@updateBy varchar,
@updateDate varchar,
@address varchar,
@email varchar,
@phone varchar,
@zip varchar,
@myfeedBack varchar,
@ethnicity varchar,
@userID int
as
BEGIN
SET NOCOUNT OFF
DECLARE @newid INT
insert into laborPersonal ( lName, fName, mName, title, craft,
lastFour, SSN, dateOfHire,  currentProj, status, project_id,
updateBy,

updateDate, address, email, phone, zip, ethnicity)
 VALUES
(@lName  , @fName , @mName , @title  , @craft , @lastFour  , @SSN ,@dateOfHire  , @currentProj ,
@status,  @project_id , @UpdateBy, @UpdateDate , @address , @email , @phone , @zip , @ethnicity )
SELECT @newid = SCOPE_IDENTITY()
insert into feedBack
(lID, feedBack, userID, project_id)
values
(@newid ,+'
+@myfeedBack + ',
+@userID  ,
@project_id )
SET NOCOUNT ON
END
GO


When i use 2 single quotes it insert the following string: "@lName" not the actual value of the variable @lName
my exec statement is this:
EXEC addLabor 'Razor', 'Nazor', 'mid', 'Mr', 'Carpenter Forman',
'1234',
'keOWVozC+wmBvaqgkVkZci5y4vFLdTKfZOVG4C6BSN6H2MBP6pdsIWA0SdPAlPJra0EjEj+uXI/kXSiBuwwnKQ==',
'6/27/2005 12:00:00 AM' , 'O.C. Public Library', 1, 3, '3', '7/25/2005
2:38:02 PM', '1233 Shady Canyon, Irvine', '', '', '12345',
'123-12-1234', 'African American', '3'

I appreciate any help or hints
thanks to all

View 1 Replies View Related

Bulk Inserts To Data Warehouse - Best Practices?

Jul 20, 2005

Hello all,I just started a new job this week and they complain about the length oftime it takes to load data into their data warehouse,which they do once a month.From what I can gather, they rebuild the indexes before the insert with an80% Fillfactor, then insert the data (with theindexes enabled), then rebuild the indexes with a 100% Fillfactor.Most of my RDBMS experience is with a different product. We would havedisabled the indexes and Foreign Keys, loaded the data, thenre-enabled them, moving any records that violated the constraints into anappropriate audit table to be checked after.Can someone share with me what the accepted "best practices" are for loadingdata efficiently into a data warehouse?Any thoughts would be deeply appreciated.Steve

View 2 Replies View Related

Master Data Services :: Preventing Duplicate Inserts

Mar 4, 2015

In my trivial example below I have a Nationality entity. The entity just has the code and Name attributes, with the code being an automatically generated Integer. I am inserting one record and then trying to prevent the same record being inserted, by setting the ImportType to 1:

truncate table [stg].[Nationality_1_Leaf]
insert into [stg].[Nationality_1_Leaf](importType, ImportStatus_id, BatchTag, Name)
values(1, 0, 'some batch' , 'American' )
select * From [stg].[Nationality_1_Leaf]
exec stg.udp_Nationality_1_Leaf 'VERSION_1', 1, 'some batch'

After this I truncate the staging table and repeat the process. Unfortunately a new record is entered into the Entity even though the name is identical to a record that already exists.

View 13 Replies View Related

Which Method Is Better For Transfering Data Using Direct Inserts/SSIS?

Feb 7, 2007

Hi

I'm transfering legecy data to SQL Server.

Can anybody tell which method is best.

My boss wants cutome user interface to choose options and Need to update UI during processing.

Currently I'm using Direct INSERT Stmt (T-sql) Execution.

Can Anybody suggest the best.

View 1 Replies View Related

SQL Server 2008 :: Add A Trigger That Inserts Original Data From 1 Table To Another

Apr 10, 2015

I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABC](
[id] [uniqueidentifier] NOT NULL,

[Code] ....

When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABCD](
[id] [uniqueidentifier] NOT NULL,

[Code] .....

The trigger I've currently written looks like this:

/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]

[Code] ...

This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?

I can't sort by uniqueidentifier in descending as those can be random.

View 9 Replies View Related

Can A Script Handle Conditional Inserts To An OLDB Data Source?

Nov 20, 2007

I have a very simple SSIS task whcih opens a flat file data source, checks the first two characters of each row and if it is equal to "06", inserts that row into an OLDB destination table. However it is inserting EVERY row, even the ones not equal to "06" in first two columns. So, how do I get it to NOT insert the rows I don't want? Any help is very much appreciated and the following is my code:


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim InactiveDate As String

If Left(Row.Column0, 2) = "06" Then

Row.FilingNbr = Mid(Row.Column0, 3, 10)

Row.Address1 = Mid(Row.Column0, 13, 50)

Row.Address2 = Mid(Row.Column0, 63, 50)

Row.City = Mid(Row.Column0, 113, 64)

Row.State = Mid(Row.Column0, 177, 4)

Row.Zip = Mid(Row.Column0, 181, 9)

Row.ZipExt = Mid(Row.Column0, 190, 6)

Row.AgentCountry = Mid(Row.Column0, 196, 64)

InactiveDate = Mid(Row.Column0, 264, 2) + "/" + Mid(Row.Column0, 266, 2) + "/" + Mid(Row.Column0, 260, 4)

If IsDate(InactiveDate) And InactiveDate <> "00/00/0000" Then

Row.AgentInactiveDate = InactiveDate

Else

Row.AgentInactiveDate_IsNull = True

End If

Row.AgentLastName = Mid(Row.Column0, 268, 50)

Row.AgentFirstName = Mid(Row.Column0, 318, 50)

Row.AgentMiddleName = Mid(Row.Column0, 368, 50)

Row.AgentSuffix = Mid(Row.Column0, 418, 6)

End If



End Sub


Karen

View 5 Replies View Related

Does The Configure Data Source Wizard Work For Updates,Inserts, And Deletes ?

Jun 8, 2007

Ok, I think this may have a simple answer. Basically I have no problems in setting up QueryString/Control/etc parameters when I use SELECT in the Configure Data Source Wizard as it prompts me for the necessary parameters. But when I try to use the Configure Data Source Wizard with an UPDATE, INSERT or DELETE it does NOT prompt me for the required parameters.Is this a bug or am I just missing something? Do I have to put them in manually or something?Thanks! 

View 5 Replies View Related

Better Practices Wanted For Cascading Inserts Of Hierarchical Data From Staging Tables

Aug 28, 2007

I apologize if this has been asked, but I can't find a complete answer.

We have a situation with parent/child tables which have an identity column as their PK. We need to be able to insert into the live tables from staging tables. The data in the staging tables are related via a surrogate key.

I have found the OUTPUT clause, but that can only refer to columns of the actual table (since there is no FROM clause in an INSERT). Our current best solution to this problem involves adding bogus "staging" columns to the destination tables, and removing them after we've inserted everything from staging. This is an unattractive solution to say the least.

I'll give an example that mirrors our actual solution, and ask if anyone has a better solution?
----------




Code Snippet
CREATE TABLE [dbo].[TABLE_A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DATA] [nchar](10) NOT NULL,
[STAGING_COLUMN] [bigint] NULL,
CONSTRAINT [PK_TABLE_A] PRIMARY KEY ([ID] ASC)
)
GO
CREATE TABLE [dbo].[TABLE_B](
[ID] [int] IDENTITY(1,1) NOT NULL,
[A_ID] [int] NOT NULL,
[DATA] [nchar](10) NOT NULL,
[STAGING_COLUMN] [bigint] NULL,
CONSTRAINT [PK_TABLE_B] PRIMARY KEY ([ID] ASC)
)
GO
ALTER TABLE [dbo].[TABLE_B]
ADD CONSTRAINT [FK_TABLE_A_TABLE_B] FOREIGN KEY([A_ID]) REFERENCES [dbo].[TABLE_A] ([ID])
GO
CREATE TABLE [dbo].[STAGE_TABLE_A](
[A_Key] [bigint] NOT NULL,
[DATA] [nchar](10) NOT NULL
)
GO
CREATE TABLE [dbo].[STAGE_TABLE_B](
[B_Key] [bigint] NOT NULL,
[DATA] [nchar](10) NOT NULL,
[A_Key] [bigint] NOT NULL
)
GO


The STAGING_COLUMN columns are the ones that will be added before, and dropped after.






Code Snippet
DECLARE @TABLE_A_MAP TABLE (
A_ID INT,
A_Key BIGINT
)
INSERT INTO TABLE_A (DATA, STAGING_COLUMN)
OUTPUT INSERTED.ID, INSERTED.STAGING_COLUMN INTO @TABLE_A_MAP
SELECT DATA, A_Key FROM STAGE_TABLE_A
INSERT INTO TABLE_B (A_ID, DATA)
SELECT TAM.A_ID, STB.DATA
FROM STAGE_TABLE_B STB INNER JOIN @TABLE_A_MAP TAM ON TAM.A_Key = STB.A_Key






This seems to work, but I'd really like another alternative. Even though this is happening when nobody else is using the database, I cringe at the thought of adding and removing columns just to make this work.

Here are a few of my constraints:



The above is a simplification of the actual problem. The actual problem goes about five levels deep (hence the B_Key in STAGE_TABLE_B). At the top level, our larger customer will have 100,000 rows to insert. Each level will average 3 times as many rows as the next higher level, so we're talking about real volumes here.

This has to finish over the course of a weekend.

This has to be delivered to QA this Friday
Thanks for any help or insight.

View 3 Replies View Related

SQL Server 2014 :: Stored Procedure That Inserts And Updates A Table With Excel Data?

May 27, 2014

I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?

View 6 Replies View Related

3-4-5 Rule

Feb 2, 2008

Hi
I came across something like 3-4-5 rule while going through datamining book....but couldn't get from where that rule has been generalized and how it really works....

can anyone explain this rule ?

Thank you

View 1 Replies View Related

How To Extract Rule

Nov 3, 2006

Hi,

How do I extract rules and it's value from a database?
I can extract the rules through view(sys.objects) but where can I get it's content?

Regards
Marcelo Gamba

View 4 Replies View Related

One Database To Rule Them All

Jul 25, 2007

How can I setup the dbs in sql server so that when I change the data in one table the changes will cascade down to the tables in my other dbs. Therefore, one database would hold a primary key table. If I had 15 other dbs, then I could somehow link them so the data changed in the primary key table of the 1st database would cascade down to the other dbs.

Thanks

View 2 Replies View Related

Valid Rule For Begin/End??

Feb 7, 2008

I wonder about whether this rule is valid or invalid for nested BEGIN/END statement...


Code:


BEGIN

BEGIN
--Query #1 (blah)...
END

WHILE EXISTS (SELECT TOP 1 * FROM #tmpTblPurchaseRaw)
BEGIN
BEGIN
--Query #2 (blah)...
END
BEGIN
--Query #3 (blah)...
END
END

END



I have no idea if nested BEGIN/END is allowed or not...

View 1 Replies View Related

Validation Rule.. For Numbers Only

Aug 31, 2007

I am getting some data from another database and only want to copy sensible data...

How do I write a validation rule in SQL (SQL SERVER 2000) for a fax number, so that it only contains numbers-- i.e digits 0-9


Thanks in advance

View 5 Replies View Related

Query To Avoid Certain Rule

Jun 16, 2014

I have a query that maps all products to some customer levels. In this case levels 0,5,7 and 8

DELETE FROM ProductCustomerLevel
WHERE CustomerLevelID IN (0, 5, 7, 8)
INSERT ProductCustomerLevel
(
ProductID,
CustomerLevelID

[Code] ....

Basically this maps all products in a database to these customer levels so that they get a discount.

I know need to create a new customer level, example number 9. These will only have 1 or 2 products applied to it.

How can I change the SQL above so that it does not map those products already in Customer Level 9 to levels 0,5,7 and 8

View 3 Replies View Related

Update And Delete Rule - Cascade

Jan 15, 2007

Hi, I have a database which saves data about bus links. I want to provide a information to passenger about price of their journay. The price depends on three factors: starting busstop, ending busstop and type of ticket (full, part - for students and old people, ...).
So I created a table with three foreign key constraints (two for busstops and one for type).
When the busstop is deleted or type of ticket I want all data connected with it to be deleted automatically. I wanted to use cascade deleting.
But I receive a following exception: Introducing FOREIGN KEY constraint 'FK_TicketPrices_BusStops1' on table 'TicketPrices' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
How can I achieve my task? Why should it cause cycles or multiple cascade paths?

View 1 Replies View Related

How To Create Rule Which Update Other Columns

Jul 20, 2005

Hi,I have a table with the following columns:ID INTEGEDR,Name VARCHAR(32),Surname VARCHAR(32),GroupID INTEGER,SubGroupOneID INTEGER,SubGroupTwoID INTEGERHow can I create a rule/default/check which update SubGroupOneID &SubGroupTwoID columns when GroupID for example is equal 15 onMSSQL2000.It is imposible to make changes on client, so I need to checkinserted/updated value of GroupID column and automaticly updateSubGroupOneID & SubGroupTwoID columns.Sincerely,Rustam Bogubaev

View 4 Replies View Related

PredictProbability With Association Rule Model..

Nov 20, 2006

I have run into a .. somewhat of a "duh" question. I'm running association rule to run a basket analysis, and I'm trying to get probability of each prediction. I know this is wrong, but how do I go about running PredictProbability on each ProductPurchase prediction?

When I run the below DMX query, I get this error message...

Error (Data mining): the dot expression is not allowed in the context at line 5, column 25. Use sub-SELECT instead.

Thanks in advance...

-Young K



SELECT
t.[AgeGroupName]
, t.[ChildrenStatusName]
, (Predict([Basket Analysis AR].[Training Product], 3)) as [ProductPurchases]
, (PredictProbability([Basket Analysis AR].[Training Product].[ProductName])) as [ProductPurchases]
From
[Basket Analysis AR]
PREDICTION JOIN
OPENQUERY([DM Reports DM],
'SELECT
[AgeGroupName]
, [ChildrenStatusName]
FROM
[dbo].[DM.BasketAnalysis.Contact]
WHERE isTrainingData = 0
') AS t
ON
[Basket Analysis AR].[Age Group Name] = t.[AgeGroupName]
AND [Basket Analysis AR].[Children Status Name] = t.[ChildrenStatusName]

View 4 Replies View Related

Cases Supporting A Particular Association Rule

Jun 18, 2007

I haven't been able to find a DMX query which will spit out the cases which support a particular association rule. I was hoping it would work sort of like drillthrough but show only the cases supporting a particular rule. Am I missing something?



What I ended up doing was extracting the itemsets of the rule from the model's content then running a SQL query to retrieve the cases that contain both the left-hand and right-hand itemset of the rule. I'm hoping there's a better way.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved