WHERE Clause On Nullable Field Not Return Null Records?
Nov 10, 2014
I recently ran into an issue with an issue with a query against our Data Warehouse. When attempting to sum revenue from a table, and using a WHERE clause on a field that contains NULL values, the records with the NULL values are suppressed (in addition to whatever the WHERE clause specified). I believe this is because a NULL value is unknown so SQL doesn't know if it does or doesn't fit the criteria of there WHERE clause so it is suppressed.
That being said, is there a way to avoid this instead of having to add an ISNULL function in the WHERE clause which is going to kill performance?
Code:
create table #nullTest (
name varchar(50)
,revenue int)
INSERT INTO #nullTest
Values ('Tim',100)
,('Andrew', 50)
,(null, 200)
SELECT sum(revenue) as Revenue FROM #nulltest WHERE name <> 'tim'
Ideally, I would want the SELECT statement above to return 250, not 50. The only way I can think to accomplish this is with this query:
Code:
SELECT sum(revenue) as Revenue FROM #nullTest WHERE isnull(name,'') <> 'tim'
Say I want to return only records with dates that fall within the next 6 months. Is there some straight-forward, simple way of doing so?As of now, I'm explicitly giving it a date 6 months in the future, but I'd like to replace it with some sort of function. SELECT DateField1WHERE (DateField1 < CONVERT(DATETIME, '2008-06-03 00:00:00', 102)) Any help is greatly appreciated... btw I'm using SQL 2005.
Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.
SELECT table1.col1 ,��n/a�� _response FROM table1 INNER JOIN table2
This is the query that get the report data for my report. Now I need to replace the second column with an actual response like ��accepted�� and ��rejected��. And these values should be a result of evaluation of this form
If(Statusarray.count < 1) Set _response = ��accepted�� Else Get Statusarray[1] Compare this to Statusarray[0] Set _response = some result based on comparision.
The _response returned in the query should return the actual response based on this evaluation where ResponseStoredProcedure is sent the current row value for table1.col1.
How can this be done in sql(using SQL Server 2005)
Hi, All records of a table must be deleted if a field (field1) contains NULL or is made empty by a previous update command. I did this but it only works when the field is made empty, not when it contains NULL: dim field1 as string = ""...sql = "delete from mytable WHERE (field1=@field1)" comd = New SqlCommand(sql, oConnection) comd.Parameters.AddWithValue("@field1", field1) comd.ExecuteNonQuery() How can i do that? Thanks Tartuffe
I have a case where a table has two candidate primary keys,but either (but not both) may be NULL. I don't want to storea copy of the concatenated ISNULL'ed fields as an additionalcolumn, though that would work if necessary. Instead, I triedthe following (this is a related simplified example, not myreal one):CREATE FUNCTION ApplyActionPK(@IP int = NULL,@DNS varchar(64) = NULL)RETURNS varchar(74) -- NOT NULLASBEGINdeclare @val varchar(74)set @val = str(ISNULL(@IP, 0), 10)set @val = @val + ISNULL(@DNS, '')return @val-- Also tried "return str(ISNULL(@IP, 0), 10)+ISNULL(@DNS, '')"-- Also tried "return ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"-- ... and other things...ENDGOcreate table ApplyAction(-- An action applies to a computerAct varchar(16) NOT NULL,-- The action to applyIP int NULL,-- The computer IP address, orDNS varchar(64) NULL,-- The DNS name of the computerTarget as dbo.ApplyActionPK(ComputerID, DNS), -- PK value-- Also tried "Target as ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target))SQL Server always complains that the primary key constraint cannot becreated over a nullable field - even though in no case will the 'Target'field be NULL.Please don't explain that I should store an IP address as a string.Though that would suffice for this example, it doesn't solve myactual problem (where there are four nullable fields, two of whichare FKs into other tables).What's the reason for SQL Server deciding that the value is NULLable?What's the usual way of handling such alternate PKs?Clifford Heath.
I have a dimension with a date field (int key value actually) that is generally null that I'm using to link to a time dimension. Everything works great when I set Null Processing to UnknownMember in the Measure Group Bindings, but if I try to set it to Preserve or anything else, I get processing errors because it can't match the null attribute key to the time dimension. I think I understand this, but I'm concerned that I'm not doing it the best way. Also, why does this page say "UnknownMember causes a data integrity error at processing time" when that's the only setting that does not give me errors?
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change DECLARE@size varchar(5)--The new size of the column DECLARE @TSQL varchar(255)--Contains the code to be executed DECLARE @Object varchar(50)--Holds the name of the table DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10) ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1 Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)
I have used the old goto method for trapping errors committing or rolling back the transaction.
Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?
I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).
Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?
So how to return timestamp and a return value and how to check for concurrency all in the try/catch.
by the way I read that you could not return an identity in the output clause but I had no problem.
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible...
The problem is thus: I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter.
The Solution I'm attempting: My idea is best represented in psuedo-code: - Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash". - The function would look something like: CREATE FUNCTION CashAccountIDs()
RETURNS TABLE
AS
BEGIN DECLARE TABLE @t1 (account INT) INSERT INTO @t1 VALUES (100) INSERT INTO @t1 VALUES (101) INSERT INTO @t1 VALUES (102) INSERT INTO @t1 VALUES (407) RETURN @t1 END
Then I could call this function by doing something such as:
SELECT * FROM Accounts WHERE AccountId IN (dbo.CashAccountIds())
I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts.
Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type"
(The code I use to generate this error is: CREATE FUNCTION fn_cashaccountids ()
RETURNS @t1 TABLE (i INT)
AS
BEGIN INSERT INTO @t1 VALUES (100) RETURN END
Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before.
It's a very strange thing! I havea a table called invoices, and a table calle customer payments which has the invoiceID of the payment.
I have many invoices that haven't been paid (so they don't have a record on the customer payments). I know this, as i can for example do: select * from invoices where invoiceID = 302247 (and i'll get one result) select * from customer_payments where invoice = 302247 (and i'll get none results)
however, if i do the following: select * from invoices where invoice_id not in (select invoice_id from customer_payments) I get nothing!!!???
It doesn't make any sense, as I should get at least 300 (including the 302247) - both invoiceids fields are int... so i just don't understand what's wrong?
I have a form that has many checkboxes (more than 40) that provide information about companies.
For example. Company ABC Checkbox 1 (Windows XP) Checkbox 2 (Windows Vista) Checkbox 3 (Windows NT4) etc.
I save these in a SQL table like the following Row 1 - Col 1 (CompanyID), col2 (checkbox value) Row 2 - Col 1 (CompanyID), col2 (checkbox value) etc.. so a comapany can have multiple checkbox selected..
I am currently developing a report where the end user can select one or many of the checkbox to see if the company exist.
This is where I get stuck - I only want to return the results of the companies that meet the report selections. So if I want to see all the companies that current have Windows NT and Windows Vista...how would I build that querry based on the data model above.
I have tried using the IN clause - however that use the OR connector and doesnt show ONLY the compaines that meet. I have tried using a UNION and INTERSECT, and running the select statement many times.. (SELECT CompanyID from xx WHERE CheckboxVal = xx) INTERSECT (SELECT CompanyID from xx WHERE CheckboxVal = xx) etc.. howevert this creates a very large quesry that is unable to be handled by SQL 2005. I get a error message asking to minimize the query.
Any suggestions on this please...this is my 4th day working on this..
When building stored procs, I typically have several nullable parameters. Then in the where clause I will either do this
WHERE (@MyParam IS NULL OR MyColumn = @MyParam) or this: WHERE MyColumn = COALESCE(@MyParam, MyColumn) Lately, I've noticed that either of these imposes a performance penalty. In other words, if I change the first one to WHERE MyColumn = @MyParam
without the IS NULL, it is noticeably faster. If I only have one or two nullable parameters, I can separate the logic using IF/ELSE, but it becomes huge if I have a bunch of nullable params.I've looked at the execution plans.
i have query which does the following select x from y where t = "House"x is an integer. If no record is found, how do i get it to return 0 rather than null?
Hi I have an sql statement that when i put through the query builder and specify NULL it returns all the rows. if i put 0 it returns nothing.how can i change the "IS Null" part of the statement to work with 0. so when i specify 0 for all values it returns all the rows.so i want 0 to work like NULL does 1 SELECT LocationID, TypeID, Title, Description, PropertyID, Price, 2 (SELECT Location 3 FROM Location_Table 4 WHERE (Property_Table.LocationID = LocationID)) AS Location, 5 (SELECT TypeOfProperty 6 FROM Type_Table 7 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty 8 FROM Property_Table 9 WHERE (@MinPrice IS NULL) AND (@MaxPrice IS NULL) AND (@TypeID IS NULL OR 10 TypeID = @TypeID) AND (@LocationID IS NULL OR 11 LocationID = @LocationID) OR 12 (@MinPrice IS NULL) AND (@TypeID IS NULL OR 13 TypeID = @TypeID) AND (@LocationID IS NULL OR 14 LocationID = @LocationID) AND (Price <= @MaxPrice) OR 15 (@MaxPrice IS NULL) AND (@TypeID IS NULL OR 16 TypeID = @TypeID) AND (@LocationID IS NULL OR
I am working on a web application for a company that uses an existing company database. I just found out before the long weekend that a couple hundred of the items in the table aren't completely related.
I am mostly using 2 tables for the project: ItemVersion, ItemVersionRev
ItemVersion has fields: ItemVersion, ItemNumber, RecordID ItemVersionRev has fields: RecordID, RevNumber
All of the items in the database have ItemNumbers (obviously) and most have revision numbers (RevNumber). One of the functions that I created for my application finds the MaxOfRevNumber. I ran a query on the database and saw that dreaded error screen that read "There is no row at position 0"
The query that I have now simply returns MaxOfRevNumber. Is there a way to program a query to check and see if an ItemNumber has a Revision and depending on the result, return a certain value. For example, if there is not a corresponding RevNumber, then return 0 as the RevNumber?
Hello all I am trying to run a sql statement, (without having to run stored procedures), that will lookup a value stored in a Record.field., otherwise ignore the value if it is blank. I have a field tied to a Microsoft Access form - ItemLocation. If the user selects a unique ItemLocation the report will attempt to locate all values within that ItemLocation. If the user decides to leave the field blank, I would like for it to return all values for every ItemLocation. If anyone can help, I would really appreciate it. I will attach code. My problem is the last line of this SQL statement. If F.ITemLocation is null, I get no data. I would like for it to return everything. Thank you.
Code: SELECT DISTINCT B.BarCodeID, A.ItemDescription, A.ItemCategory, A.TypeOfItem, A.SerialNumber, B.ItemLocation, B.LocationID, B.LastUpdate, B.TrackItID, B.UserID FROM tblMISFixedAssetTable A, tblMISFixedAssetTable2 B WHERE A.BarCodeID = B.BarCodeID AND A.DisposalDate is null AND B.LastUpdate = (Select Max(C.LastUpdate) from tblMISFixedAssetTable2 C Where B.BarCodeID = C.BarCodeID) AND B.BarCodeID in (Select D.BarCodeID from tblMISFixedAssetTable3 D Where D.FiscalYear = (Select E.FiscalYear from tblReportFY E)) AND B.ItemLocation = (Select F.ItemLocation from tblReportFY F)
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.
I am working on a Function that takes multiple parameters. I have a query that populates a temporary table, and then it processes some logic. My question is, if the parameter is passed as null, I dont want the query to be affected by this null value. Rather, I would like to not pass it at all to the query. So if the parameter is NULL, dont pass it through the query. I have the following but its not compiling right:
SELECT bom.pEngr_BOM_ID , bom.fEngr_Item_ID, det.pEngr_BOM_Detail_ID, 1, bom.Bill_Type, bom.Rev_Ltr, bom.Series_Ltr FROM dbo.Engr_BOM_Control bom WITH (nolock) INNER JOIN dbo.Engr_BOM_Detail det WITH (nolock) ON det.fEngr_BOM_ID=bom.pEngr_BOM_ID WHERE bom.pEngr_BOM_ID=@v_pEngr_BOM_ID AND det.fEngr_BOM_ID=@v_pEngr_BOM_ID CASE WHEN @v_Bill_Type IS NOT NULL THEN AND bom.Bill_Type=@v_Bill_Type END
hi i am trying to get the output of the select statements of sqldatasource : protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { DataView dv; dv = (DataView)(this.SqlDataSourcePictures.Select(DataSourceSelectArguments.Empty)); } } the problem is that dv returns null ? and here is the sqldatasource definition in .aspx page <asp:SqlDataSource ID="SqlDataSourcePictures" runat="server" ConnectionString="<%$ ConnectionString:con1%>" SelectCommand="SELECT URL FROM SchoolPictures WHERE (School_Code = @School_Code) AND (SchoolPictureCategory = @SchoolPictureCategory)" OnSelecting="SqlDataSourcePictures_Selecting" OnSelected="SqlDataSourcePictures_Selected"> <SelectParameters> <asp:QueryStringParameter Name="School_Code" QueryStringField="bid" Type="Int16" /> <asp:ControlParameter ControlID="ddlCat" Name="SchoolPictureCategory" PropertyName="SelectedValue" Type="Int16" /> </SelectParameters> </asp:SqlDataSource>
Hi, I basically do not want to return a null value as a result of using a sum function (using sum against 0 rows). Is there a common way to avoid this? Thanx
I have this program that I want to invoke from SQLServer funny thing is I can invoke this from command line but could not get it to work via 'xp_cmdshell' on this particular server. When I invoke run the following from SQLServer Query Analyser I always get 'NULL' returned. I tried just running EXEC master..xp_cmdshell 'dir ' and it worked, so I am certain it is not a SQL rights problem.
FYI, I executed the same code on another machine and it worked. I just cannot figure out why it is not working on this one server? Can anyone help? thanks
I have this table 'Request' in which there's 2 fields 'RequestName' and 'DateSubmitted'. The datatype for 'DateSubmitted' is datetimn.
I have to write a query to extract all the 'RequestName' that falls under each month based on the 'DateSubmitted'.
For example, the table 'Request' has 12 rows, each row ('DateSubmitted') with the month of the year. Like row1 = January, row2 = February etc.
For each month, I have to extract all the 'RequestName' for that month based on 'DateSubmitted' field. My query is like this:
select RequestName, datename(month, DateSubmitted)as month from Request where datename(month, DateSubmitted) = 'March'.
By right it should return just one row right?
Instead, it returned me all 12 rows, only one row with value 'March' and the other 11 rows with value 'NULL'. Even if the other 11 rows have value of other months, when i run the above query, it will return me null.
I am trying to return all the names of employees and their managers
this query returns all the employees except for 1
SELECT E.FNAME,E.LNAME,M.FNAME,M.LNAME FROM EMPLOYEE E,EMPLOYEE M WHERE E.SUPERSSN=M.SSN
the one that isn't returned has a null SUPERSSN, but when I add in:
OR E.SUPERSSN IS NULL
it returns a row with the name of the employee whose SUPERSSN is null 8 times (where each time the M.FNAME,M.LNAME are other employee names)
How do I ammend the first query to return each employee and their respective manager once, the employee without a manager having null values for the manager name columns?
johnny writes "I am trying to return only the columns from multiple tables that are NOT NULL for a specific ID. Once I have the tables JOINED is there a way to only get those columns that are populated. Thanks."
SQL Server 2000 SP3.Is it possible for the @@ROWCOUNT function to return NULL after astatement? I am troubleshooting a relatively large stored procedure withmultiple SELECT statements and a couple of INSERTs into table variables.Immediately after each statement I save the value returned by @@ROWCOUNT toa local variable. That information eventually is passed back to the clientvia one output parameter, for all statements in the procedure.Occasionally, the value returned via that parameter is NULL. This cannot bereproduced by re-running the SP with the same input parameters.Before doing any further troubleshooting, I would like to rule out thepossibility that @@ROWCOUNT can actually return a NULL under somecircumstances. From searching the archives, it appears that in SQL Server7.0 this could happen in the context of a DML query on a table withtriggers. This is not the case here - the only DML queries are INSERTs intotable variables, all other queries in the SP are SELECTs.Any related information would be appreciated.--remove a 9 to reply by email
