I have a field that may be null which is valid, and I am finding
something I didnt expect when working with nulls.
SELECT NULL/4.0 will return NULL (which I expect), however, when I test
it with a case it does not:
SELECT NULL/4.0 AS 'TEST1', TEST2 = CASE NULL/4.0 WHEN NULL THEN
'HURAY' ELSE 'OH DARN' END
I can work around by testing for NULL first else CASE ..., but I'd like
to understand why the CASE does not test for null.
TIA
rob
SQL 2005 Enterprise x64, SP1
I have an instead of trigger for update on particular table which adds3 or so columns and puts the total in a 4th No matter what the input is(as long as there is one NULL) the total is always NULL. More thanlikely is that only one of the fields has a value in it and the restnull but i don't want to do the MAX in case that is not true.a quick visual aid from input and desired outputInput DatacolA colB ColC tot5 NULL NULL NULLCurrent OutputcolA colB ColC tot5 NULL NULL NULLDesired OutputcolA colB ColC tot5 NULL NULL 5How do i Manipulate the TSQL command to work correctly?Update tbl_lossChecksSETclc_totalFees = Inserted.mny_LegalFees + Inserted.mny_AdjusterFees +Inserted.mny_Expense ,
I'm trying to use a DTS package to import data from an excel file. A few tables keep throwing errors about not being able to insert null values.
Is there any way to just skip a row when a column is null? I know there are certain columns that will never be null, so if they are null, I know that I just have a messed up row in Excel.
I could probably do it by implying an SQL query and having it ignore null rows, but I can't find anything useful on SQL syntax where Excel files are concerned.
Hi folks,I'm doing calculations based on data in a table, but the data has somezeros in the field I'm dividing by. I'm trying to write a script toreplace any field with 0 or null with 1, but it's not working. HEre'swhat I've got:Update A Set A.deptcode = A.deptcode,A.type = A.Type,A.Volume = (case A.VolumeWhen Null Then 1When 0 then 1Else A.VolumeEnd)From Data_Unsorted A Join Data_Unsorted B OnA.deptcode = B.deptcode and A.type = B.TypeMy table is data_unsorted and deptcode and type are my primary keysVolume is the item I want to put 1 if null or zero, and I'd thing theabove statement would work, but it doesn't. This table has 383 rows,and it says it updates 383 rows, but when I run the following query totest:select a.deptcode, a.type, a.volumefrom data_unsorted awhere a.AveMonthVolume = 0 or a.AveMonthVOlume is nullIt didn't work... still TONS of nulls and zero's. Is there a trick tothis???Thanks,Alex.
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table - Unable to modify table. Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails. The statement has been terminated.
is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Can someone tell me what to do if several NULLS are showing up within tables. Is there an easy step to locate all NULLS and delete them within the table?
Can someone provide detailed infromation on how to delete NULLS within tables. I'm new o the SQL side so I hope I have explained what it is that I'm looking to do. Our in house programmer said that the NULLS maybe causing a problem with the software communicating with the SQL Server software.
Hi i am new to SQL, and i have a data base thats got NULLS in it is there a query i can run, which can take the NULL value out ? anything will be greatly appreciated.
Hi,I don't have any specific details as I do not really understand whythis is happening but we have a customer database that we have beenusing and queries on text fields have begun returning empty stringvalues instead of NULL.So for example:SELECT TAB1.DESCRIPTIONFROM TABLE1 TAB1WHERE TAB1.DESCRIPTION IS NOT NULLORDER BY 1may have returned 50 records in the past (purely an example). It isnow returning an additional record first that appears just to be ''.Now I am not too sure where to begin looking into this. ODBCconnections, collation settings? I just am not sure where to begin.The column will not have had '' inserted into it; therefore it shouldbe NULL.I have posted this mainly so I can see if anybody else has seen thissort of behaviour - I cannot see why this has suddenly happened.The collation setting on our server is different to that of thecustomer DB, but this shouldn't make a difference should it?If anybody has any ideas then I can post some more information.Thanks,Paul
create table t1(c1 int, c2 varchar(10))insert t1 values(1,'Hello')insert t1 values(2,'')insert t1 values(3,NULL)select *from t1c1c21Hello23NULLselect *from t1where c2 = ' 'c1c22select *from t1where ltrim(rtrim(c2)) is nullc1c23NULLThe last query should have result as following. However sql server2000 does no list row c1 = 2.c1c223NULL
HiI am probably going to regret asking this because I'm sure you are going totell me my design is bad 8-) ah well we all have to learn....anywayI often use Nulls as a marker to see if certain tasks have been completed.A typical example would be a column say invoice_valuewhen new work is entered and a new record is appended, I leave theinvoice_value column as NULL, so if I want a View for uninvoiced work Icheck for the invoice_value being equal to NULL, and alternatively if I wantinvoiced work, I check for not null. I did it this way to save putting inanother column that needed to be set to TRUE or FALSE. (I do similar thingselsewhere aswell)so far everything seems to work OK but reading some old stuff on Google Iget the feeling that NULLS should be left alone, have I done wrong?many thanksAndy
Hi,This should be straight forward, but I've searched high and low on the net.I have a FORM which allows me to INSERT data, SQL Server 2005 backend. I populate the all the mandatory fields. But when I click on the Insert button, it won't let me save and says:Column 'PERSONAL_ID' does not allow nullsI'm using tableadapters, business logic layers etc. and pausing clearly shows that the values from the form are being passed to the procedure that I have created "AddNewRecord". And PERSONAL_ID is definitely not NULL! It fails on Line 971: Me.Rows.Add(row)PERSONAL_ID is a primary key which I generate. The pause also shows that for PERSONAL_ID it says " {"Conversion from type 'DBNull' to type 'String' is not valid."}Function AddNewRecord looks like this:<System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, True)> _ Public Function AddNewRecord(ByVal PERSONAL_ID As String, ByVal SURNAME As String, ByVal CHRISTIAN_NAME As String, ByVal SEX As String, _ ByVal FAMILY_POSITION As String, ByVal FAMILY_ID As String, ByVal ADDRESS_1 As String, ByVal ADDRESS_2 As String, _ ByVal ADDRESS_3 As String, ByVal ADDRESS_4 As String, ByVal ADDRESS_5 As String, ByVal ADDRESS_6 As String, ByVal POSTCODE As String, ByVal COUNTRY As String, ByVal ORG_ID As String) As Boolean ' create a new details row instance Dim details As New smDetails.smTbl_DetailsIDDataTable Dim detail As smDetails.smTbl_DetailsIDRow = details.NewsmTbl_DetailsIDRow details.AddsmTbl_DetailsIDRow(detail)...my formview code is this: <InsertItemTemplate> PERSONAL_ID: <asp:TextBox ID="PERSONAL_IDTextBox" runat="server" Text='<%# Bind("PERSONAL_ID") %>' AutoPostBack="True" /> <br /> SURNAME: <asp:TextBox ID="SURNAMETextBox" runat="server" Text='<%# Bind("SURNAME") %>' AutoPostBack="True" /> <br /> CHRISTIAN_NAME: <asp:TextBox ID="CHRISTIAN_NAMETextBox" runat="server" Text='<%# Bind("CHRISTIAN_NAME") %>' /> <br /> SEX: <asp:TextBox ID="SEXTextBox" runat="server" Text='<%# Bind("SEX") %>' /> <br /> FAMILY_POSITION: <asp:TextBox ID="FAMILY_POSITIONTextBox" runat="server" Text='<%# Bind("FAMILY_POSITION") %>' /> <br /> FAMILY_ID: <asp:TextBox ID="FAMILY_IDTextBox" runat="server" Text='<%# Bind("FAMILY_ID") %>' /> <br /> ADDRESS_1: <asp:TextBox ID="ADDRESS_1TextBox" runat="server" Text='<%# Bind("ADDRESS_1") %>' /> <br /> ADDRESS_2: <asp:TextBox ID="ADDRESS_2TextBox" runat="server" Text='<%# Bind("ADDRESS_2") %>' /> <br /> ADDRESS_3: <asp:TextBox ID="ADDRESS_3TextBox" runat="server" Text='<%# Bind("ADDRESS_3") %>' /> <br /> ADDRESS_4: <asp:TextBox ID="ADDRESS_4TextBox" runat="server" Text='<%# Bind("ADDRESS_4") %>' /> <br /> ADDRESS_5: <asp:TextBox ID="ADDRESS_5TextBox" runat="server" Text='<%# Bind("ADDRESS_5") %>' /> <br /> ADDRESS_6: <asp:TextBox ID="ADDRESS_6TextBox" runat="server" Text='<%# Bind("ADDRESS_6") %>' /> <br /> POSTCODE: <asp:TextBox ID="POSTCODETextBox" runat="server" Text='<%# Bind("POSTCODE") %>' /> <br /> COUNTRY: <asp:TextBox ID="COUNTRYTextBox" runat="server" Text='<%# Bind("COUNTRY") %>' /> <br /> ORG_ID: <asp:TextBox ID="ORG_IDTextBox" runat="server" Text='<%# Bind("ORG_ID") %>' /> <br /> <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert" /> <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" /> </InsertItemTemplate> Any pointers in the right direction would be appreciated. Thanks in advanceTushar
I have the following query: SELECT tblArticleCategory.ACategoryID, IsNull(ParentCategory, '') + IsNull( ' > ' + ACategoryName, '') AS Category from tblArticlecategory RIGHT OUTER JOIN (SELECT ACategoryName as ParentCategory, ACategoryID from tblArticleCategory WHERE AParentID is null AND aActive=1) AS Parent on parent.ACategoryID = tblArticleCategory.AParentID WHERE tblArticleCategory.AActive=1ORDER BY Category This produces an ordered list of the parent categories and subcategories from the table as long as there is a subcateogry directly below one of the parent categories. If there isn't one directly below the parent category, the parent category is dropped from the list. None of the parents with the NULLS back to back are in the list.
6 Arts & Entertainment > Dance2 Arts & Entertainment > Movies13 Computers > E-Learning4 Computers > Hardware14 Computers > Java16 Computers > Link Popularity17 Computers > Microsoft.net15 Computers > RSS5 Computers > Software8 Real Estate > Finance How do I fix the query so that Automotive, Business, Cancer and Communications are included in the list?
I have a table as part of a replicated database where i need to change the allow nulls value (set it to not allow nulls) but when i try to save the table i get this error.
'CONTACTS' table - Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.CONTACTS' because it is being used for replication.
will i have to stop replication to make the change or is it worse and i have to delete the publication and start all over again?
I have a table with 3 columns. Product, Location and Value. The data looks like this:
NULL NULL 100 Atlanta NULL 50 Atlanta Cookie1 30 Atlanta Cookie2 20 Dallas NULL 120 Dallas Cookie1 80 Dallas Cookie2 40
This table gets filled with a Groupby with Rollup option. The NULLS show subtotals/total. Is there a way to build a query that returns the results with NULLs at the bottom of each section like:
Atlanta Cookie1 30 Atlanta Cookie2 20 Atlanta NULL 50 Dallas Cookie1 80 Dallas Cookie2 40 Dallas NULL 120 NULL NULL 100
I've forgotten how to put a null value in a column of a table where a previous value existed before.
What I mean is if through EM, you look at the data (Open Table -> Return all Rows) and you see a value in a column that is nullable (for example, I want to change a column that has a 'ABC Company' to a NULL value).
How do you that - i've done it in the past ,but cannot remember
We have compare two table with populated with the same data and with the same definition except, one table allows nulls and the other table does not. All the fields are defined as integer. The table that allows nulls takes much more data space. Why is that? I could not find an explaination in BOL.
I have the following query in a stored procedure. If there are no rows in the history file, it returns a null. If there some setting or function that would have it return a zero if no rows are found? I use the variable to do arithmetic later on and a null messes everything up.
I'm using ADO.NET and have noticed that it is somewhat more difficult to send/receive nulls to/from the database. What are the benefits (if any exist) with storing a null value in the db as opposed to a default value?
In particular, do you save any space by storing a null opposed to a zero or 01/01/1900? Are the nulls faster for searching/comparing when querying the db? Basically I'm just wondering if storing nulls is really the best practice and why, so that I know whether it's worth the effort in .NET to transform my variables into a null-happy form.
Documentation states that you can set the Default Value for a column which is to be used whenever a row with a null value is inserted into the table. However, it appears that the column ends up as NULL regardless of this setting when inserted field has a value of NULL.
We have a stored procedure that accepts parameters for each column on the table and initializes the incoming parameter to NULL. It then inserts the row into the table. Table definition allows NULLs in the columns, but has a Default Value of ''.
I have a table with 3 columns. Product, Location and Value. The data looks like this:
NULL NULL 100 Atlanta NULL 50 Atlanta Cookie1 30 Atlanta Cookie2 20 Dallas NULL 120 Dallas Cookie1 80 Dallas Cookie2 40
This table gets filled with a Groupby with Rollup option. The NULLS show subtotals/total. Is there a way to build a query that returns the results with NULLs at the bottom of each section like:
Atlanta Cookie1 30 Atlanta Cookie2 20 Atlanta NULL 50 Dallas Cookie1 80 Dallas Cookie2 40 Dallas NULL 120 NULL NULL 100
I'm not sure why this is happening, but I'm trying to sort/order on this special field/column that I have created to allow precise ordering. However, for all of the values that are returned, if there are any NULLS, they are returned first/ahead of the values of what I want to be returned first. This field/column I created is called 'POW' (stands for Priority on Web). The values are just integers, 1-whatever, and we try to make sure that they're all unique. So for example, if we have a 1 in that field, then that row should be returned first, and if another row has 2, then it should be next. However, this is not working exactly - all of the NULL values are returned FIRST, then the proper values in the correct order. I want the NULLS to be last, because it may take some time for someone to go into each record, and assign it a unique value. My current SQL statement reads:
mySQL = "SELECT SignTypes.Id, SignTypes.SignType, SignTypes.SignDescription, SignTypes.FinalSignWidth, SignTypes.FinalSignHeight FROM SignTypes JOIN PropLocSigns ON SignTypes.Id = PropLocSigns.SignTypeID WHERE PropLocSigns.PropLocID = " & Session("PropID") & " AND (RestrictOnWeb = 0 OR RestrictOnWeb IS NULL) ORDER BY SignTypes.POW; SELECT @@ROWCOUNT"
So if someone knows of a way to put NULLS last, and still have my proper ordering, that would be great! I would think that there's some simple way of doing this, but not being an expert, I don't know. Thanks for the help!
I have several tables anywhere from 50,000 to 100,000 rows with about 100 columns in each that allow nulls. How bad will this affect performance (if any)? If it's significant, how can I replace nulls with zeros without doing it column by column? (100 columns will take too much time) thanks
I have two tbls joined via a varchar field.. which in tbl I have to use a CASE statement to format it correctly for the JOIN..
All works well.. however, if I add a WHERE clause to the statement, with the WHERE clause affecting only one of the JOINED tbls.. All the NULLS are not shown.
I should get results like:
Postcode Count
tf4 0 tf5 23 tf6 15 tf7 0 etc
Whereas, when I add the where clause, none of the "zeros" are shown, so I get:
tf5 23 tf6 15
Why is this?
Here's the SQL:
SELECT ISNULL(COUNT(CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', '')) WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3) ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END), 0) AS PCCount, dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country, dbo.UkPostcodes.Master_Account FROM dbo.UkPostcodes LEFT OUTER JOIN dbo.Customers_Trade ON dbo.UkPostcodes.Postcode = CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', '')) WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3) ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END WHERE (dbo.Customers_Trade.TradeCustomerID IN (SELECT DISTINCT TradeCustomerID FROM dbo.Orders_Trade WHERE (DATEDIFF(d, order_date, GETDATE()) < 60))) AND (dbo.Customers_Trade.is_activated <> 'No') OR (dbo.Customers_Trade.BillingAccountID <> '') AND (dbo.Customers_Trade.BillingAccountID IN (SELECT DISTINCT TradeCustomerID FROM dbo.Orders_Trade WHERE (DATEDIFF(d, order_date, GETDATE()) < 60) AND (is_activated <> 'No'))) GROUP BY dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country, dbo.Customers_Trade.Master_Account, dbo.UkPostcodes.Master_Account
does not show the nulls:
where as:
SELECT ISNULL(COUNT(CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', '')) WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3) ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END), 0) AS PCCount, dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country, dbo.UkPostcodes.Master_Account FROM dbo.UkPostcodes LEFT OUTER JOIN dbo.Customers_Trade ON dbo.UkPostcodes.Postcode = CASE LEN(REPLACE(dbo.Customers_Trade.PostCode, ' ', '')) WHEN 5 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 2) WHEN 6 THEN LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 3) ELSE LEFT(REPLACE(dbo.Customers_Trade.PostCode, ' ', ''), 4) END GROUP BY dbo.UkPostcodes.Postcode, dbo.UkPostcodes.Town, dbo.UkPostcodes.County, dbo.UkPostcodes.Area, dbo.UkPostcodes.Country, dbo.Customers_Trade.Master_Account, dbo.UkPostcodes.Master_Account
SELECT title_id,advance, CASE advance WHEN advance ISNULL THEN '0' -- i tried is null also and keep getting error ELSE advance END as 'How much to I advance' FROM titles
Gives me this error:
Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near 'ISNULL'.
WHen i use just null i get null in my result set where i want 0
I have been struggling to find a solution for nulls and lags , please help with a solution
i need to get a average of 3 months , for which i have used lag function , but apparently, the values in 1st two months is null for which its not giving me the correct results.
While i am using this code , i get the following results, which are not correct
AVG(
[Tbl Date Key].[Hierarchy].CurrentMember.lag(2):[Tbl Date Key].[Hierarchy].CurrentMember,
[Measures].[Q1_Avg])
i get the following result which is not acceptable
Level 04 Level 05
Region CE District CE1
Calendar Year Calendar Month Ke Date Q1_Avg try 2007 1 .00 0.00 2 .00 0.00 3 950.00 316.67 4 955.61 635.20 5 949.82 951.81 6 939.81 948.41 7 945.25 944.96 8 945.04 943.37 9 945.51 945.27 10 955.89 948.82 11 947.24 949.55 12 946.61 949.92 Total 947.47 947.47 Grand Total 947.47 947.47
The Actual Result should be
Calendar Year Calendar Month Ke Date Q1_Avg try 2007 1 .00 0.00 2 .00 0.00 3 950.00 950.00 4 955.61 952.81 5 949.82 951.81 6 939.81 948.41 7 945.25 944.96 8 945.04 943.37 9 945.51 945.27 10 955.89 948.81 11 947.24 949.55 12 946.61 949.91 Total 947.47 947.47 Grand Total 947.47 947.47
