I have the problem of dynamically changing a field value in a row depending on the value of the same field in the previous row, assuming the table is sorted on that field.
:(
You may consider that field as a key of same sort as other values in the table do not help in selecting them uniquely.
Ex.
Before After
130 -> 130
130 -> 130A
140 -> 140
140 -> 140A
Consider this scenario.
I have two database in the sql server and consider that i have a query which has 4 tables inner joined.
When i execute the query in the database1 , the query is returning rows, But when i execute the same query in the database2, the query is not retuning rows . I know that the
no rows are returned because of missing data in the database2. But have no idea how to trace what values are missing in the database2. Please note the tables is having a huge
list of records by which manually comparison is painfull. Please consider i dont have any background idea of the values in the tables but just using it. Any help would be
appericated.
I'm using SQL Server 2005 Express.
In my main database table I have many fields but the two following fields are my main concern.
1) email_address
2) unsubscribe
In my secondary database table I have one record only.
1) email_address
What I want to accomplish... I want to compare the email_address of the secondary database table to the email_address of the main database table and if it exist, change the value of the unsubscribe field. (or if I can't do that, then delete the record within the main database table completely.)
I'd really appreciate any help I can get.
Thanks,
Bill
Hi All,
I would like to know whether it is possible to add and updated date column in a slow changing dimension table using the slow changing dimension data flow transformation.
I would like to keep track of what record is updated in the dimension table based on the data being processed.
Thanks for you help and information
Regards,
Fadzli
I have a field called Trainee1 and there are about 3 in every 10 records that the value is <NULL> - I want to change that value to 0.
I have tried to use this command but it changed EVERY record to <NULL>
update Trainee1 set RegId=replace(RegId,'NULL','0')
Has anyone any suggestions on how to just change the records where the value of traineeId is <NULL>???
Thanks in advance,
Anthony
What is the best way to design your tables in cases where field values change?
Example:
CREATE TABLE Product (ProductID INT, Description VARCHAR(32), Price SMALLMONEY...);
CREATE TABLE Purchase (PurchaseID INT, ProductID INT, Quantity INT);
Since price obviously change over time, I was wondering what the is the best table schema to use to reflect these changes, while still remembering previous price values (like for generating reports on previous sales...)
is it better to include a "Price SMALLMONEY" field in the purchases table (which kind of de-normalizes it) or is it better to have a separate ProductPrice table that keeps track of changing prices like so:
CREATE TABLE ProductPrice (ProductID INT, Price SMALLMONEY, CreationDate DATETIME...);
and have the Purchase table reference the ProductPrice table instead of the products table?
I have used both methods in the past, but I was wanted to get other peoples' take on it.
Thanks
How can i change the default value of a column? I already have a columnnamed DateOfRental but I want to alter it so that it has default valuegetdate()ThanksDavid--http://www.nintendo-europe.com/NOE/...=l&a=Prodigious
View 4 Replies View RelatedHi!
We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.
What am I missing here?
BR John
Hi Guys,
I have had a look through the forum and I can't seem to be able to find how to do this... I have a vague recollection of achieving this before but it was many years ago since I've done any sql.
I have a table like this
StaffID JobNumber Status
------- --------- ------
1 50 'In Progress'
2 20 'On Hold'
3 30 'Completed'
4 40 'Completed'
what I want to be able to do is change the status to something common if it is not 'Completed' I don't want to update the database though, I just want to return it in a select statement. Is this possible.
My output would be something like;
StaffID JobNumber Status
------- --------- ------
1 50 'NOT Completed'
2 20 'NOT Completed'
3 30 'Completed'
4 40 'Completed'
Many thanks in Advance.
Bobg
Aim- the following columns “Cancel_Date”, “First_Post_Date”,“Last_Post_Date” can either be populated with figures or have a null value.
I want all Null values to be removed and be replaced with a blank
Final table is
Select *
from #test
left join #SF_AccountBuild on #test.FDMSAccountNo = #SF_AccountBuild.[External FDMSaccountno]
Results
[FDMSAccountNo],
External_ID ,
Parentsfid,
[DBA Name],
[Legal Name],
Street,
[MM3-DBA-ADDR2],
[Code] .....
I want to be able to open URLs in a new window in my SSRS report, and the URLs keep changing in every record. The problem is these links are not the only things present in the record, and there is other data present. I keep seeing examples where they are considering only a link to be present in the record with no other data.
Also the data in the record includes HTML tags and I checked the option that says "HTML - Interpret HTML tags as styles" on the placeholder properties window.
Example of data in the record:
Hello! Welcome to google, the most used search engine.
Click on www.google.com to go the website
So when I click on the www.google.com URL, it should open in a new window.
Here's the deal:
I import a flat file from a legacy system, and then convert it into a single table. That works simply enough.
Then I have a SP that querys that table using a parameter for an accountID. My business tier calls that SP and returns the results to the calling tier (my web application). Easy enough...
Now for the question. The people who created the flat file (written in COBOL) decided to use "codes" to represent data. So, for instance, if I'm looking for the account plan, I can expect to see characters like ], or [, or +, etc... These characters have a special meaning, like:
] = Plan A
[ = Plan B
+ = Plan C, and so on.
Currently, the web application displays those characters, but I want it to display the actual plan name. Is there a way that when I execute the SP, the SP could pull the necessary records, and whenever it encounters a certain "plan" character, it could convert it into a "readable" name? Say that it sees that the plan_type field has a value of "]" for twenty records, so it converts those twenty records' plan_type value from "]" into "Plan A"? I'm not sure if I can do that, but I want to at least evaluate the option if I can.
I've evaluated other options, like using a CASE statement in my code, but I shot that down quickly...for obvious reasons. I don't wanna be changing my web application or business tier each time these guys update a plan name, or add a new one, delete an existing one, etc...
I've also thought about creating a dictionary table than contains the plan's code and its name, and then just INNER JOIN the first table with the dict table. This would keep my SP very simple (it's very straight-forward right now, and I like that). That way, if a plan name is ever changed, or a new one is added, I simply update the dict table using a simple query. However, if my SP is doing the conversion, I could just as easily update the SP.
Either of these methods would work for me, and I *do* know how to do the latter (dict table). However, there are quite a few other fields that I may have to do this for. I believe when I left for the day on Friday, my last count was 14 fields total that needed translation. That would mean 14 different dict tables! That could certainly affect my SP performance with all those INNER JOINS!
Therefore, I'm certainly interested in figuring out if it's possible to do the former method (SP), and then I shall decide which method is best for my situation.
Feel free to include your thoughts on which process you think is better as well. I'm really riding the fence with this one. However, if I can't find out how to change field values in my SP, then obviously I'll make a decision very quickly...
Thanks in advance.
I've got this virus URL... in our production server. It is changing the values in a table column.
View 6 Replies View RelatedHello,i have a table and if a record is inserted i will test a numeric valuein this table. If the this value is greather as 1 million, than anstatus column should be changed from 'A' to 'B'.Yes and sorry, this is a newbie question.On Oracle this work simple:create trigger myTrigger on tableXasbeginif :old.x 100000 then:new.y:='B'end if;end;ThanksMaik
View 2 Replies View Related
Has anyone seen this issue before? We are running a SQL CE 3.5 database on a windows desktop. A couple of our tables have ntext fields. When we do an insert the statement updates the value for all rows, not just the one that was added. I can easily repro this with some of the online samples too. Try the following:
SqlCeConnection conn = new SqlCeConnection(_sConn);
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE BlobTable(name nvarchar(128), blob ntext);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO BlobTable(name, blob) VALUES (@name, @blob);";
SqlCeParameter paramName = cmd.Parameters.Add("name", SqlDbType.NVarChar, 128);
SqlCeParameter paramBlob = cmd.Parameters.Add("blob", SqlDbType.NText);
paramName.Value = "Name1";
paramBlob.Value = "Name1 Memo";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO BlobTable(name, blob) VALUES (@name, @blob);";
SqlCeParameter paramName = cmd.Parameters.Add("name", SqlDbType.NVarChar, 128);
SqlCeParameter paramBlob = cmd.Parameters.Add("blob", SqlDbType.NText);
paramName.Value = "Name2";
paramBlob.Value = "Name2 Memo";
cmd.ExecuteNonQuery();
After the second execution the blob column in both rows will have the value 'Name2 Memo'.
This is obviously a huge problem for us and would appreciate it if someone can explain what is happening. Seems like a bug but would like to be certain before I go the support route.
Hi
I'm working with merge replication between Sql Server 2005 and Sql Server 2005 Mobile. I'm using dynamic filtering by function HOST_NAME (... where Table.FilteredColumn = HOST_NAME())
When I'm trying to chagne values from filtered column on the client side (Table.FileteredColumn) I get error message that the column is read-only.
How can I change data in filtered column? Is this possible?
I have a report I want to modify and when I go to deploy it I get:
Changing the report parameters or data sources to the values you specified is not allowed. The report is configured to run unattended. Using the specified values would prevent the unattended processing of this report
Now I understand the error, it is because on the report server's web management interface I entered credentials for the report to run that point to a custom datasource and supplied the appropriate credentials to run the report.
However, I don't want to have to reconfigure that just to deploy a slightly modified version of the report.
How can I get around this? Or what is the proper work around? I just want to change the background color from dark gray to light gray on a header column....
Thanks!
I can't figure this one out...
I have an Excel spreadsheet (Excel 2003 format) with a column of values. The column is formated to General (although I've tried it formated to text). The values are alphanumeric. However, there are a few values that are completely numeric.
Example:
_ITEMNUMBER_
1-528
K214-5
184PR
45678
As can be seen, the last value is completely numeric. I am importing into a table with one column that is formated as nvarchar(50).
I use dtswizard.exe and do a simple import into the table and the alphanumeric values import just fine but anytime a value is completely numeric it has a NULL value in the table instead of the value that should be there.
I've tried changing the format of the column to nchar, char, etc, etc. Always comes in NULL.
Any ideas?
Seems that when i use the edit functionality of the formview, and there are NULLs in the record, that my sql update statement (default from the FormView wizard) doesnt work (nothing updates). However, if i populate all fields, everything works fine. I have modified the FormView edit template to add my own textboxes and dropdowns to simplify the user record editing.
Because it works when all fields are populated with non-NULL values, Seems my Bind's are all correct.
---Jim
Hi all,
I wanted to check the previous and next record values.
For example:
sKey NextKey PreviousKey
1 2 Null
2 8 1
8 5 2
5 null 8
I wanted to check the value of NextKey of Prev record and Skey of Next record.
Any idea?
Regards
Helen
Here is some sample data:
CREATE TABLE
#MyTable
(
Pk INT,
GroupID INT,
Value VARCHAR(10)
[code]...
I am looking to retrieve any GroupID in which every Value of that GroupID is either (a) null, (b) an empty string, or (c) "XYZ". So in the above example, GroupID #1 would not be returned in my query because there is a Value of "ABC", but GroupID #2 would be returned since it consists of only nulls, "XYZ"'s, and empty strings.What would be the most efficient way to write such a query?
SELECTIndustry,
100.0 * SUM(CASE when ceoischairman = 'yes' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [YesPercent],
100.0 * SUM(CASE when ceoischairman = 'no' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [NoPercent]
FROMTCompanies
GROUP BYIndustry
ORDER BYIndustry
This code above is working as I need it but I need to insert some additional functionality. Thanks
I need to add something like this:
IF YesPercent > NoPercent
UPDATE tableX SET CEOIsChairman='Yes' WHERE Industry='<the industry value being evaluated>'
Else If NoPercent > YesPercent
UPDATE tableX SET CEOIsChairman='No' WHERE Industry='<the industry value being evaluated>'
Else
UPDATE tableX SET CEOIsChairman='Equal' WHERE Industry='<the industry value being evaluated>'
End
Hi All -
I have been killing myself over this -
For the attached table,I want to retrieve records that have a minimum Price for all the groups of Category, Symbol, Date
CategorySymbolDate PriceCoupon
1 IBM01/05/20061005
1 IBM01/05/2006995.8
1 IBM01/06/20061005
1 IBM01/06/20069610
2 IBM01/05/20061026
2 IBM01/05/20061055.7
For a given symbol, date and Category, I want to pull in records with the min(Price). My problem is that if I say,
Select min(Price)
,symbol
,date
,Category from TEST group by symbol, date, Category
I lose the coupon information that is attached to the record that has minimum price and maximum price within the group of symbol, date and Category
My results from the above table should be –
CategorySymbolDate PriceCoupon
1 IBM01/05/2006995.8
1 IBM01/06/20069610
2 IBM01/05/20061026
Thanks for all your help.
Hello,I know that I've seen this question asked on here before, but I can'tfind an answer that gives me the performance that I need.I have a table that stores events for users:CREATE TABLE Lead_Action_History (lead_action_seq INT IDENTITY NOT NULL,lead_action_date DATETIME NOT NULL,lead_seq INT NULL,operator_id VARCHAR(20) NOT NULL,call_time INT NOT NULL,CONSTRAINT PK_Lead_Action_History PRIMARY KEY (lead_action_seq) )GOThe table has a foreign key to another table through the lead_seqcolumn:CREATE TABLE Lead_Master (lead_seq INT IDENTITY NOT NULL,state CHAR(2) NOT NULL,CONSTRAINT PK_Lead_Master PRIMARY KEY (lead_seq) )GOI need to write a query that will give me a sum of call_time brokendown by a column that is in the table joined through the lead_seq.However, if the lead_seq for a row is NULL then I need to use thelead_seq for the previous row (based on lead_action_date) for the sameoperator.This is what I came up with:SELECT LM.state, SUM(call_time)FROM Lead_Action_History LAHINNER JOIN Lead_Master LM ON (LM.lead_seq = LAH.lead_seq)OR (LAH.lead_seq IS NULLAND LM.lead_seq = (SELECT TOP 1LAH2.lead_seqFROMLead_Action_History LAH2WHERELAH2.operator_id = LAH.operator_idAND LAH2.lead_seqIS NOT NULLORDER BYLAH2.lead_action_date DESC))GROUP BY LM.stateThe problem is that Lead_Action_History has millions of records andany solution that I've found involves one or more subqueries on itwhich kills performance. I am going to look at using a covering indexwith the solution above, but I thought that someone here might haveanother way of doing this.I can't really change the structure, but I can play with the indexing.I would still be curious though how other people model this type oftemporal data in a way that makes it easy to work with.Thanks!-Tom.
View 4 Replies View RelatedHi all,
I wanted to check the previous and next record values.
For example:
sKey NextKey PreviousKey
1 2 Null
2 8 1
8 5 2
5 null 8
Ex : In the first record of the table, the NextKey is pointing to 2.
So the next record of Skey will be 2. The Next Key for this record is 8. Like wise the next record of this should have the Skey as 8.
Now I need to check whether the NextKey and SKey are correct for all rows.
For that I need to check the previous record of "Next key" and next record of "Skey".
Any idea?
Regards
Helen
I'm sorry, this question has a pretty long preamble, but bear with me as this will fully explain my problem, and save a number of clarification questions.I am using Visual Web Developer 2005 Express, with SQL Server 2005. After reading a Microsoft article saying that it was much better to use a three tier architecture with data adapters than to write explicit queries, I have used this approach extensively, and generally it works very well. Typical code patterns are: - Dim taPassenger As New ShippingTableAdapters.VPassengerTableAdapter Dim tbpassenger As Shipping.VPassengerDataTable Dim trpassenger As Shipping.VPassengerRow ..... ' to process all passengers from a voyage: - tbpassenger = taPassenger.GetDataByVOYid(Voyageid) For Nbr As Integer = 1 To tbpassenger.Count trpassenger = tbpassenger(Nbr - 1) ' I now have the row available in properly-typed fields, eg trpassenger.vpxName ... Next ' to read and process a single passenger tbpassenger = tapassenger.GetDataByVPXid(VPXid) trpassenger = tbpassenger(0)The table adapter has been created with Insert,Update, Delete methods. Thus you can assign values to row fields, and then update the row: - trpassenger.VPXName = "New Name" .... tapassenger.update(trpassenger)There are actually several overloads of the update method: you could have written a field list, like this, where the names (VPXid etc) would be defined in my program as variables of the appropriate type for the corresponding column. tapassenger.update(VPXid, VPXName, VPXAge, ....HOWEVER, this doesn't work properly if you have non-string data that could be null. Thus if the fourth field is a foreign key that could be null, if you write Dim VPXVoyId as Nullable(of GUID) VPXVoyid = nothing tapassenger.update(VPXid, VPXname, VPXAge, VPXVoyid, ...or Trpassenger.VPXVoyid = Nothing tapassenger.update(trpassenger) you do NOT put a null value into the database. Instead you store a value of "00000000-0000-0000-0000-000000000000". You can solve this annoying problem in two ways: -A. If you will ALWAYS be storing a null value at this point of your program, then simply write the update statement like this: - tapassenger.update(VPXid, VPXname, VPXAge, Nothing, ...Here "Nothing" actually means "DBNull" even though it doesn't mean this when used in a normal VB assignment (and neither does DBNull).B You can use the SetxxxxNULL method: - If trpassenger.VPXVoyid = nothing then trpassenger.SetVPXVoyidNull End If tapassenger.update(trpassenger) Thus when the program logic is complex and there are many paths to the update statement I will often put statements such as this in front of the update statement, using SetxxxxxNull for all non-string values that may be null. You don't need to worry about string values because the Nullvalue property can be used to simply say that Null values are empty, and it all works properly, but for reasons unknown Microsoft have prohibited this for other data types. Now at last we get to my question, which is "How do I do the equivalent for an Insert?" tapassenger.insert does not have any overloads, and the only form is tapassenger.insert(field list). But this creates the problem above: if VPXVoyid = nothing then I insert the not-null value of "00000000-0000-0000-0000-000000000000". If I COULD use the form tapassenger.insert(trpassenger), then I'd have another problem: if you haven't set trpassenger (for example with trpassenger = tbpassenger(0)) then it will not exist, so you will get an exception when you try to reference it. Yet you can't write If isnothing(trpassenger) then trpassenger = new shipping.vpassengerrow) (I've tries this both as an assignment, and as a Dim statement) end ifSo far I can think of only two solutions, both awful: -a/ Use multi-choice logic to choose an appropriate insert statement. If there is only one or two possibly-null fields, I could write:- If field4 = nothing then trpassenger.insert (VPXid, VPXname, VPXAge, Nothing, .. However if there is more than one possibly-null field, you need (2 to the power of nbr-of-possibly-null-fields) insert statements, with of course a lot of testing to choose which oneb/ I could insert the record with nulls, then read it back and update it. Surely there is a better way! Help! Thank you, Robert Barnes.
View 2 Replies View RelatedDear friends,
I am thinkking there has to be away to do this in a SQL
statement.
can't you do some kind of...
Create Procedure sp_update_users
@User_Id Int,
@Title_Id Int
AS
UPDATE USERS
DELETE WHERE @User_ID = User_Id and @Title_Id = @Title_Id
ELSE
INSERT INTO USERS VALUES (@User_ID,@Title_Id;)
HI ALL,
CAN ANYBODY HELP ME TO SOLVE THE ISSUE
I WANNA UPDATE A RECORD OF ONE TABLE WITH VALUES THAT ARE PRESENT IN A COLUMN OF ANOTHER TABLE
OR
CAN I DO THE SAME USING INSERT QUERY
THANKS IN ADVANCE
Distinct name that match both subjects (math, science) from classname in level 2 only. Not sure where to even start. Example table below:
name subject level
bob math 2
hank math 1
joe science 2
bob science 2
joe math 2
ben science 2
carl science 1
Hello, I'm trying to accomplish 3 things with one stored procedure.I'm trying to search for a record in table X, use the outcome of thatsearch to insert another record in table Y and then exec another storedprocedure and use the outcome of that stored procedure to update therecord in table Y.I have this stored procedure (stA)CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL)ASSET NOCOUNT ONSELECT OType, Status, SSN, FName, LNameFROM CustomersWHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum)GO.Then, I need to create a new record in another table (Y) using the SSN,FName and Lname fields from this stored procedure.After doing so, I need to run the second stored procedure (stB) Here itis:CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL)ASSET NOCOUNT ON-- select the recordSELECT OrderID, OrderDate, SSNFROM OrdersGROUP BY OrderID, OrderDate, SSNHAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum)GO.After running this, I need to update the record I created a moment agoin table Y with the OrderDate and OrderID from the second storedprocedure.Do you guys think that it can be done within a single stored procedure?Like for example, at the end of store procedure A creating an insertstatement for the new record, and then placing something like execprocstB 'SSN value'? to run stored procedure B and then having aupdate statement to update that new record?Thanks for all your help.
View 1 Replies View RelatedHi, I have a difficult case that I need to solve. I will try to be the very clear explaining my problem:
I have a sql query which brings me many records.
This records have a column in common which have the same value (COL1)
There is a second column (COL2) which has different values bewteen these records.
I need to concatenate values from the second column in records with same value in COL1. And I need only one record of the ones that have the same values. If two records have the same COL1 value, only one row should be in my result.
Let me give you an example:
COL1 COL2
RECORD1 1-A HHH
RECORD2 1-A GGG
RECORD3 1-B LLL
RECORD4 1-B MMM
RECORD4 1-B OOO
RECORD5 1-C NNN
Me result should be:
COL1 COL2
RECORD 1-A HHHGGG
RECORD 1-B LLLMMMOOO
RECORD 1-C NNN
It is clear what I need? I dont know if I can solve it through sql or any function inside SSIS.
Thanks for any help you can give me.
I have been researching BOL and other online resources but cannot seem to get a definitive answer.
Current Output:
[MemberID][Category][Type]
12345ABCtest
12345XYZtest
12777ABCtest
12888FGDtest
Desired Output:
[MemberID][Category][Type]
12345ABCtest
12777ABCtest
12888FGDtest
Query:
SELECT m.MemberID,
vw.Category,
vw.Type,
FROM dbo.TestVW vw JOIN
dbo.TestMember m ON vw.MemberKey = m.MemberKey
WHERE vw.Type = 'test'
GROUP BY m.MemberID,
[Code] ...
but cannot seem to be able to return one record with its corresponding value criteria.
May i know what is the SQL query to create another intermediate column which is used to store the sum of two columns of each and every record. Thank you.
View 16 Replies View Related