SQL Server 2014 :: Finding NULL In CASE Statement
Sep 15, 2015
The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE
WHEN msdb..backupset.type = 'D' THEN 'Database'
[Code] .....
View 9 Replies
ADVERTISEMENT
Oct 22, 2013
Code below:
SELECT
NAME,
CASE WHEN PHONE IS NULL THEN '-' ELSE PHONE END AS PHONE
FROM
PATIENT
WHERE PHONE = '-'
If I am switching the NULL values to hyphens (-), why am I not getting any records when I look for hyphens in the WHERE clause? The script is not as simple as this or else I would just check for NULL values. I really need to switch NULL values to something I can pass on to a variable and query on it from Reporting Services.
View 6 Replies
View Related
Jul 21, 2004
Can someone help me with this :
Case WHEN ISNULL (dbo.BLH.n1name,'NOTHING')= 'NOTHING' then dbo.BLH.coname else dbo.BLH.n1name End as CustName
as the strored proc is not returning value when the fileds is blank
cheers
View 2 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 2, 2014
I am looking for a table where Maintenance Clean Up Task configuration is stored. For example, Delete file older than the following - which is 2 days. Which table can I retrieve the setting in msdb ?
View 5 Replies
View Related
Aug 13, 2014
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
What is the best way to script this
View 1 Replies
View Related
Apr 22, 2014
I have a table exam_setup
CREATE TABLE exam_setup
(
setup_id INT,
sub_id INT,
assignment decimal(4,1),
[Code] ....
I have to fetch the values like
assignment = 25
attendance = 15.5
INT_1 =0
INT_2 =0
if decimal point is 0 then have to return integer value only.. but the below query doesn't work, why?
[Code] ....
View 3 Replies
View Related
May 27, 2014
I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.
Because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE().
SELECT
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END
FROM TableName A
View 9 Replies
View Related
Jun 10, 2014
I've a variable in my SP that I'd like to use to change the format of my output. Try this!
select case when 1=2 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Then this:
select case when 1=1 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Why in either case, the output is returned as n.nn ? Whilst single select expressions (like these):
select cast(1 as decimal(10,2)) -- = 1.00
select cast(2 as decimal(10,0)) -- = 2
Work fine.
View 8 Replies
View Related
Sep 21, 2015
I'm trying to get a result set without the NULLs. Here is the code I'm using. I'd like the results to look like:
17285204 90471 090471
17285204 90715 090715
17285204 99396 099396
17285204 99420 099420
17285204 90471 090471
17285204 NULL G0444
create table #Test
(
AppNum varchar(10),
CPT varchar(10),
src char(1)
)
insert into #Test(AppNum, CPT, src) values('17285204','090471','b')
[Code] ...
View 5 Replies
View Related
Oct 29, 2015
I have always (or at least intended to) treat NULL and empty strings separately in my SQL querying history. Now I have run across something that mystifies me (but probably shouldn't) that I would like an explanation for.
Consider this bit o' code:
DECLARE @ORDER CHAR(10)
SET @ORDER=
(
SELECT NULL
)
IF @ORDER <> ''
PRINT 'Not an empty string'
IF @ORDER IS NULL
PRINT 'It is NULL'
Run this and you will get:
It is NULL
I was expecting:
Not an empty string
It is NULL
Why is NULL not passing the 'not an empty string' test? In other words, how does NULL = '' ? Is NULL cast to an empty string for this comparison?
View 9 Replies
View Related
May 18, 2015
Over the weekend I decided to give it the ability to do a case sensitive character swap. Updating the code was pretty straight forward but when I was through, I noticed that I was getting Cardinality Estimate warnings that I wasn't getting before.
Anyway, here is some test data and two versions of the executed SQL (the base code is all dynamic and the two code versions are the result of toggling the @MatchCase parameter).
/* ========================================
CREATE TABLE
======================================== */
CREATE TABLE [dbo].[PersonInfoSmall](
[PersonID] [BIGINT] NOT NULL,
[FirstName] [NVARCHAR](50) NOT NULL,
[MiddleName] [NVARCHAR](50) NULL,
[LastName] [NVARCHAR](50) NOT NULL,
[code]....
View 8 Replies
View Related
May 4, 2015
What will be the best way to write this code? I have the following code:
BEGIN
declare
status_sent as nvarchar(30)
SET NOCOUNT ON;
case status_sent =( select * from TableSignal
[Code] ...
then if Signal Sent.... do the following query:
Select * from package.....
if signal not sent don't do anything
How to evaluate the first query with the 2 options and based on that options write the next query.
View 2 Replies
View Related
Feb 18, 2014
I am trying to build a select statement that will
1). find records where a duplicate field exists.
2.) show the ItemNo of records where the Historical data is ' '.
I have the query which shows me the duplicates but I'm stuck when trying to only show records where the Historical field = ' '.
Originally I thought I could accomplish this goal with a sub query based off of the original duplicate result set but SQL server returns an error.
Below you will find a temp table to create and try in your environment.
create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int
[Code] ....
Ultimately I need a result set that returns 'ATMR10 MFZ N', and 'QBGFEP2050 CH DIS'.
View 3 Replies
View Related
Nov 6, 2014
I have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.
For instance, if I put the following string into a FT indexed table
'J9355 - Trastuzumab (Herceptin)'
AND
'J9355 - Trastuzumab emtansine'
The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'
Nor in
'J8999 - Everolimus (Afinitor)'
It did not recognize 'Afinitor' as a keyword.
In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.
why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?
View 0 Replies
View Related
Oct 29, 2013
I have a excel sheet with some data and blank columns. I have a ssis package using to import data from excel to sql table. For blank excel columns it is importing as null instead i want to show them as '0'. If data comes in it should update the data.
View 8 Replies
View Related
Sep 30, 2015
I have a table :
Person(Id bigint , FirstName nvarchar(50) , LastName nvarchar(50),Address nvarchar(100))
and I have a sp for search data in "Person" table:
CREATE PROCEDURE SelectPerson
@First nvarchar(50) = NULL,
@Last nvarchar(50) = NULL,
@Address nvarchar(100) = NULL
[Code] ....
And there exists 500 record in Person.
I execute SelectPerson by this code:
"
DECLARE@return_value int
EXEC@return_value = [dbo].[SelectPerson ]
@First = 'ام',
@Last = NULL,
@Address= NULL
SELECT'Return Value' = @return_value
GO
"
but result don't has any rows.why?
View 9 Replies
View Related
Jun 17, 2003
Hi,
My database is case insensitive. However, the application is case sensitive for the data in the table.
I need to find out the data from the table where the data is stored in lower case.
Here is the sample data:
Table: inv_manager
column: sku varchar(5)
Data: 1134X
1135x
1123a
b145Y
I just need query to return row 2, 3, 4.
Here is my query:
select sku from inv_manager where sku like lower('%[a-z]%')
The problem is it returns all the rows from the table including first one.
How can I return just the data with lower case ?
Thanks
View 2 Replies
View Related
Nov 5, 2007
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
Thanks,
Jason
View 1 Replies
View Related
Aug 5, 2014
My table structure like below.
id date value1 value2
1 5-june-2104 Yes No
1 6-june-2014 No Yes
2 5-june-2104 Yes Yes
Want to calculate yes count on any day for same id
View 5 Replies
View Related
Jun 11, 2014
I want to pivot a table something like this .I pivoted it successfully but the results are not correct.
Here is the example :
install-Name Fiscal year Question Answer
Washington 2010 what is the reason for install? tttttt ggg yttt o
washington 2010 reason id 12345
washington 2010 install start date 10/10/2010
washington 2010 install end date 10/12/2010
washington 2010 install status successfull
[code]....
I want the above data to get pivoted like this
Install-name | Fiscal year |what is the reason for install? | reason id | install start date | install end date |
install status |Do you feel the install is incomplete? | Is the expiration of 90 days exceeded? |
washington | 2010 | tttttt ggg yttt o | 12345 | 10/10/2010 | 10/12/2010 |
successful | | |
washington | 2010 | trtbnbthwgt hrgthjrt | - | 12/3/2010 | 12/8/2010 |
| | |
washington | 2011 | sbjeh dhebwdh dbjw | 345 | 10/10/2010 | 10/12/2010 |
successful | No | Yes ,but b b b b |
View 2 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
Apr 10, 2007
I want to write a query to bring out any records in a table
where any of its fields contain a null value. I could do it in this way:
select *
from
customers
where name is
null
or role
is null
or email
is null
u003c/p>u003cp styleu003d"margin:0cm 0cm 0pt">u003cfont faceu003d"Times New Roman" sizeu003d"3">u003cfont coloru003d"#000000">However I want to perform a similar function for a number of tables and the method above would mean more sql and maintenance.u003cspan>Â u003c/span>I was hoping there was a simple way to do the same thingu003cspan> ie find any rows with a blank fieldu003c/span>?u003cspan>u003cfont faceu003d"Arial" sizeu003d"2">Â u003c/font>u003c/span>u003c/font>u003c/font>u003c/p>u003c/span>u003c/font>u003c/div>",0]
);
D(["ce"]);
//-->
However I want to perform a similar function for a
number of tables and the method above would mean more sql and maintenance. I was hoping there was a simple way to
do the same thing ie find any rows with a blank
field?
View 4 Replies
View Related
Jan 9, 2007
If I have a query such as
SELECT * FROM TABLE WHERE PARENT = NULL
where a run a query like this one it returns zero results. I am not sure why. How can I look in a column for empty or null results?
View 10 Replies
View Related
Jul 20, 2015
I have data that I want at multiple granularities, 5,15,30 and 60 minutes. To reduce repetition, I have put them all in the same table, so that there is a column for 5,15,30 and 60 minutes, with a filtered index on each of the columns that removes the nulls. This means that each day will have 288 slots, but only 24 of the slots are filled in for 60 min data, and all of them are filled for 5 minute data.
I have another column that specifies the interval granularity, and my first thought was to access my data through a join, where I can use a CASE statement, and depending on the data granularity necessary, it will look at a different column:
INNER JOIN Data d ON
AND d.settlement_key =
CASE st.interval_granularity
WHEN 5 THEN [5_min_settlement_key]
WHEN 15 THEN [15_min_settlement_key]
WHEN 60 THEN [60_min_settlement_key]
ELSE NULL END
Despite the presence of the indexes on the columns, then the process seems to be quite slow, I think probably due to the fact that any query plan isn't going to know beforehand which of the columns it is going to use for any given dataset, until it actually starts to run, so it may not be optimised.
How I could optimise this based on the given structure? Maybe there are hints to be added to the join, or maybe I can clear the query plan each time the SQL is run? My other option for dealing with the data of different granularity was to use one column and repeat the data multiple times, each at the different granularity, but this makes my data, row and table sizes much higher, as we are adding just a column for each additional granularity. Would this work any better in future versions of SQL server, maybe with column store indexes?
View 5 Replies
View Related
Sep 9, 2014
I have TSQL query using UNION ALL. First subset returns 92 rows, second subset return 0 (ZERO) rows. Now if I execute them separately they execute subsecond. If I put UNION ALL between them then it hangs forever exactly on 88s row.
I understand I need to provide execution plans, actual queries etc but before going into all this details, why on earth even in theory UNION ALL with empty result will result in a hang of the first subset?
Based on estimated execution plan 2 subqueries are performed in parallel and then CONCATENTION is being used which shall result with just first subset immediately displayed in result.
Estimated execution plan attached, I can not get actual execution plan because query never completes.
View 9 Replies
View Related
Sep 18, 2015
I have two tables for insertion in one transaction scope. Table one have 10 rows. After first table insert statement (not yet committed) if I run select on first table from other session, it holds table until my insert is committed or rolled back and from (SSMS), it display 10 rows and then wait for transaction scope till finished. My question is do I need to use no lock hint in this situation. Or there is something wrong with isolation level. One saying that in this situation table should not hols select while insert is in transaction scope.
View 5 Replies
View Related
Feb 4, 2014
Below is the scenario which I have currently in my query. I need to write this query without any hardcode values , so that it will work til n number of years without modifications.
Startdate =
CASE WHEN
Trandate between '06-04-2013' and '05-04-2014' then '06-04-2013'
Trandate between '06-04-2012' and '05-04-2013' then '06-04-2012'
Trandate between '06-04-2011' and '05-04-2012' then '06-04-2011'
Trandate between '06-04-2010' and '05-04-2011' then '06-04-2010'
Trandate between '06-04-2009' and '05-04-2010' then '06-04-2009'
Trandate between '06-04-2008' and '05-04-2019' then '06-04-2008'
END
View 5 Replies
View Related
Aug 20, 2014
Is this possible, I am trying to execute a stored procedure depending on what parameter is selected, something like this????
Case
when field = 'value' then execute sp_procedure else execute sp_procedure_2 end
case
View 1 Replies
View Related
Aug 21, 2014
How I am using a CASE statement within a WHERE clause to filter data:
CREATE PROCEDURE dbo.GetSomeStuff
@filter1 varchar(100) = '',
@filter2 varchar(100) = ''
AS
BEGIN
SELECT
[Code] .
What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.
Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?
View 5 Replies
View Related
Oct 6, 2014
I have a bit of trouble getting values into one alias field, my code is below. I am trying to get values into the alias extension, Agent_ID is sometimes null, and so is agent_id2, however sometimes they both have values in them, and then only one of the values is every returned. When in the example below only Agent_ID (11111) is ever returned by I want both of them returned.
Agent_ID Agent_ID2
11111 22222
<code>
SELECT DISTINCT
CASE WHEN [AGENT_ID] is not null then AGENT_ID
when agent_id2 is not null then agent_id2 end as extension
FROM [AA_Helper].[dbo].[tblEmpData]
</code>
View 9 Replies
View Related
Oct 14, 2014
1. I have a simple JOIN statement between A and B, e.g. Cities A JOIN Countries B:
SELECT A.City_Name, B.Country_Code, B.Country_Area
FROM Cities A
JOIN Countries B
ON B.Country_Id = A.Country_Id
WHERE B.Country_Type='ABC';
That statement works absolutely fine, very fast (less than a second) and returns me 2 records
2. I need to replace Country Area column with 1 for Europe and 0 for all the rest. I implement so in the following way:
SELECT A.City_Name, B.Country_Code, CASE B.Country_Area WHEN 'EUR' THEN 1 ELSE 0 AS Country_Area
FROM Cities A
JOIN Countries B
ON B.Country_Id = A.Country_Id
WHERE B.Country_Type='ABC';
Now to get the same two records it takes 03:55 minutes (!)
I have looked into Estimated Execution Plan, but couldn't spot any difference - all straight forward.
It is SQL 2012 SP1 with compatibility level set to 110
View 9 Replies
View Related
Feb 13, 2014
We have a table with 2 columns 'OrderNo' and 'Amount' as below
ORDERNO | AMOUNT
1D1ZX000 | 9262.5
1D1ZX001 | 9000.0
1D1ZX001 | 9000.0
1D1ZX002 | 10000
1D1ZX003 | 1000
1D1ZX003 | 200.50
1D1ZX003 | 100.50
1D1ZX004 | 500.0
1D1ZX004 | 1000
1D1ZX004 | 2000
1D1ZX004 | 1000
as per my client requirement we need subtotal of 'Amount' group by 'OrderNo'. column so am writing a select statement with WHERE condition and I would like to have another column called SUBTOTAL in the result set (select statement result) with subtotals for that order Number as below
ORDERNO | AMOUNT | SubTotal
1D1ZX000 | 9262.5 | 9262.5
1D1ZX001 | 9000.0 | 18000
1D1ZX001 | 9000.0 | 18000
1D1ZX002 | 10000 | 10000
1D1ZX003 | 1000.0 | 3001
1D1ZX003 | 2000.5 | 3001
1D1ZX003 | 1000.5 | 3001
1D1ZX004 | 500.00 | 4500
1D1ZX004 | 1000.0 | 4500
1D1ZX004 | 2000.0 | 4500
1D1ZX004 | 1000.0 | 4500
View 7 Replies
View Related
