Why I Got The Message As You Need To Create The Many-to-one Relationship Between The Case Table And The Nested Table?
Hi, all experts here,
Thank you very much for your kind attention.
I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it?
Hope it is clear for your help.
Thanks a lot for your kind advices and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Question On Case Table And Nested Table
Hi, all here, As we are allowed to select one table as both case table and nested table, however what is the benefit of using one table as both case table and nested table? Thanks in advance for your advices. I am looking forward to hearing from you shortly. With best regards, Yours sincerely,
View Replies !
View Related
Case && Nested Table Selection And Errors
hi Here are the two tables again. 1)PATIENT(PATIENT_ID,NAME,CITY) 2) DISEASES(DISEASE_ID,NAME) I am trying to select patient table as case and diseases table as nested to create an association model. i m getting following error. Disease table cannot be used as a nested table because it does not have a many-to-one relationship with the case table. You need to create a many-to-one relationship between the two tables in the data source file. i have created a relationship by dragging Disease_id from diseases table on Patient_id in patient table. when i am trying to select Patient_id as key, City as input, it is not showing disease_id to choose as a predict column. please suggest me if i am doing anything wrong? i have not done any thing to do my datbase, just selected the tables i want to create an association model on and trying to create association model. your help and insight is highly appreciated. regards Raju
View Replies !
View Related
Case Table And Nested Tables Design Problem
Hi, I have a problem in design the tables. My main task is to learn how to give the Match Score. I have hundreds of dataset and one of them is like this: Test Record Number: 19 Prospect ID = 254040088233400441105260031881009 Match Score = 95 Input Record Fielding ( eg wordnumber[Field] ) : 1[1] 2[1] 3[11] 4[11] 5[11] Prospect Word = 1 type = 1 match level = 4 input word = 1 input type = 1 Prospect Word = 2 type = 2 match level = 0 input word = NA input type = NA Prospect Word = 3 type = 3 match level = 4 input word = 2 input type = 1 Prospect Word = 4 type = 11 match level = 4 input word = 3 input type = 11 Prospect Word = 5 type = 13 match level = 4 input word = 4 input type = 11 Prospect Word = 6 type = 14 match level = 4 input word = 5 input type = 11 Now I have all my data stored in the DB and I seperated them into 3 tables and their structures are: 1) prospect (id, testrecordnumber, prospectID, matchscore) 2) inputfieldind (id, prspid, inputword, inputfield) 3) prospectinfo (id, prspid, prospectword, prospecttype, matchlevel, inputword, inputtype) and the prspid in table 2 & 3 refers to the prospectID in table 1.What I did was setting: a) prospect table as case table with id AS key, prospectID AS input & predictable; b) and the other two as nested tables with inputword/inputfield AS key & input, prospectword/prospecttype/matchlevel/inputword/inputtype AS key & input . But it shows error for having multiply key columns... And also I am thinking about using the Naive bayes algorithm. Can I also have some suggestion on this? Thanks
View Replies !
View Related
Case Table And Nested Tables In Association Algorithm
hi i m trying to build microsoft association model using Microsoft association algorithm. i got 1) patient table(patientid, name, city) 2) diseases(diseaseid, dieseasename) It is M:N [many to many] relationship between above tables, so 3)Patient_diseases(patientid,disease_id). [RELATIONSHIP TABLE] i am trying to associate city in patient table --> disease in diseases table. I want to build association data mining model and use it on web form, such a way when the user enters city associated disease will be displayed. should i select all 3 table to build the model? could help me to decide what tables should i select as Case and what tables as Nested? what attributes from the table should i select as key, input, predictive ? i am using data mining tutorials on sqlserverdatamining.com to build this model. is there anything further during my model building i get into confusion? please suggest me where i can find complete resource or inform here. i appreciate Mr.Jamie for his guidance so far in my academic project. i do have the book 'Data mining with sql server 2005'. I left with just one day to do this and document. hoping someone could suggest. your help is much appreciated. regards raju
View Replies !
View Related
Create Table With Many-to-many Relationship...
Hi, I come back again. Can anyone help me to create table with many-to-many relationship. Here is my three tables tbl_Networks ( NID int identity(1,1) primary key, NetworkName nvarchar(256) ) tbl_Categories ( CID int identity(1,1) primary key, CateName nvarchar(256), NID int ) tbl_Sim ( SID int identity(1,1) primary key, NID int, CID int, NameOfSim nvarchar(256) ) My problem is 1 value in tbl_Sim may have multiple values in table tbl_Categories and vice versal. And I don't know how to organise them So I need some help...
View Replies !
View Related
Create Table With Recursive Relationship
I am fairly new to SQL and I am currently trying to createa SQL table (using Microsoft SQL) that has a recursiverelationship, let me try to explain:I have a piece of Data let's call it "Item" wich may again contain onemore "Items". Now how would I design a set of SQL Tables that arecapable of storing this information?I tried the following two approaches:1.) create a Table "Item" with Column "ItemID" as primary key, somecolums for the Data an Item can store and a Column "ParentItemID". Iset a foreign key for ParentItemID wich links to the primarykey"ItemID" of the same table.2.) create separate Table "Item_ParentItem" that storesItemID-ParentItemID-pairs. Each column has a foreign key linked toprimary key of the "Item" Column "ItemID".In both approaches when I try to delete an Item I get an Exceptionsaying that the DELETE command could not be executed because itviolates a COLUMN REFERENCE constraint. The goal behind these FK_PKrelations is is that when an Item gets deleted, all childItems shouldautomatically be deleted recursively.How is this "standard-problem" usually solved in sql? Or do I inned toimplement the recursive deletion myself using storedprocedures or something ?
View Replies !
View Related
Questions On Key Column In Case Table And Key Time Column In Nested Table Using Time Series Algorithm
Hi, all experts here, Thank you very much for your kind attention. I am confused on key column of case table and key time column of nested table by using Time Series algorithm. In my case, the case table structure is as below: Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks), While the nested table structure is as below: Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID) So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table? Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month? Hope it is clear for your advices and help. And I am looking forward to hearing from you shortly. With best regards, Yours sincerely,
View Replies !
View Related
Failing To Create Relationship (SQL Server Claims Table Lacks PK For Some Reason ?)
I tried to create a relationship in EM's diagram pane inSQL Server 2000 (I'd list the version of EM, but About gives methe MMC version, which is probably not relevant.)(The database itself is SQL Server 2000 SP3.)I got an error that I don't understand (because, at leastat first blush, it appears to be quite untrue). Note thatI have never clicked before on the diagrams child of thedatabase; this was entirely experimental."Primary key or UNIQUE constraint must be defined fortable 'xxx' before it can participate in a relationship."http://msdn.microsoft.com/library/d...cantbepktbl.asp1) My table already had a primary key; why is MS SQL Server apparentlyclaiming otherwise ? (I don't think I can define a second primary keyon the same table. I could perhaps define an additional unique indexon top of the primary key, but, I'm not sure.)2) What does it mean: table '<0s>' ? That is, what does 0s mean ?
View Replies !
View Related
Import Csv Data To Dbo.Tables Via CREATE TABLE && BUKL INSERT:How To Designate The Primary-Foreign Keys && Set Up Relationship?
Hi all, I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations: -- ImportCSVprojects.sql -- USE ChemDatabase GO CREATE TABLE Projects ( ProjectID int, ProjectName nvarchar(25), LabName nvarchar(25) ); BULK INSERT dbo.Projects FROM 'c:myfileProjects.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) GO ======================================= -- ImportCSVsamples.sql -- USE ChemDatabase GO CREATE TABLE Samples ( SampleID int, SampleName nvarchar(25), Matrix nvarchar(25), SampleType nvarchar(25), ChemGroup nvarchar(25), ProjectID int ); BULK INSERT dbo.Samples FROM 'c:myfileSamples.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) GO ========================================= -- ImportCSVtestResult.sql -- USE ChemDatabase GO CREATE TABLE TestResults ( AnalyteID int, AnalyteName nvarchar(25), Result decimal(9,3), UnitForConc nvarchar(25), SampleID int ); BULK INSERT dbo.TestResults FROM 'c:myfileLabTests.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) GO ======================================== The 3 csv files were successfully imported into the ChemDatabase of my SSMSE. 2 questions to ask: (1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables? Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period? (2) How can I set up the relationships among these 3 dbo Tables? Please help and advise. Thanks in advance, Scott Chang
View Replies !
View Related
Default Table Owner Using CREATE TABLE, INSERT, SELECT && DROP TABLE
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View Replies !
View Related
Updating Table Referencing 2nd Table Using Case
Hi Im trying to create an update statement which references two tables (join) and has a CASE clause attached. Not sure where im going wrong... Using T-sql!!! update import set import.gone = from import inner join stat ON stat.id = import.id CASE WHEN stat.A = import.field2 THEN import.gone = sec.A WHEN stat.B = import.field2 THEN import.gone = sec.B WHEN stat.C = import.field2 THEN import.gone = sec.C WHEN stat.D = import.field2 THEN import.gone = sec.D WHEN stat.E = import.field2 THEN import.gone = sec.E WHEN stat.F = import.field2 THEN import.gone = sec.F ELSE import.gone = null END Any help would be greatly appreciated
View Replies !
View Related
Create Table From Text File, Extract Data, Create New Table From Extracted Data....
Hello all, Please help.... I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem.... I need to extract data from DataFile table, here's my query: select * from dbo.DataFile where DF_SC_Case_Nbr not like '0000%'; Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one. Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)? Any help would be highly appreciated. Thanks in advance.
View Replies !
View Related
What Relationship To Use Between These Two Table?
Hi I have two tables: 1.) Operator-OperatorID{PK, int, not null}-OperatorName{varchar(100), not null}-Enabled{bit, not null}-PasswordChange{bit, not null}-BirthDate{datetime, not null} 2.) Password-PasswordID{PK, int, not null}-Password{varchar(50), not null}-ExpirationDate{datetime, not null} I'm not sure how to design and layout these two tables. The layout of these two tables is completely flexible as the application has not been deployed. I'm open to any good suggestions. For each Operator I want to stored up to 3 previous passwords plus their current password. The password change field is so that if the operator's password expires or gets reset, they will be forced to enter a new password. This is a simple internal company application, so password encrypting is not necessary. The ExpirationDate indicates the date that the password will expire. Hope to hear from someone soon! Thank you!
View Replies !
View Related
Table Relationship
Hello, I created some SQL 2005 tables using Microsoft SQL Server Management Studio. I need to get the script code of those tables. I was able to do that by right clicking over each table. But how can I get the code for the relationships between the tables? Can't I create relationships between two tables by using T-SQL? Thanks, Miguel
View Replies !
View Related
Naming Of Relationship Table
We have two tables. Users and Projects and there is a many-to-many relationship.Ex. A user can be assigned into multiple projects.For the relationship table, should the table name be UserProjects or ProjectUsers?Also should it be singular or plural? (ex. UsersProjects or ProjectsUsers)?
View Replies !
View Related
Relationship Inside The Same Table
i ve got a database that has a table...that table has a relationship between its primary key,and another field,actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)i hope i ll have an answer soon,and thanks in advancedPS: i am using MSSQL 2000 evaluation
View Replies !
View Related
Combine Relationship Table
Hello everyone: I have two database desgin questions. I hope could get some helps here to resolve my puzzle. suppose I have a parent table which has many-to-many relationship to its three children tables. E.g: Parent table: webpage -pageID -pageTitle child table: (which are the content could be in the webpage) Picture ---pictureID ---pctureName StreamVideo --videoID --videoName StreamAudio --audioID --audioName Normally, in a many-to-many relationship, a third table will be introduce to break down the many-to-many into one-to-many relationship. So in this case, I will have three relationship tables 1. Webpage--Picture --pageID --pictureID 2. Webpage--Video --pageID --videoID 3. Webpage--Audio --pageID --audioID My question is should I combine this three relationship table into one big relationship table like: Webpage--content --pageID --pictureID --videoID --audioID I know the drawback of this combining will cause redundant, but this could avoid join tables when writting query (especially when there are more child table associate with one parent table). Also I was told that foreign key can be nullable, so if one webpage only have pictures, as long as I insert pictureIDs with the null value videoIDs and audioIDs into the Webpage--content table, then it seems I don't duplicate anything. So far I don't see anything wrong with this approach, I hope someone can give me some suggestions whether I should go this way. My another question is also about the relationship table, suppose in the above exmaple, we won't combine those three relationship tables. But we need to assign a value to each pictureID, videoID, audioID. And this value will be different if a pictureID, videoID, aduioID appears in different webpage. How I should solve this issue? The best I can think so far is add a value column in the relationship table. But I have not seen people did this before. Add columns into a relationship table. Is this a good desgin?
View Replies !
View Related
Table Relationship In VB Express
I have 3 tables I am trying to relate for a music player. I was following the example in the msdn however, my relationships do not seem right. Here are tables i want to relate: Table 1: ArtistID ArtistName: Table 2: RecordingID RecordingName ArtistID Table 3: TrackID TrackName TrackNumber TrackLocation RecordingID So the main idea here is that the foreign keys are recordingId and artistID. So what i did is created the 3 tables and then make a diagram to create the relationships. I then was reading this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=219696&SiteID=1 What i want to do is use 2 different text boxes lets say and as i move from the records in the Record_table (2) the corresponding artist will change with it. However in the dataset the relationship looks like this: Artist -> Recording -> Track.... inorder for me to get this relationship to work correctly i have to change all the relationships in the dataset diagram. This way the dataset would look like this: Track -> Recording ->Artist. This way i can use the 2 bindings to reference each other as stated in the link above. why doesnt the relationship of the database know this already? why do i have to change the relationship in the datasets.
View Replies !
View Related
Table Relationship. What Am I Doing Wrong?
Hello, I have 3 tables: Articles, ArticlesTags and Tags. ArticleTags relate Articles and Tags records. When I try to delete a record in Tags I get the following error: The DELETE statement conflicted with the REFERENCE constraint "FK_ArticlesTags_Tags". The conflict occurred in database "MyDB", table "dbo.ArticlesTags", column 'TagID'. The statement has been terminated. What am I doing wrong? Here is how I am creating my tables: create table dbo.Articles ( ArticleID uniqueidentifier not null constraint PK_Article primary key clustered, Body nvarchar(max) not null ) create table dbo.ArticlesTags ( ArticleID uniqueidentifier not null, TagID uniqueidentifier not null, constraint PK_ArticlesTags primary key clustered (ArticleID, TagID), constraint FK_ArticlesTags_Articles foreign key(ArticleID) references dbo.Articles(ArticleID) on delete cascade, constraint FK_ArticlesTags_Tags foreign key(TagID) references dbo.Tags(TagID) ) create table dbo.Tags ( TagID uniqueidentifier not null constraint PK_Tag primary key clustered, [Name] nvarchar(200) not null ) Basically, what I need is: 1. If an Article is deleted then: > Delete all records for that Article in ArticlesTags > Don't delete any Tag in Tags. 2. If an Tag is deleted then: > Delete all records associated with it in ArticlesTags. > Don't delete any Article in Articles. What am I missing? Thanks, Miguel
View Replies !
View Related
SSMS Express: Create TABLE && INSERT Data Into Table - Error Msgs 102 && 156
Hi all, I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC. I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got Error Messages 102 and 156 when I did "Parse" or "Execute". This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"? Please help and advise. Thanks in advance, Scott Chang ///////////--SQLQueryCroomLabData.sql--/////////////////////////// USE MyDatabase GO CREATE TABLE dbo.LabResults (SampleID int PRIMARY KEY NOT NULL, SampleName varchar(25) NOT NULL, AnalyteName varchar(25) NOT NULL, Concentration decimal(6.2) NULL) GO --Inserting data into a table INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration) VALUES (1, 'MW2', 'Acetone', 1.00) VALUES (2, 'MW2', 'Dichloroethene', 1.00) VALUES (3, 'MW2', 'Trichloroethene', 20.00) VALUES (4, 'MW2', 'Chloroform', 1.00) VALUES (5, 'MW2', 'Methylene Chloride', 1.00) VALUES (6, 'MW6S', 'Acetone', 1.00) VALUES (7, 'MW6S', 'Dichloroethene', 1.00) VALUES (8, 'MW6S', 'Trichloroethene', 1.00) VALUES (9, 'MW6S', 'Chloroform', 1.00) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00 VALUES (11, 'MW7', 'Acetone', 1.00) VALUES (12, 'MW7', 'Dichloroethene', 1.00) VALUES (13, 'MW7', 'Trichloroethene', 1.00) VALUES (14, 'MW7', 'Chloroform', 1.00) VALUES (15, 'MW7', 'Methylene Chloride', 1.00 VALUES (16, 'TripBlank', 'Acetone', 1.00) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00) VALUES (19, 'TripBlank', 'Chloroform', 0.76) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51) GO //////////Parse/////////// Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '6.2'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'VALUES'. ////////////////Execute//////////////////// Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '6.2'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'VALUES'.
View Replies !
View Related
Create Target Table Dynamically Based On Source Table Schema?
I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely, Bryan Ax
View Replies !
View Related
Create An Extra Table (for Audit Purpose) For Every Existing Table In A Database
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose. For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work. Please advise.. Thanks in advance. Here is my code: Code Snippet USE pubs DECLARE @TABLE_NAME sysname DECLARE @AUDIT_TABLE VARCHAR(50) SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME NOT LIKE 'audit%' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME = 'sales' WHILE @TABLE_NAME IS NOT NULL BEGIN SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_NAME = 'sales' SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME'' SELECT * INTO @AUDIT_TABLE FROM @TABLE_NAME TRUNCATE TABLE @AUDIT_TABLE ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime Char(50),AuditUser Char(50) SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME NOT LIKE 'audit%' END Thanks. ..
View Replies !
View Related
Problems Creating Second Relationship To The Same Table
I have two tables: ads and categories. I have an existing relationship: categories.id (PK) and ads.categoryid (FK). Now I want to create additional relationship with categories.id (PK) on ads.SecondCategoryID (FK). When I try to save it in SQL Manager I get the following error: - Unable to create relationship 'FK_classifieds_Ads_classifieds_Categories2'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_classifieds_Ads_classifieds_Categories2". The conflict occurred in database "mydb", table "dbo.classifieds_Categories", column 'Id'.
View Replies !
View Related
Search On Table With Recursive Relationship
I am using tables with recursive relationships extensively. For example the table tbComponent has a primary key called Co_ID and a foreign key called Co_Co_ID which references the field Co_ID. This allows a component to have unlimited child components, and each child component can have an unlimited number of tiers of children. I have a few question for which I have seen no documentation on. 1. How can I create a view or a SP that will return a component record and all of its children and children's children records down to the last/lowest child record? 2. I need to be able to do a search in this table. Example: Table: tbComponent Columns: Co_ID Integer Co_Co_ID Integer Co_Name Text Co_Attribute Text Co_Category Text Note: the data for Co_Category comes from a lookup table with also has a recursive relationship to itself where a category can have an unlimited number of tiers of children categories. A typical group of records could be something like this: Co_ID Co_Co_ID Co_Name Co_Attribute Co_Category 1 1 Car Blue Ford 2 1 Body Steel Parts 3 2 Door Front Parts 4 3 Invoice April 1 1999 Accounting Ok, say there is over a million records in this table. Say I want to query this table and return all of the invoices for cars between March 1 1999 and May 1 1999. Say for example that there are less records where Co_Name has a value of Car then there are records with a value of Invoice, so logically I would set some kind of criteria to limit only invoices with where Co_Name = "Car". That's easy, I can return a result set of all the records Where Co_Name = "Car" and I can hold these in a view or a temp table. Now I need to query this View or temp table and see if it has any children records records Where Co_Name = "Invoice" . The problem is that the Invoice child could be a child record directly under the "Car" record, or 10 levels of children records down. The logic for this would be: If Co_Name = "Car" Then Select * Where Co_Co_ID = 1, then take all of those record's values in Co_ID and run another statement Where Co_Co_ID = X and so on until there are no more children to search. If any of those records have a value of Invoice in Co_Name then return them. Please don't give any speeches on Normal Relationship Database design suggesting a typical design using something like a Car table, a parts table, and a invoice table. I used the example above to demonstrate the dynamics of doing a search on a recursive relationship. I understand relationship theory and a recursive relationship is what we need to use in our situation. Thanks for any and all input!
View Replies !
View Related
How To Create Index On Table Variable (Table Don't Have Primary Key)
Hi all, my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop... below is my table variable and I need to create 3 indexes on this... DECLARE @t_Replenishment_Rpt TABLE ( Item_Nbr varchar(25) NULL, Item_Desc varchar(255) NULL, Trx_Date datetime NULL, Balance int NULL, Trx_Type char(10) NULL, Issue_Type char(10) NULL, Location char(25) NULL, Min_Stock int NULL, Order_Qty int NULL, Unit char(10) NULL, Issue_Qty int NULL, Vendor varchar(10) NULL, WO_Nbr varchar(10) NULL, Lead_Time int NULL, PO_Nbr char(10) NULL, PO_Status char(10) NULL, Currency char(10) NULL, Last_Cost money NULL, Dept_No varchar(20) NULL, MSDSNbr varchar(10) NULL, VendorName varchar(50) NULL, Reviewed varchar(20) NULL ) I tryed all below senarios...it is giving error... --Indexing the @t_Replenishment_Rpt table on the column Names Item Number, Vender , Department Number --EXEC sp_executesql(CREATE UNIQUE CLUSTERED INDEX Replenishment_index ON @t_Replenishment_Rpt (Item_Nbr)) --CREATE UNIQUE CLUSTERED INDEX Idx1 ON @t_Replenishment_Rpt.Item_Nbr INDEX_COL ( '@t_Replenishment_Rpt' , ind_Replenishment_id , Item_Nbr ) --EXEC sp_executesql('SELECT INDEXPROPERTY('+ '@t_Replenishment_Rpt' + ', ' + 'Item_Nbr' + ',' + 'IsPadIndex' + ')') --EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Vendor','IsPadIndex')) --EXEC sp_executesql(SELECT INDEXPROPERTY('@t_Replenishment_Rpt', 'Dept_No','IsPadIndex'))
View Replies !
View Related
When To Use A Nested Table?
Why would someone choose to model a problem with a case and nested table versus just using a case tables with multiple rows? For example, a simple customer and product scenario where you could say: Mining Model DT_CustProd ( [Id] , [Gender] , [Age] [Products] Predict ( [ProductName] , [Quantity] ) ) But you could also model as: Mining Model DT_CustProd ( [Id] , [Gender] , [Age] [ProductName] Predict, [Quantity] Predict ) and just insert multiple rows per Id/Gender/Age? Is there any rule of thumb to use here when deciding to model with a nested table? I understand that a nested table essentially pivots the data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=408840&SiteID=1) and the data becomes an attribute, but how do you know if you want that, or you want the data as a value of an attribute?
View Replies !
View Related
Why Use Nested Table?
Hi, all here, I have a question about nested table use. I dont quite clearly see through why the nested table is very much necessary? I mean if we need information from both the current case table and nested table for mining models, we can just join both tables? Thanks a lot in advance for any guidance and help.
View Replies !
View Related
Trying To Enforce Ref Integrity On Hierarchy Table Relationship
I've got a table that includes:CREATE TABLE [dbo].[Content] ( [Id] int IDENTITY(1, 1) NOT NULL, [ParentId] int NULL, I'm wanting to make sure that a ParentId must be in the table as Id someplace else. When I try to do it by making it a foreign key get the error: --------------- SQL --------------- ALTER TABLE [dbo].[Content]ADD CONSTRAINT [Content_fk3] FOREIGN KEY ([Id]) REFERENCES [dbo].[Content] ([ParentId]) ON UPDATE NO ACTION ON DELETE NO ACTIONGO ---------- ERROR MESSAGE ---------- There are no primary or candidate keys in the referenced table 'dbo.Content' that match the referencing column list in the foreign key 'Content_fk3'.Could not create constraint. See previous errors. Any ideas? ALTER TABLE [dbo].[Content]ADD CONSTRAINT [Content_fk3] FOREIGN KEY ([Id]) REFERENCES [Content].[dbo] ([ParentId]) ON UPDATE NO ACTION ON DELETE NO ACTIONGO
View Replies !
View Related
Database Table Data Modifications With PK/FK Relationship
I have two tables (T1 and T2). In T1 I have a field FT1 that is aprimary key in T2 I have a field FT2 that is a foreign key linked toFT1. These fields have been populated with data. Lets say that in onerow of data I have in T1 under FT1 "my cell" as the data entry,similarly with T2 under FT2 I have 2 rows of data that also have "mycell" as the data entry. What is the best line of action is I wantedto change "my cell" to "my data"?
View Replies !
View Related
Link Table Versus XML Column For One-to-many Relationship
I am designing a database schema where several tables have one-to-many relationships to records in other tables. One way to implement it is to create a link table for each pair of tables that have a relationship: identity Table1_ID Table2_ID 1 12 9 2 12 15 3 18 42With the SQL 2005 support for the XML data type, there is the possibility of storing the IDs in an XML column. The XML stored with a record (equivalent to record 12 from the above example) might look like this: <Links> <Table2_LinkType> <ID>9</ID> <ID>15</ID> </Table2_LinkType> <Table8_LinkType> . . . </Table8_LinkType> </Links>The XML column method has the advantage of not requiring that a separate table be created but does not enforce referential integrity. The link table method has the advantage of allowing constraints to enforce referential integrity but has the disadvantage of requiring the creation of a separate table for each pair of tables having a relationship and joining to an additional table has performance implications. Implementing standard Add, List and Delete operations for the link table method is straightforward. As a test and to familiarize myself with the new XML features, I created Set, List and Delete stored procedures for the XML method. Both methods will work. In deciding which method to go with are there any other issues I should be considering besides database integrity, complexity and possible performance issues? From the standpoint of best practices and coding standards is one method preferred over the other? Here is some additional information. The data set I will be working with has table sizes numbering in the hundreds of thousands. Any given record will only be linked to at most a half-dozen records in any other table. I searched on-line for information and I was able to find plenty of good articles discussing how to use the new XML data type in SQL. However, I was not able to find any information on when not to use the XML data type over equivalent joined-table methods.
View Replies !
View Related
Table Relationship Question For Unique Record
Hello Hello, This Noob has a question. I have been atempting for days to figure this out. I have this set up: (See Below) The Problem I am having is occuring at the Table named OU in Maroon below. It combines 2 chains of keys into one table to try and make a unique record. The problem is that it is not. Pasted below is a Query run on the data within the tables: As you can see certain data is getting duplicated in this tabel. I want 1 Unique record in the OU table based upon the Study ID Primary Key and the zCombined Primary Key. Any Ideas as to what is wrong, or how to make it work would defineatly be appreciated. Thanks a Bunch Table: Sponsor PK: Sponsor ID Table: Protocol PK: ProtocolID FK: Sponsor ID Table: Study PK: StudyID FK: ProtocolID Table: OUPK: OUIDFK: StudyIDFK: zCombinedID Table:zCombined PK: zCombinedID FK: TempID ~ To Temp Table FK: ShipTypeID ~ To Ship Type Table FK: CoordinatorID ~ To Coordinator Table FK: BoxTypeID ~ To Box Type Table Sponsor Protocol Study OU~Column1 OU~Column2 Box Coordinator Ship Type Temp Omni AAAAA XPG S0101 NA Wheaton Raul Vargas Daily -70 Omni AAAAA XPG S0101 NA Matrix Raul Vargas Daily -70 Omni AAAAA XPG S0101 NA B-1 Raul Vargas Daily -70 Omni AAAAA XPG S0101 NA 9 x 9 4mL Raul Vargas Daily -70 Omni AAAAA XPG S0101 NA 9 x 9 2mL Raul Vargas Daily -70 Omni AAAAA XPG S0101 S0201 Wheaton Mike Keane DNA -40 Omni AAAAA XPG S0101 S0201 Matrix Mike Keane DNA -40 Omni AAAAA XPG S0101 S0201 B-1 Mike Keane DNA -40 Omni AAAAA XPG S0101 S0201 9 x 9 4mL Mike Keane DNA -40 Omni AAAAA XPG S0101 S0201 9 x 9 2mL Mike Keane DNA -40
View Replies !
View Related
Any Built-in Function To Show Table Relationship?
Hi guys I have a table which I need to analyze, what jobs this is working with, what stored procedure is calling this table etc. Is there any built-in function to show me or anybody know how to do it? This is my new job, and there are hundreds of stored procedures and jobs, do i really have to go thru one by one? Please help. Michelle
View Replies !
View Related
Nested Nodes In XML From A Table
Dear all,I have table called CATEGORY, which is defined as follows:CREATE TABLE CATEGORY(CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,CATEGORY_NAME VARCHAR(40) NOT NULL CONSTRAINTUC__CATEGORY__CATEGORY_NAME UNIQUE,PARENT_CATEGORY_ID INTEGER,CATEGORY_ICON IMAGE,DEPTH INTEGER,CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID))Supposly, the following snap shot was taken later:================================================== ============| CATEGORY_ID | CATEGORY_NAME | PARENT_CATEGORY_ID | DEPTH |================================================== ============| 1 | PC | NULL | 1 |--------------------------------------------------------------| 2 | Networks | 1 | 2 |--------------------------------------------------------------| 3 | Audio | 1 | 2 |--------------------------------------------------------------| 4 | Video | 1 | 2 |--------------------------------------------------------------| 5 | TV Cards | 4 | 3 |--------------------------------------------------------------| 6 | Graphics Cards | 4 | 3 |--------------------------------------------------------------| 7 | AGP | 6 | 4 |--------------------------------------------------------------| 8 | PCI | 6 | 4 |--------------------------------------------------------------| 9 | Input Devices | 1 | 2 |--------------------------------------------------------------and I'd like to create out of this hierarchy the following desired XMLfile:<?xml version="1.0" encoding="utf-8" ?><Hardware><Catgeory name="PC" id="1"><Catgeory name="Networks" id="2" /><Catgeory name="Audio" id="3" /><Catgeory name="Video" id="4"><Catgeory name="TV Cards" id="5" /><Catgeory name="Graphics Cards" id="6"><Catgeory name="AGP" id="7" /><Catgeory name="PCI" id="8" /></Category></Category><Catgeory name="Input Devices" id="9" /></Catgeory></Hardware>The reason for this file is that it will be a datasource of theTreeView Control new in asp.net 2.0.Now, programmateiclally using C#.net i started using the XmlDocument,XmlTextWriter and XmlTextReader Namespaces and started using susingrecurrsion to genearete this desired XML file out of the records in thesnapshot, but ...Is there an easy way of doing this using SqlServer 2005 with the newdatatype XML?*Any hint would also be ok*Best regards
View Replies !
View Related
I'm Having Issues With Nested Table...
Hey gang, I'm having some issues with nested table. This is my setup. [ProductTable] is the case table, and [CustomersTable] is a nested table. I'm trying to organize my algorithms around products. [ProductTable]<---[CustomersTable] [ProductTable] table only has product ID, and it is key. [CustomersTable] table has variety of customer attributes (productID, customerID, location, demographics...) and CustomerRevenue is predict_only. ProductName is the key for the nested table. I keep getting this error when I'm processing the mining models (Logical Regression and Neural Net). Error (Data mining): In mining model, Estimate Neural Net, the algorithm does not allow table column as predictable. Error (Data mining): Error validating attribute for the 'Estimate Neural Net' mining model. When using Decision Tree, it processes OK, but the result is totally wrong. The model is empty. Any ideas? -Young K. P.S. I'm trying to great a single model for multiple products. This is a label saving device that I'm trying. If this doens't work, I'll have to create a model for each product.
View Replies !
View Related
|
TRACKER
