Transact SQL :: IS NULL In A Where Clause
Nov 10, 2015
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.
View 8 Replies
ADVERTISEMENT
Nov 4, 2015
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
View 13 Replies
View Related
Oct 27, 2015
We have a case where in we should show date based on conditions for e.g if we had a column defined as
col varchar(10) then we would show col as 'NULL' for some condition and actual value when no condition
Normaly date values are stored here e.g under col 20150901 .
Case
when col>'20150901' then 'NULL'
else col
end as Derivedcol
Note this is an extract process and we are presenting data by pumping the data in a table .
Now there is another similar column -colz varchar(10) which stores date but doesnt have case condition so whenever date has no value its shows null which is database null.
So whats the difference between database null and string null ?
How can we show database null for the case condition instead of string "null"?
View 10 Replies
View Related
Dec 30, 2003
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
View 6 Replies
View Related
Jun 3, 2008
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
View 3 Replies
View Related
Jul 19, 2006
Hi all
My query has some inner joins to some tables. And problem is when any ON clause get null as value, the correspondent record is not displayed.
SELECTTableA.A, TableB.AFROM TableAINNER JOIN TableB ON TableA.A = TableB.A
What I did try:
SELECTTableA.A, TableB.AFROM TableAINNER JOIN TableB ON TableA.A = TableB.A OR TableA.A IS NULL
(but It generates redundant values from TableB)
I need to show all values even that value from Tablea is null
Thank a lot for any help
View 1 Replies
View Related
Apr 7, 2008
I have an optional param passed to my sproc @Pid if that is populated i want to have it part of where clause, if it is null then not in where
I know this is wrong, and very new to sqlsrver:
WHERE (Pos = 'T') AND (ofDate= @OfDate)
IF not @PID is null
AND (PID = @PID )
GROUP BY
bla bla bla
Thanks
View 12 Replies
View Related
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'
View 4 Replies
View Related
Jun 17, 2007
The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement:
"SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]"
Thanks for the help in advance!
View 1 Replies
View Related
Aug 11, 2015
Declare @date date = '2015-02-12'
Column in table datetime format
when i use where clause WHERE @Date > ValidateDateTime , got no rows but when i changed ValidateDateTime > @date, getting result as expected, this behavior because of data format or something else.
View 3 Replies
View Related
Jan 6, 2005
Hi
I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.
I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.
Does anyone know how I can elegantly get around this problem other than my best guess below:
Best guess pseudo code:
IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.
Here's some code that reconstructs the problem:
select datalength(char(13)) CarriageReturnVisible
, datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]
Cheers - Andy
View 4 Replies
View Related
May 28, 2015
I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below
UPDATE s
SET Exception_Ind = 1
OUTPUT s.Master_Id, s.TCK_NR
INTO #temp2
FROM Master_Summary s
INNER JOIN Exception d
ON d.Id = LEFT(s.Id, 8)
AND d.Barcode_Num = s.TCK_NR
WHERE s.Exception_Ind IS NULL
The above code is throwing an error as follows:
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.Master_Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.TCK_NR" could not be bound.
View 4 Replies
View Related
Jun 4, 2015
I have a table Customer with column name "SerNo" the value of SerNo column is like below.
Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3
Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3
Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3
Metz-2d9c957d-ca1c-4b27-adf8-39fef552f3f7
Metz-2d9c957d-ca1c-4b27-adf8-39fef552f3f7
Circle Graphics-a48712c1-2769-4964-ab89-4c1fb2949cf3
[code]...
I want to join it with nother table "Order" which has a SerNo column but does not have first part of SerNo.
SerNo.
9ad88929-f32d-459e-96c4-8d6c3d61e9d9
0a1d8b8f-7f5f-4a01-8e63-64579213afef
9342f8d7-dfdd-4535-8f01-5301bde669aa
[code]...
So basically i want to join these two tables and ignore the first part before "-" from SerNo column in customer table.
View 5 Replies
View Related
Nov 18, 2015
I have a small table named Exclude_Cusno_Item that has a varchar(29) field named
ITEM. When I query the table to show all of the records like this:
Select * from Exclude_Cusno_Item
The result set contains a record with 'AAA' in the ITEM field. However, where I write a query with a WHERE clause to select this specific 'AAA' record like this:
Select * from Exclude_Cusno_Item where item = 'AAA'
I get no results. What could be happening that would prevent the 'AAA' record from appearing in the result set of this query? Note that the 'AAA' entry has no trailing or leading blanks.
View 12 Replies
View Related
Aug 14, 2015
I'm struggling with the syntax for qualifying an openquery's results with a where clause. I copied this from examples on the web but get the error incorrect syntax near '32810'.
declare @sysid varchar(50) = '32810C534D01C920E7CB07EBC0A80122'
declare @sql varchar(500) =
'select * from OPENQUERY(WAREHOUSE,''select * from xxx.yyyy where sys_id = ''' + @sysid + ''''')'
exec(@sql)
selecting @sql it looks like
select * from OPENQUERY(WAREHOUSE,'select * from xxx.yyyy where sys_id = '32810C534D01C920E7CB07EBC0A80122'')
View 6 Replies
View Related
Nov 6, 2015
I have a scenario where the End User is going to select two parameters. @AgentID and @Location If the End User selects @AgentID parameter, in my WHERE clause, I do not want to check the location condition. By default, the agent knows their location already.
If the End User select @Location parameter, in my WHERE clause, I still want to check the Agent condition.
@AgentID parameter would populate either with "All" or their actual agent# (76754 for example).
@Location parameter would populate either with "All" or their actual location (Birmingham or Bessemer for example)Sample of my current WHERE clause:
WHERE
gl.ReceiptDate >= @BeginDate and gl.ReceiptDate < DATEADD(D,1,@EndDate)
AND gl.AgentId = CASE WHEN @AgentID = 'All' THEN gl.AgentID ELSE @AgentID END
AND gl.Location = @Location
View 9 Replies
View Related
Aug 13, 2015
I've got this set of registers (just an example) after ordering by the first 3 columns:
value_A value_B value_C ID date
1 2 3 YVIR 29/08/2015
1 2 3 ANTE 27/04/2015
1 2 3 REGO 20/02/2015
I need to get as a final result:
value_A value_B value_C ID date
1 2 3 REGO 29/08/2015
In other words, I need to get, after ordering the result by the date field, the most recent date but at the same time the oldest ID in the list.
I've been trying to do this with the group by clause:
select
value_A, value_B, value_C, min(ID), max(date) -- or max(ID)
from table
group by value_A, value_B, value_C
But in the field ID I'm getting the wrong result because this value is been associated with the alphabetic order.
In access this query involves the function LAST, but in SQL I have not found a good way to perform this. And I am asking because I have seen some possible solution but almost all of them involving the UNION operation, but my problem is, this table can have more than 350.000 registers.
This table is update by some one else, I just can access the information and use it as a source.
View 3 Replies
View Related
Nov 2, 2015
I am trying to write where clause after pivot but I could not know how?
DECLARE @PivotQuery AS NVARCHAR(MAX)
View 8 Replies
View Related
Jun 4, 2015
I have used the below update query. However, its updating only the first value. Like its updating AB with volume when c.Type = ABC, similarly for CD. Its not updating based on the 2nd or the next case condition.
Update XYZ Set AB = a.Amt * (CASE WHEN c.Type = 'ABC' THEN (c.volume)
WHEN c.TYPE = 'DEF' THEN (c.volume)
WHEN c.Type = 'GHI' THEN (c.volume)
Else 0
END),
CD = CASE WHEN c.Type = 'MARGIN' THEN '4105.31'
WHEN c.Type = 'ABC' THEN '123.1'
WHEN c.Type = 'DEF' THEN '234.2'
WHEN c.Type = 'GHI' THEN '567.1'
END
from table1 a join table2 b
on a.Cust = b.Customer
join table3 c
on b.account = c.account and b.channel =c.channel
Why its not working properly? But if i use Select statement instead of update query its working properly.
View 18 Replies
View Related
Jun 2, 2015
I have an existing stored table with duplicate rows that I want to delete.Using a cte gives me
WITH CTE AS
(
SELECT rn = ROW_NUMBER()
OVER(
PARTITION BY employeeid, dateofincident, typeid, description
ORDER BY Id ASC), *
FROM dbo.TableName
)
DELETE FROM cte
WHERE rn > 1
This is what I want to do basically. But this is only deleting in my CTE, is there anyway I can update my existing table "TableName" with this, without using temp tables?
View 4 Replies
View Related
May 7, 2015
I have to build a dynamic query to go over to Oracle and get some data and bring it back via a SQL Server Stored Procedure. How I can create an IN clause passing values and ","s from a SQL Server Temp Table that I have already built.
SET @SQLQuery = @SQLQuery + 'WHERE MEMBER.MEMBER_ID IN (''''' + SELECT MEMBER.MEMBER_ID + ',' FROM #TempTable + ''''' '
View 6 Replies
View Related
Jul 14, 2015
My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum PolicyStartdate
123G 01/01/2012
456D 02/16/2012
789A 01/21/2012
163J 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates
Ex: policynum ='456D' It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
I need t-sql code to get above results.
View 12 Replies
View Related
Jul 9, 2015
I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:
SELECT tblGRNItem.GRNID
FROM tblGRNItem
INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp
WHERE tblGRNItem.DateCreated BETWEEN
(SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT
[code]....
View 6 Replies
View Related
Jun 24, 2015
I'm asking my previous question in this new thread with more specific data and condition.I have below sample data,
DECLARE @DATA TABLE (U_ID INT, U_NM NVARCHAR(20), U_DT DATE, U_ORD INT, P_U_NM NVARCHAR(20) NULL)
INSERT INTO @DATA VALUES (1, 'Design', '06/15/2015', 2, NULL), (1, 'Plan', '07/01/2015', 1, NULL), (1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'), (1, 'Cust Plan 2', '06/05/2015', 0, 'Plan'),
(2, 'Design', '06/25/2015', 2, NULL), (2, 'Plan', '06/20/2015', 1, NULL)
We have 2 different U_ID (1, 2) and I want a SELECT query to display,
1. For U_ID = 1, we have 2 parent U_NM (Design & Plan) and Plan having 2 child (Cust Plan 1 & Cust Plan 2).
2. I want to display parent U_NM ORDER BY U_ORD
3. If any parent having child element, then need to show immediately under that parent and ORDER BY U_DT
4. For U_ID = 2, we don't have any child, hence display ORDER BY U_ORD
View 10 Replies
View Related
Jun 12, 2015
I am try to use a variable containing comma separated values in the IN clause.
View 10 Replies
View Related
Oct 29, 2015
I cannot seem to find the syntax to combine IN + CASE in a WHERE clause
WHERE
ses.BK_MS_SESSION <= '2015-03'
AND vis.CAT_DRAW_STATUS =
(CASE ses.BK_MS_SESSION
WHEN '2015-03' THEN vis.CAT_DRAW_STATUS
ELSE
CASE stat.BK_MS_VISIT_STATUS
WHEN 'T' THEN 'X'
ELSE vis.CAT_DRAW_STATUS
END
END
) IN ('D','R')
View 7 Replies
View Related
Oct 7, 2015
I have a table that has the records as shown below
Id From To
1 Delhi Noida
2 Faridabad Kanpur
3 Noida Delhi
4 Kanpur Faridabad
5 Banglore Mumbai
6 G H
7 I J
8 Mumbai Banglore
I want the results in the following order
5 Banglore Mumbai
8 Mumbai Banglore
1 Delhi Noida
3 Noida Delhi
2 Faridabad Kanpur
4 Kanpur Faridabad
6 G H
7 I J
Suppose as in above records There is a complete roundtrip from Banglore to Mumbai and Mumbai to Banglore.
View 11 Replies
View Related
Jul 17, 2015
I'd like to modify the dates within this where clause to be dynamic, building the date depending on the current year, but everything I try doesn't seem to be syntactically correct.
SELECT *
FROM Openquery(LS_CIS, 'select * from BI_WRKFLW_TASKS where (BI_EVENT_DT_TM>=''1/1/2011'' and (BI_NEEDED_DT_TM>=''1/1/2011''))OR (BI_EVENT_DT_TM>=''1/1/2011'' and BI_NEEDED_DT_TM is null)') AS derivedtbl_1
I'd like to replace ''1/1/2011'' in the where clause with something like:
CAST(CAST(YEAR (GETDATE())-4 AS varchar) + '-' + CAST(01 AS varchar) + '-' + CAST(01 AS varchar) AS DATETIME)
View 9 Replies
View Related
Sep 24, 2015
For Below example when @x=1 to retrieve col>0 rows or all rows.
With out another if else blocks or Dynamic sql to solve only in where clause.
select 0 col into #x
union
select 1 col
union
select 2 col
declare @x INT =1
SELECT * FROM #x
where col>CASE WHEN @x=1 THEN 0 ELSE (col=col) END
--here in case i want to compare only when @x=1 then col>0 other wise select all rows with out filter
View 5 Replies
View Related
Nov 16, 2015
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store Type Sales
11 A 1000
23 A 1980
23 B 50
5 B 560
I want to filter the store in "where clause" where
1)- if the store has type A and B, then assign only A
2)- if the store has type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type
from table1
where (TYPE]= (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
View 7 Replies
View Related
Jul 14, 2015
Suppose we have the following table in our database;
CREATE TABLE [dbo].[PERMISSION](
[ID] [int] IDENTITY(1,1) NOT NULL,
[USERID] [int] NOT NULL,
[STARTTIME] [smalldatetime] NOT NULL,
[ENDTIME] [smalldatetime] NOT NULL,
[REASON] [nvarchar](250) NULL,
[PERMISSIONTYPEID] [int] NOT NULL,
[code]....
This code works pretty well. But I don't want to do this with "select" since there is OUTPUT clause in T-SQL. So the CommandText property will be changed into this;
command.CommandText = @"insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @outID, @outCREATETIME
values(2, getdate(), getdate(), 'sdfg', 1, DEFAULT);";
well, not only this statement gives an error while executing; but also, no such usage defined in the
documentation of OUTPUT Clause. Actually the documentation tell us to use Temporary Tables for that. So I have to change CommandText into this;
command.CommandText = @"DECLARE @MyTableVar table(ID int, CREATETIME smalldatetime);
insert PERMISSION
output INSERTED.ID, INSERTED.CREATETIME into @MyTableVar
code]....
No temporary tables required; thus, no "type spesific" things required. This way is easier to create dynamic queries though. Only,the RETURNING INTO clause.So, I was wondering; why MS-SQL (T-SQL) forces users to "declare a temporary table (type specific)" and "execute select on the temporary table in order to assign values to output parameters" while using "the OUTPUT Clause". Comparing to the Oracle's sample I'm just using "the RETURNING INTO Clause in order to assign values to output parameters" and that's it; very easy and dynamic. To Summarize, Oracle's RETURNING INTO Clause is better than MS-SQL's OUTPUT Clause, but in fact they're doing the same job.
View 7 Replies
View Related
Oct 31, 2015
I am using sql server 2012. suppose i have a table called cte which contains id and name columns . in name column there are null value . i want to display top row value instead of null value as like attached image. Here is query :
;with CTE As (
Select 1 as Id , 'Advance' as Name
union all
Select 2 as Id , NULL as Name
union all
Select 3 as Id , NULL as Name
[Code] ...
Expected Result :
I want to write normal select Query. i am not interest to using loop or cursor.
View 5 Replies
View Related
Sep 16, 2015
In SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).
View 4 Replies
View Related
