SQL Server 2008 :: How To Get Rid Of NULL In Results From Case When
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
ADVERTISEMENT
Aug 31, 2015
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies
View Related
Mar 25, 2015
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
View 4 Replies
View Related
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
View Related
Oct 12, 2007
easy question:
If I use "results to text" option when executing a query in Ms SQL Server Management Studio null value is inserted as 'NULL' string in the result. Is it possible to have blank ('') string instead of 'NULL' in the result in 2005 version.
It must be possible, but I could not find it in tools/options
thank you in advance
Andras
View 6 Replies
View Related
Oct 7, 2015
I have written one query like this
select staffid,staffname,deptname
From staff s join dept d on s.deptid=d.deptid
This query we have no results
I need this results
staffid staffname deptname
null null null
View 7 Replies
View Related
Mar 1, 2015
I have a question on date manipulation functions and CASE statements
My sql is passed the following parameter's and performs a select using a manipulation on these date param's to get a start and end date range depending on the conditions;-
monthColHeader = eg 'Feb 2015'
defaultStartDate and defaultEndDate
filterStartDate and filterEndDate.
These are my conditions;-
if defaultStart and End = filterStart and End use monthColHeader for the date range
if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd match then use the filterStart & End month/Year with the monthColHeader to get the date range
if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd DON't match use filterStart Day and monthColHeader for our start date and monthColHeader for our end date.
When I say use monthColHeader I mean like this;-
(r.dbAddDate >= (CAST('@Request.monthColHeader ~' AS DATETIME)) AND r.dbAddDate < DATEADD(mm,1,'@Request.monthColHeader ~'))
This sql works for converting say 'Feb 2015' to '2015-02-01' & '2015-02-28'....
View 1 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
Oct 29, 2015
The below would work if all of the values in A.Value were numbers but they are not. So I need to restrict the view to only look at the following measures but still show all the other row.
WHERE [Measure] IN ('RTT-01','RTT-04','RTT-07')
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
[Code] ....
Is there any way that I can create a select statement in the case when to only look for them measures that I know contain numbers?
View 9 Replies
View Related
May 28, 2015
Say I want to search for a range of account numbers but only which are active. After I set my field for A (active) this field shows in my results, I dont want it to.
In Access you can easily just uncheck that field in design view, but how do I do it in sql?
View 4 Replies
View Related
Apr 23, 2015
I have the Person table with the following fields:
Id_Person
Nm_Person
Id_AddressResidential
Id_AddressCommercial
Another table called Address with the following fields:
Id_Address
Ds_Street
I want to make the relationship between these tables so that when deleting a related field address in Person is set to null.the SQL Server allows me to make this relationship in only one field.A person can live and work in the same place. If I delete her address, I want the two Individual fields are set to null.
View 5 Replies
View Related
Aug 25, 2015
I want to remove the nulls from the result set so the result is 1 line. Code and results are below:
SELECT
CustomerNumber,
(case when yearseq = 2012 then isnull(sum(mainPower),0)+isnull(sum(sidePower),0)+isnull(Sum(leftPower),0)
+isnull(Sum(netappPower),0)+isnull(Sum(rightPower),0)+isnull(Sum(lowerPower),0) end) as '2012',
(case when yearseq = 2013 then
[Code] ....
What can I do to my code to remove the Nulls to the entire result is just 1 line?
View 2 Replies
View Related
Apr 6, 2015
I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.
ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR
[Code] .....
View 2 Replies
View Related
Jul 31, 2015
I have the following code below where I need to have all of the query results output into a .csv file to use in a VBA macro. The issue I am running into is that the data is not deliminating correctly and my rows are being shifted incorrectly. Any better way of out putting the results into a .csv file with a common delimiter.
-- Declare the variables
DECLARE @CMD VARCHAR(4000),
@DelCMD VARCHAR(4000),
@HEADERCMD VARCHAR(4000),
@Combine VARCHAR(4000),
@Path VARCHAR(4000),
@COLUMNS VARCHAR(4000)
[Code] ...
Output from query (please post in a text editor. The line starting with (only ) should be on line 1 after 20 pks and is shifted to a new line.):
557898^1^9885E25^80082^9.0 CM GLASS FIBER PADS 20PKS
(only 12 pks in stock that will ship today)
^12.00000000^.00000000^18.32000000^219.84000000000^28.30000000
^339.60000000000^9.98000000^35.2650176678445^DR9146322^0^
[Code] ....
View 1 Replies
View Related
May 13, 2015
I am stuck on a query where I need to display all the month year values even if the corresponding count_booking values are NULL. The requirement is to display the 13 month year period from current date.
For e.g. if the date exists in the current month then starting from May 15 go all the way back to Apr 14.
My current query works in terms of displaying all the 13 months if the count_booking values exist for all the months. However, if some months are missing the values then those months don't show up.how to display all the months even if the values are NULL ? Query below:
SELECT COUNT(m.BOOKING_ID) AS count_booking, LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2)
AS month_name
FROM MG_BOOKING AS m
WHERE (m.CREATE_DT >= DATEADD(m, - 13, GETDATE()))
[code]...
View 8 Replies
View Related
Jun 11, 2015
Everytime I try to open up SSMS, I get this error:
"Values cannot be null. Parameter Name: ViewInfo"
When I press ok to login, It doesn't show me the database and system dbs aren't expandable.
View 4 Replies
View Related
Oct 15, 2015
Should data type money allow nulls? Are there valid arguments both pro and con?
Yes, there is the age-old question regarding how one might interpret a NULL found in any column - does it mean the amount is not known or that the amount is zero (in the case of a numeric type)? You get the drift...
Other than that, though - are there any practical considerations an old data hound ought to be aware of?
View 7 Replies
View Related
Jul 15, 2015
I have a set of results based on a query that displays a learner, their workplace assessor and both their email addresses.
I want to send a standard request email (using db_mail) to each learner's email address which specifies who their individual assessor is and their current employer. One per learner.
I then want to email each assessor and in this email detail their learners (and their employer details) that I have emailed the request too.
View 1 Replies
View Related
Aug 25, 2015
If I use msdb..sp_send_dbmail or save query results as text (using sqlcmd) and include the column headers I get the dashed separator line.
e.g.
custID, name
------, ------
1,bob
2,jamesI would like this
custID, name
1, bob
2 ,james
I found this method [URL] ....
::
sqlcmd -E -S (local) -d myDB -W -w 1024 -s "," -i "SELECT * FROM tblCust" | findstr /V /C:- /B > C: emp.csv
Can the same result be achieved sending as attachment with dbmail?
EXEC msdb..sp_send_dbmail @attach_query_result_as_file = 1I don't want to have to add column names as part of the query
Change the query to return column headers in resultset
SELECT 'CustID' as f1, 'name' as f2
UNION ALL
SELECT CAST(CustID as Varchar(10)), name FROM tblCustand set
msdb..sp_send_dbmail @query_result_header = 0
View 0 Replies
View Related
Nov 2, 2000
I am having trouble with the following:
SELECT CASE name
WHEN NULL THEN 'No name'
ELSE name
END
It is not working as I'd expect. The column displays <NULL> instead of 'No
name'. Any thoughts?
TIA
View 1 Replies
View Related
Mar 11, 2008
I am trying to format the Sys field (which is Int) in case the first byte is zero. But I'm getting an error msg;
Msg 156, Level 15, State 1, Procedure sp_Pull_Cancels, Line 43
Incorrect syntax near the keyword 'Right'.
CASE
WHEN substring(PCRACT, 5, 1) = '4' THEN 3641
WHEN substring(PCRACT, 5, 1) = '5' THEN 2012
WHEN substring(PCRACT, 5, 1) = '6' THEN 9694
END as Right('0000' + Cast(Sys as varchar (4)),4)
Any ideas how I construct this CASE statement to get the desired results.
Thanx,
View 13 Replies
View Related
May 19, 2006
SELECT whatever_field, CASE LEN(DrAccount) WHEN 12 THEN DrAccount ELSE CASE (Note1) WHEN NULL THEN Location + DrAccount ELSE Note1 + DrAccount END END AS Account FROM Table1 The purpose of the CASE(Note1) is when Note1 column is null, return Location+DrAccount. The actual result is when Note1 column is null, it always returns null, Location+DrAccount is not executed. When Note1 column is not null, it returns correctly Note1+DrAccount. The problem seems to reside in validating the null value in WHEN NULL How to check for null in CASE(fieldname) WHEN ???
View 1 Replies
View Related
May 26, 2015
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
View 0 Replies
View Related
Sep 19, 2014
I have a query that pulls a list of organizations. I'm trying to use a CASE statement to add a column of names based on the relationship of the individual to that organization. It is possible for two people at the organization to both have that relationship to the organization. Currently, when I pull my results, I get all the individual's names for all the organizations, but I'm also getting a row where there is no name, something like:
Company1 ''
Company1 'Individual1'
Company1 'Individual2'
Some of the organizations, they have no one with the relationship, but for all the organizations that do have someone in the position I want to not see the blank row. My case statement is:
CASE WHEN EXISTS
(Select ind_customer.cst_key WHERE ixo_rlt_code = 'Relationship' )
THEN ind_customer.cst_ind_full_name_dn
ELSE ''
END
AS 'FULL NAME'
View 7 Replies
View Related
May 6, 2008
Hello again! I'm having yet another problem with my code.
CASE Kit WHEN 'Y' THEN '(Kit)' WHEN 'N' THEN '' END + ' ' +
CASE cream WHEN 'Y' THEN '(cream)' WHEN ' N' THEN '' END + ' ' +
CASE Phone WHEN 'Y' THEN '(Phone)' WHEN 'N' THEN '' END
The problem that I am running into is that if one of the values comes up as NULL the whole field is NULL. am i doing something wrong?
View 11 Replies
View Related
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
Mar 17, 2008
I am trying to setup a query that receives input variables one being a number which could be null (could receive as string).
What I want to happen is if a number is sent the data is filtered by that number but if it is empty no filtering is performed I think this involves using a case statement in a where clause but can't work out the syntax I am using sql 200 server.
I think the case statement is something like this:
(week_no = CASE @week WHEN null THEN '#' ELSE @week END)
or maybe I am way off
I considered using like @inNumber + '%' but this would cause the problem of if I inoput 2 it would also return 20's 200's ...
View 3 Replies
View Related
Jul 23, 2005
HiConsider two tablesid1 code1----------- -----1 a2 b3 cid2 code2 value----------- ----- -----------1 a 02 a 13 b 1They are joined on the code field.For each code, I want the maximum corresponding value. If the valuedoesn't exist (corresponding code in second table doesn't exist), I wanta NULL field returned.The result should look like this:code2 value----- -----------a 1b 1c NULLI can't get it to include the NULL row.While there are uniqe ID's in this example, the real life example uses ahorrible four field compound key.Any help would be appreciated.Ger.The above example can be recreated by the following script.DROP table #temp1DROP table #temp2SELECT 1 AS 'id1', 'a' AS 'code1'INTO #temp1UNIONSELECT 2, 'b'UNIONSELECT 3, 'c'SELECT 1 AS 'id2', 'a' AS 'code2', 0 AS valueINTO #temp2UNIONSELECT 2, 'a', 1UNIONSELECT 3, 'b', 1SELECT code2, valueFROM #temp1 t1LEFT JOIN #temp2 t2 ON t1.code1 = t2.code2WHERE CASEWHEN t2.value IS NULL THEN 1WHEN t2.value = 0 THEN 2WHEN t2.value = 1 THEN 3END = (SELECTMAX( CASEWHEN value IS NULL THEN 1WHEN value = 0 THEN 2WHEN value = 1 THEN 3END )FROM#temp2WHEREcode2 = t2.code2)
View 5 Replies
View Related
Nov 9, 2007
Here's what I have and it's not working.
Code Block
SELECT [userID],
CASE
WHEN [mdlName] = NULL AND [mdnName] = NULL THEN [lstName] + ', ' + [fstName]
WHEN [mdlName] <> NULL AND [mdnName] <> NULL THEN [lstName] + ', ' + [fstName] + ' ' + [mdlName] + ' ' + [mdnName]
WHEN [mdlName] <> NULL AND [mdnName] = NULL THEN [lstName] + ', ' + [fstName] + ' ' + [mdlName]
WHEN [mdnName] <> NULL AND [mdlName] = NULL THEN [lstName] + ', ' + [fstName] + ' ' + [mdnName]
END AS [Full Name]
FROM [grUser]
ORDER BY [Full Name] ASC
The [Full Name] is always null. Any idea why? What I'm trying to do is if mdlName (middle name) and mdnName (maiden name) is null then display the lstName (last name) and fstName (first name) only and so on.
Any help is much appreciated.
View 4 Replies
View Related
Jun 19, 2015
DECLARE @I1 VARCHAR(5),
@I2 VARCHAR(5)
;
WITH cte
AS (SELECT 1 AS i,
'val1' AS j UNION ALL
SELECT 2,
'val2')
[code]....
Why @i1 is null but not @i2 ? I'm trying to assign values after grouping by the column.
View 17 Replies
View Related
Aug 10, 2006
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far:
SELECT
l.loanid,
p.investorid,
l.duedate,
case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate,
pc.interestrate
FROM loan l
inner join participation p on p.loanid = l.loanid
inner join paymentchange pc on pc.loanid = l.loanid
where p.investorid = '12345' and RateDueDate is not null
order by l.loanid, pc.duedate
I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated.
Thanks!
View 6 Replies
View Related
Apr 16, 2015
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
View 9 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
