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
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 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?
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
Hi all,
One of my columns is the table has some Null values, and I Would like to stop having NULL values into that column any more.
I know, If I alter the column to NOT NULL will throw me an error, since it does a batch update.
Is there any way to achieve this...
Thanks...
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
View 12 Replies View RelatedI have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
Hi
can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.
If Not Row.Datum1_IsNull Then
Row.OutputDatum1 = Row.Datum1
Else
Row.OutputDatum1 = CDate(System.Convert.DBNull)
End If
Any help welcome.
Hi,
My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list
e.g.
Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -
At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?
Thanks
Dan
Hello DBAs:
I am having difficulty constructing a where clause. I have 4 columns in a table. Col1 is never NULL. Col2,3,4 may or may not contain NULL values. Here is the situation.
1. Col2,3,4 are NULL
WHERE Col1 = condition
2. Col2 is NULL, 3 may or may not be NULL. When Col3 is NOT NULL, Col4 may or may not be NULL
where col1 and (col3(Not Null values) OR Col4(Not Null values)) = condition.
How do I construct this logic. Please help
Hi I have a table, which contains Char and NChar NOT NULL columns
Now I need to change it to NULL, when I use the following command, it fails for the following error,
The command I used,
ALTER TABLE <TableName>
ALTER COLUMN <ColName> CHAR NULL
ALTER TABLE <TableName>
ALTER COLUMN <ColName> NCHAR NULL
Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.
But for the same table, the below command executes fine,
ALTER TABLE <TableName>
ALTER COLUMN <ColName> SMALLINT NULL
Also I can change the NULLABILITY from NOTNULL to NULL using Enterprise Manger, editing the table using Table Design and selecting Allow Nulls option.
I need a script to accomplish this task.
Any help would be greatly appreciated.
-Senthil
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.
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
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
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
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!
Hi, I ve a table, I want to fetch certain rows based on the value of a Column. That column is nullable, and contains NULL values.I used the following query,SELECT Col_A FROM TABLE1 WHERE SOME_ID = 1317 AND Col_B NOT IN (8,9) Here, Col_B contains NULL values too. I need to fetch all rows where Col_B is not 8 or 9.Now, if I use "NOT IN", it does not work. I tried reading on it and got to know why it does not work. Even "NOT EXISTS" does not help. But still I've to fetch my values. How do I do that?Thanks & Regards,Jahanzeb
View 6 Replies View RelatedI have tried doing a search, as I figured this would be a common problem, but I wasn't able to find anything. I know that my SP is functional because when I use VWD execute the query outside of the webpage, I get the correct results -however I have to ensure that a field is either entered, or set to <NULL>. In my SET's I want it to use the wildcards.
What I want is to do a search (plenty of existing topics on that, however none were of help to me). If a field is entered, then it is included in the search. Otherwise it should be ignored. In my VB I have the standard stored procedure call, passing in values to all of the parameters in the stored proc below:
CREATE PROCEDURE dbo.SearchDog@tagnum int,@ownername varchar(50), @mailaddress varchar(50),@address2 varchar(50),@city varchar(50),@telephone varchar(50),@doggender varchar(50),@dogbreed varchar(50),@dogage varchar(50),@dogcolour varchar(50),@dogname varchar(50),@applicationdate varchar(50)AS IF @tagnum=-1 SET @tagnum=NULL SET @ownername = '%'+@ownername+'%' SET @mailaddress = '%'+@mailaddress+'%' SET @address2='%'+@address2+'%' SET @city = '%'+@city+'%' SET @telephone='%'+@telephone+'%' SET @dogcolour='%'+@dogcolour+'%' SET @dogbreed='%'+@dogbreed+'%' SET @dogage='%'+@dogage+'%' SET @doggender='%'+@doggender+'%' SET @dogname='%'+@dogname+'%' SET @applicationdate='%'+@applicationdate+'%'
SELECT DISTINCT * FROM DogRegistry WHERE ( TagNum = @tagnum OR OwnerName LIKE @ownername OR MailAddress LIKE @mailaddress OR Address2 LIKE @address2 OR City LIKE @city OR Telephone LIKE @telephone OR DogGender LIKE @doggender OR DogBreed LIKE @dogbreed OR DogAge LIKE @dogage OR DogColour LIKE @dogcolour OR DogName LIKE @dogname OR ApplicationDate LIKE @applicationdate ) AND TagNum > 0GO
I don't know why it is creating links inside my SP -ignore them. TagNum is the primary key, if that makes a difference.
On the webpage, it ONLY works when every field has been filled (and then it will only return 1 row, as it should, given the data entered). Debugging has shown that when nothing is entered it passes "".
Any ideas?
I am trying to retrieve data from two different tables. One of the tables has more than 20 columns some of which are null. I would like to retrieve data from both tables excluding the columns which have null values. How do I do this?
View 3 Replies View RelatedWould this take care of null values in either a.asset or b.asset?
SELECT convert(decimal(15,1),(sum(isnull(a.asset,0))/1000.0)+(sum(isnull(b.asset,0))/1000.0)) as total_assets
What's throwing me off is that there are multiple a.asset or b.asset for each unique ID. It seems to work, but I'm not following the logic too well. If I were doing this in another language, I would loop through, summing a.asset and b.asset wherever it's not null for each unique ID.
Hi,
How can I use "Derived Column" to check if a Datetime value is null or not and if null to insert 00/00/00 instead. ?
The background being that while using a "Derived Column" to change a Column from a (DT_DATE) to a (DT_DBTIMESTAMP) everytime I get a null value it see's it as a error.
And the column in particular has ~ 37 K blank / null fields so Im getting a lot of errors
So far I have tried to use something like
ISNULL([Column 34])
Or
SELECT ISNULL(ID, '00/00/0000') FROM [Column 34]
Or
SELECT ISNULL(au_id, '00/00/0000 00:00') AS ssn
FROM [Column 34
but none seems to work [Column 34] being the offending column.
What a normally use is just a simple "(DT_DBTIMESTAMP)[Column 34]"
in the expression column, which seems to work well, but here I get alot of errors
Any ideas?
I set up a new SQL database file, in that file I allowed nulls, When I went through code to save the record, the exception is saying it doesnt allow nulls.
Before I get to involved with SQL, is it a bad practice to use nulls?
If it is what do you enter in place of the null value,
which will lead to more code, right?
Davids Learning SQL
hi ive got a inert sub where i grab values from text boxes etxthe values are passed to a stored procedure however , one of these fields is a date field , but the field is not required ...so on this line if the date text box is left blank i get an error , not a valid date .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Texti have tried ( the actiondate field can take nulls ..)if txtActionDate="" then .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = nothing else.Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Textend if but this doesnt workwhat is the best way of allowing blank values to be passed to the stored procedure( it doesnt fall over with normal text / varchar fields ) thanks
View 1 Replies View RelatedI have a sproce that accepts null for one of its parameters I can execute the sproce and enter null and it works fine, it returns all rows. When I try doing this with my GridView and the SQLDataSource it does not work. I need some help in understanding how the SQLDatasource wants a null. Here is what the parameter row of the SQLDataSource looks like.
<asp:ControlParameter ControlID="EnteredByText" DefaultValue="Null" Name="EnteredBy" PropertyName="Text"
Type="String" ConvertEmptyStringToNull="true" />
In my sproce I have setup the parameter as follows;
@EnteredBy Nvarchar(50)=Null
In my WHERE Clause I have:
WHERE (tblClient.EnteredBy = @EnteredBy OR @EnteredBy IS NULL)
How can I check on NULL values in a Select statement?
SELECT ID FROM TabelWHERE somecolumn <> NULL??
I wonder if someone out there can help me. I am writing an ASP application to query a MSSQL database. The users will be able to use one or all of 4 columns. There may be time when the columns are empty (null). How can I write a select query to ignore null values? A rough example of what I am talking about it below.
select * from table where value1='something' value2=<null> value3='something' value4=<null>
I would like to ignore the null values so that in effect the statement would just do the following.
select * from table where value1='something' and value3='something'
I realize my syntax is wrong but I think you get the idea. Any thoughts?