Get Value By Looping Through Table Column
Apr 5, 2014
Here are two tables:
TABLE_A
[ac] [dest]
1 A
1
1
2
2
3
3 B
3
(ac=1, dest=A; ac=3, dest=B)
The space between '1' and 'A' isn't showing up correctly.
TABLE_B
[fleet] [ron]
1 A
1 A
1 A
1 A
1 A
2
2
2
2
3 B
3 B
(fleet=1, ron=A; fleet=3, ron=B; etc.etc.)
I would like to fill TABLE_B's column "ron" by referring to TABLE_A's column "dest".ac = fleet, and for each ac, I would like to loop through the 'dest' column in TABLE_A from top to bottom to get the top most value. If there are no values (like with ac = 2), then value is blank.
For 1, the value is A.
For 2, the value is blank.
For 3, the value is B.
Therefore, in TABLE_B, for all 'ron' for fleet=1, the value is filled A.
For all 'ron' for fleet=2, the value is blank.
For all 'ron' for fleet=3, the value is B.
- create variable 'v'
- where ac=fleet, loop through 'dest' from top to bottom to get top-most value. variable 'v' = the value.
- in TABLE_B, for each ac=fleet, insert variable 'v' into the 'ron' column
View 1 Replies
ADVERTISEMENT
Apr 22, 2008
Hi Everyone,
I am looking for a few pointers on how to achieve something. For the purpose
of my application I need to specify a SQL Server table. After this I wish to
be able to loop through column types and calculate the appropriate .NET
datatype based on the SQL Data Type, and also figure out if the column
allows NULL values.
I'm not looking for someone to do my work for me, just some pointers on what
I should be reading to achieve my aim.
Kind Regards
View 2 Replies
View Related
Mar 20, 2008
I have table like below
ID AMT
1001 1234.560
1001 34.560
1001 134.000
1002 45.000
1002 3456.000
1003 5678.999
I need to create a fixed length data file..For example, ID char(6) and amt num(10.3) and sum(23.3)
It should be group and order by ID : 01 Represent ID line and 02 represent amt ( there can be multiple amt records) and 03 represent sum of amt.
01 + ID
02 + AMT
03 + Sum(AMT)
The output should look like this.. How do this either using a t-sql or SSIS?
011001
021234.560
0234.560
02134.00
031403.12
011002
0245.000
023456.000
033501.000
011003
025678.999
035678.999
View 6 Replies
View Related
May 24, 2007
i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!
Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file....
Thanks in advance
View 1 Replies
View Related
Dec 5, 2006
I've been looking online and cannot find any help / resources with this so I brought it here :D
I'm looking for help in creating a Cursor (this will be inside a SP) that will loop through the records of a "Table" (Temporary or Retrieved) and for each row that is looped through I can use it's values to do inserts against a few other tables.
Any resources / help would be great! I work best by example.
View 12 Replies
View Related
Nov 8, 2007
Hello, Its hard trying to explain this.
I have 3 tables
Table 1 is where the users are stored, each user has a username and a userrank
Table 2 is where the points that decides the userrank are stored
Table 3 contains the available userranks like this
Table 1 (user_list) looks briefly like this:username nvarchar(20),userrank int, -- Reference to Table3 id... alot more fields
Table 2 (settings_profile) looks like this:username nvarchar(20),total_active_points int,... some more fields
Table 3 (data_ranks) looks like this:id int primary key auto inc,rankname nvarchar(20),min_pts int,max_pts int
Points get added to table 2 whenever they do something that generates points on the site. Points also get withdrawn every 7 days, so a user can only collect points for 7 days, on the 8th day, all points he earned on the 1st day is reduced from the current points with this code:
WHILE (SELECT @username = username, @id = id, @temp1 = ap_sentmails, @temp2 = ap_createdthreads, @temp3 = ap_createdanswers, @temp4 = ap_signguestbook, @temp5 = ap_blogcomment, @temp6 = ap_createblogentry, @temp7 = ap_profilefirsttime, @temp8 = ap_profilephoto, @temp9 = ap_activateguestbook, @temp10 = ap_addnewfriend, @temp11 = ap_superguruvote, @temp12 = ap_forumtopicvote, @temp13 = ap_labervote, @temp14 = ap_funstuffitemvote, @temp15 = ap_movievote, @temp16 = ap_actorvote, @temp17 = ap_money_new WHERE (created < Dateadd(dd, -7, @todaysdate))BEGINSET @sum = 0SET @sum = @temp1 + @temp2 + @temp3 + @temp4 + @temp5 + @temp6 + @temp7 + @temp8 + @temp9 + @temp10 + @temp11 + @temp12 + @temp13 + @temp14 + @temp15 + @temp16 + @temp17UPDATE settings_profile SET total_active_points = total_active_points - @sum WHERE (username = @username)DELETE FROM konto_daylist WHERE (id = @id)END
Now my question is this, i want to loop thru the table A, collect all usernames inside of it, then run it against table b and table c to determine the current rank of the user.Something like this...
DECLARE @username nvarchar(20)DECLARE @pts int, @rank int
...something that starts a loop thru table A (user_list) and get the username into @username...
SELECT @pts = total_active_points FROM settings_profile WHERE (username = @username)-- Determine the rank here, by compairing the points the user have against the pointstabel in table data_ranksSELECT @rank = id FROM data_ranks WHERE (pts_min => @pts AND pts_max < @pts)UPDATE user_list SET rank = @rank WHERE (username = @username)
...next persion in the loop...
This SP runs once a day and will first reduce the points from 8days ago, then it will run thru all the users and determine their new rank...
But how do i loop thru all the users? with a cursor?
View 10 Replies
View Related
Jun 3, 2008
Hello,
I am a little new at T-SQL. and I could use a suggestion on the best way to accomplish this task. I have an application where I must loop through a table in Microsoft SQL Server 2005, find the records that match and break them up into groups where the value of the Thickness field is in groups of ( <=8).
Lets say I have 100 records and the table contains sacks marked A, B, C, and D. Within Sack A is 6 records. Each of the 6 records has a thickness column with a value between 0.5 to 1.0 (my example shows 2) I have a column called bundle that I must update to show a 1 for the first group of (<=8) inches thick a 2 for the second group (<=8) inches thick and so on. Then repeat the process for sacks B, C and D.
The table itself has the following
Order Number |Zipcode |Sack(A, B, C,or D)| Thickness |Bundle|
1 |19809 |A | 2 |NULL |
2 |19809 |A | 2 |NULL
3 |19809 |A | 2 |NULL
4 |19809 |A | 2 |NULL
5 |19809 |A | 2 |NULL
6 |19809 |A | 2 |NULL |
7 |19721 |B | 4 |NULL |
8 |19721 |B | 3 |NULL |
9 |19721 |B | 2 |NULL |
10 |19721 |B | 5 |NULL |
11 |19721 |B | 2 |NULL |
12 |19721 |B | 2 |NULL |
What I need to do is sum the thickness until it gets to less than 8 and update bundle with a 1. The result would be 4 records and the table would then look like this:
Order Number |Zipcode | Sack(A, B, C,or D)| Thickness |Bundle|
1 |19809 |A | 2 |1 |
2 |19809 |A | 2 |1
3 |19809 |A | 2 |1
4 |19809 |A | 2 |1
5 |19809 |A | 2 |NULL
6 |19809 |A | 2 |NULL |
7 |19721 |B | 4 |NULL |
8 |19721 |B | 3 |NULL |
9 |19721 |B | 2 |NULL |
10 |19721 |B | 5 |NULL |
11 |19721 |B | 2 |NULL |
12 |19721 |B | 2 |NULL |
Notice there are two records that are not bundles for sack A. I need to place them in a separate bundle called 2. The result will be as follows
Order Number |Zipcode | Sack(A, B, C,or D)| Thickness |Bundle|
1 |19809 |A | 2 |1 |
2 |19809 |A | 2 |1
3 |19809 |A | 2 |1
4 |19809 |A | 2 |1
5 |19809 |A | 2 |2
6 |19809 |A | 2 |2 |
7 |19721 |B | 4 |NULL|
8 |19721 |B | 3 |NULL
9 |19721 |B | 2 |NULL
10 |19721 |B | 5 |NULL |
11 |19721 |B | 2 |NULL |
12 |19721 |B | 2 |NULL |
Now Sack A is complete so I must move on to Sack B. The first two records total to 7 and adding another record would move the thickness past 8 so I must update the bundle for only the first two records with a 1. The next two total to seven also and the following 2 total to four. We must set those bundles to 1, 2, 3 as follows
1 |19809 |A | 2 |1 |
2 |19809 |A | 2 |1
3 |19809 |A | 2 |1
4 |19809 |A | 2 |1
5 |19809 |A | 2 |2
6 |19809 |A | 2 |2 |
7 |19721 |B | 4 |1 |
8 |19721 |B | 3 |1 |
9 |19721 |B | 2 |2 |
10 |19721 |B | 5 |2 |
11 |19721 |B | 2 |3 |
12 |19721 |B | 2 |3|
These scenarios just keep repeating until the last record
Thank you for any help you can give me
View 7 Replies
View Related
Oct 1, 2004
The process of adding a column with DEFAULT (0) to a table that has 15million records takes a despicable amount of time (too much time) and the transaction log of the database grew to an unacceptable size. I would like to accomplish the same task using this procedure:
·Add the column to the table with null value.
·Loop through the table (500000 records at a time) and SET the value in the newly added column to 0.
·Issue a commit statement after each batch
·Issue a checkpoint statement after each batch.
·Alter the table and SET the column to NOT Null DEFAULT (0)
Here is my Sample script
ALTER TABLE EMPLOYEE ADD EZEVALUE NUMERIC (9,6) NULL
Go
Loop
UPDATE EMPLOYEE SET EZEVALUE = 0
Commit Tan
CHECKPOINT
END (Repeat the loop until the rows in EMPLOYEE have the value 0)
Go
ALTER TABLE EMPLOYEE ALTER COLUMN EZEVALUE NUMERIC (9,6) NOT NULL DEFAULT (0)
My problem is with the loop section of this script. How do I structure the loop section of this script to loop through the employee table and update the EZEVALUE column 500000 rows at a time, issue a Commit Tran and a CHECKPOINT statement until the whole table has been updated. Does anyone out there know how to accomplish this task? Any information would be greatly appreciated.
Thanks in advance
View 5 Replies
View Related
Dec 31, 2006
Hi,
If i have an SP called mySP that accepts one parameter @param
If I have a table of paramaters with only one column like this:
Param1
Param2
..
ParamN
How do I do if I want to execute the SP on all the table fields:
some thing like this:
Exec my SP 'Param1'
Exec mySP 'Param2'
...
Exec mySP 'ParamN'
I want that automatically since the parameters are going to be in a table called myTblParams
Notice that I don t want to pass all the parameters to the SP just once but only one value each time I execute the SP since mySP ccepts only one parameter.
Thanks a lot for guidelines
View 8 Replies
View Related
Mar 27, 2007
I have a large table that looks like this.
(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))
1, 1, p12, 1, p23, 2, p34, 2, p45, 3, p56, 3, p67, 4, p78, 5, p19, 5, p210,5, p83
i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.
can anyone help?
i have to use temporary tables. (not cursors-with cursors i know how to di it, but i want with temporary table)
thanks in advance
View 1 Replies
View Related
Mar 10, 2008
I have a table (temp_Order) wherein I use to insert a temporary order... after inserting the temporary order, when the buyer submits the order,I want to loop through the table and fetch the orders made by the buyer and insert those products on another table (Order)...how can I achieve this? im currently using sqlDataSource...
View 2 Replies
View Related
Jun 24, 2014
I have a master table containing details of over 800000 surveys made up of approximately 400 distinct document names and versions. Each document can have as few as 10 questions but as many as 150. Each question represents one row.
My challenge is to create a separate spreadsheet for each of the 400 distinct document names and versions containing all the rows and columns present in the master table. The largest number of rows would be around 150 and therefore each spreadsheet will not be very big.
e.g. in my sample data below, i will need to create individual Excel files named as follows . . .
"Document1Version1.xlsx" containing all the column names and 6 rows for the 6 questions relating to Document 1 version 1
"Document1Version2.xlsx" containing all the column names and 8 rows for the 8 questions relating to Document 1 version 2
"Document2Version1.xlsx" containing all the column names and 4 rows for the 4 questions relating to Document 2 version 1
I assume that one of the first things is to create a lookup of the distinct document names and versions assign some variables and then use this lookup to loop through and sequentially filter the master table data ready for creating the individual Excel files.
--CREATE TEMP TABLE FOR EXAMPLE
IF OBJECT_ID('tempdb..#excelTest') IS NOT NULL DROP TABLE #excelTest
CREATE TABLE #excelTest (
[rowID] [nvarchar](10) NULL,
[docName] [nvarchar](50) NULL,
[Code] .....
--Output
rowIDdocNamedocVersionquestionblankField
1document11q1NULL
2document11q2NULL
3document11q3NULL
4document11q4NULL
5document11q5NULL
6document11q6NULL
[Code] .....
View 9 Replies
View Related
Mar 28, 2008
Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005
TABLES
Category SubCategory (PK)CategoryName (PK) SubCategoryNameCategoryID SubCategoryIDDate Date (Just shows the date inserted) (FK)CategoryID
On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?
View 5 Replies
View Related
Sep 27, 2006
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
View 2 Replies
View Related
Sep 15, 2006
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
View 6 Replies
View Related
Jun 18, 2007
Hi, all experts here,
Thanks for your kind attention.
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 6 Replies
View Related
Jun 4, 2007
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 1 Replies
View Related
Sep 7, 2007
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
View 18 Replies
View Related
Mar 7, 2008
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
View 1 Replies
View Related
Nov 12, 2012
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC
So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.
View 3 Replies
View Related
May 26, 2014
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies
View Related
May 3, 2007
I receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks
View 1 Replies
View Related
Oct 14, 2015
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
View 4 Replies
View Related
Sep 18, 2014
A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.
I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.
The text before ;# (35106) should be inserted in to another column of the same table.
Below is the table structure:
create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)
View 2 Replies
View Related
Mar 11, 2015
when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?
View 5 Replies
View Related
Jan 11, 2008
Hi!
I have a "little" problem with nested case model:
-- "normal" database:
DROP TABLE [unitInfo] ;
GO
CREATE TABLE unitInfo (
unitID INT PRIMARY KEY
, beginDate SMALLDATETIME
, area VARCHAR(10)
, partSize INT
, y2predict MONEY
) ;
go
INSERT INTO unitInfo
VALUES (1, '2007-02-01', 'home', 42, 10.0) ;
INSERT INTO unitInfo
VALUES (2, '2007-03-05', 'home', 43, 11.0) ;
INSERT INTO unitInfo
VALUES (3, '2007-02-02', 'office', 11, 11.4) ;
INSERT INTO unitInfo
VALUES (4, '2007-02-01', 'office', 10, 33.6) ;
INSERT INTO unitInfo
VALUES (5, '2007-02-01', 'office', 42, 44.1) ;
CREATE TABLE unitLog (
id INT IDENTITY(1, 1)
PRIMARY KEY
, logtime SMALLDATETIME
, -- combination of logtime/unitID is unique
unitID INT
, -- "FK" on unitInfo
m1 FLOAT
, m2 FLOAT
)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 2, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 3, 63.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-02', 4, 432.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-02', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-03', 1, 423.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-04', 1, 432.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-05', 2, 43.0, 441.0)
INSERT INTO [unitLog]
VALUES ('2007-01-06', 2, 43.0, 4.0)
INSERT INTO [unitLog]
VALUES ('2007-01-06', 3, 43.0, 4.0)
INSERT INTO [unitLog]
VALUES ('2007-01-07', 1, 4.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 3.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-09', 2, 143.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-10', 3, 143.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-11', 4, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-11', 5, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-12', 2, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-13', 4, 413.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-14', 4, 43.0, 414.0)
INSERT INTO [unitLog]
VALUES ('2007-01-14', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-20', 1, 43.0, 414.0)
INSERT INTO [unitLog]
VALUES ('2007-01-22', 1, 43.0, 414.0)
-- SSAS:
CREATE MINING STRUCTURE NestedStructure
( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE
, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS
, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS
, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS
)
)
ALTER MINING STRUCTURE NestedStructure
ADD MINING MODEL nestedModel ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR
,y2predict REGRESSOR PREDICT_ONLY
, logdata ([id] , unitID
, m1, m2
)
) USING Microsoft_Decision_Trees
/* version 1*/
insert into NestedStructure ( unitID, beginDate, area, partSize, y2predict
, logdata(skip,unitID, m1, m2))
openrowset('sqloledb', Server=myserver;Trusted_Connection=yes;,
'Shape {select * FROM mydb.dbo.unitInfo }
Append ( { select id, unitID, m1, m2 from mydb.dbo.unitLog }
Relate unitID to unitID ) as logdata ')
Parsing the query ...
OLE DB error: OLE DB or ODBC error: Syntax error or access violation; 42000.
Parsing complete
Where is the error?
/*version 2*/
CREATE MINING STRUCTURE NestedStructure1
( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE
, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS
, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS
, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS
)
)
ALTER MINING STRUCTURE NestedStructure1
ADD MINING MODEL nestedModel1 ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR
,y2predict REGRESSOR PREDICT_ONLY
, logdata ([id] , unitID
, m1, m2
)
) USING Microsoft_Decision_Trees
insert into mining structure NestedStructure1 ( unitID, beginDate, area, partSize, y2predict
, logdata(skip,unitID, m1, m2))
Shape {openquery(dsnDB,'select * FROM mydb.dbo.unitInfo') }
Append ( { openquery(dsnDB,'select id, unitID, m1, m2 from mydb.dbo.unitLog') }
Relate unitID to unitID ) as logdata
Parsing the query ...
Error (Data mining):
INSERT INTO error: The '[logdata].[id]' nested table key column is not bound to an input rowset column.
Parsing complete
Remark that combination logtime/unitID is the natural key in unitLog.
"ID" is the surrugate key.
What is wrong here...?
View 6 Replies
View Related
Mar 25, 2008
Table structure as follows
Employee
Empno empname salary
commission
I want to have an other employee table named employee_modified
Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)
Is that possible to do it.. or am I doing something wrong.
something like
Select empno , empname , salary , commission,
(salary + commission) as derived_colum1 ,
(derived_colum1 + xxxxx) as derived_colum2 ,
(derived_colum2 + xxxxx) as derived_colum3
into employee_modified from employee
View 3 Replies
View Related
Apr 28, 2008
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
View 1 Replies
View Related
Jul 16, 2015
Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:
Text QueryPlan Plan_handle
Name Value
select id,name,Address from person <showPlznXML... 010101 Table Person
select id,name,Address from person <showPlznXML... 010101 column id
select id,name,Address from person <showPlznXML... 010101 Table Person
[code]....
View 26 Replies
View Related
Jul 11, 2007
When you use "Alter Table add Column", it adds the column to the end of the list of fields.
How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?
Create table T1 ( a varchar(20), b varchar(20), c varchar(20))
Alter table add column x varchar(20)
so that the resulting table is
T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)
Can this be done programmatically?
View 33 Replies
View Related
Mar 4, 2008
How do I update the OrderGUID column in Table B with Values from OrderGUID column in Table A. I have already populated the OrderGUID column in Table A using NEWSEQUENTIALID(). Now I need to populate the OrderGUID column in Table B with Matching GUID values from the OrderGUID Column in Table A.
Does any one have a script to accomplish this task. thanks
View 4 Replies
View Related
Aug 3, 2006
i have a table
table1
column1 int not null
column2 char not nul
column3 char
i want to script a change for table1 to alter column1 to be the table identity column. not primary.
View 5 Replies
View Related
Jun 13, 2012
I have a table where one column is an id card number (col1) and another is another column (col2). In col2 I have various values for each col1 ie col1 can have various values of col2. How can i insert col1 concatenated for each col1 in another table?
View 2 Replies
View Related
