March 16, 2017 at 3:57 pm
I have setup a test view in t-sql 2012 which is as follows:
CREATE VIEW [dbo].[vDirectCer]
AS
select top 100 percent *
from test.dbo.DirectCer
order by mailAddress desc,city desc ,state desc ,zip desc
GO
However the sql I listed does not work as I needed it to.
What I need if any or all of the fields called mailAddress,city,state ,zip
contain null or spaces, the all 4 columns in the view need to display spaces.
In addition when these fields display spaces in the view, those records should be displayed first in the list of records that are displayed.
Thus would you show me the -t-sql 2012 that can be used to accomplish this goal?
March 16, 2017 at 4:07 pm
Use SQL Server's IsNull Function for each column in the select list which means you have to list the columns not SELECT *.
SELECT
IsNull(mailAddress, ' ') AS mailAddress
, IsNull(city, ' ') AS city
, IsNull(state, ' ') AS state
, IsNull(zip, ' ') AS zip
FROM test.dbo.DirectCer
ORDER BY mailAddress DESC,city DESC ,state DESC ,zip DESC;
March 17, 2017 at 4:24 am
I think such requests should be handled in front-end
But, the below code should helpSELECT TOP 100 PERCENT
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.mailAddress END AS mailAddress,
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.city END AS city,
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.state END AS state,
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.zip END AS zip
FROM test.dbo.DirectCer AS DC
CROSS APPLY (
SELECT CASE
WHEN DC.mailAddress IS NULL OR DC.mailAddress = '' THEN 1
WHEN DC.city IS NULL OR DC.city = '' THEN 1
WHEN dc.state IS NULL OR DC.state = '' THEN 1
WHEN dc.zip IS NULL OR DC.zip = '' THEN 1
ELSE 0
END AS NullCheck
) AS FN
ORDER BY FN.NullCheck DESC
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 17, 2017 at 5:40 am
Kingston Dhasian - Friday, March 17, 2017 4:24 AMI think such requests should be handled in front-end
But, the below code should helpSELECT TOP 100 PERCENT
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.mailAddress END AS mailAddress,
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.city END AS city,
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.state END AS state,
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.zip END AS zip
FROM test.dbo.DirectCer AS DC
CROSS APPLY (
SELECT CASE
WHEN DC.mailAddress IS NULL OR DC.mailAddress = '' THEN 1
WHEN DC.city IS NULL OR DC.city = '' THEN 1
WHEN dc.state IS NULL OR DC.state = '' THEN 1
WHEN dc.zip IS NULL OR DC.zip = '' THEN 1
ELSE 0
END AS NullCheck
) AS FN
ORDER BY FN.NullCheck DESC
Wow why would you even try to write something like that when a simple isnull is available.
Not to mention there is a huge flaw in your code - your setting nullcheck for the entire row so if a single column has a null the entire row will be blank........
March 17, 2017 at 5:49 am
FridayNightGiant - Friday, March 17, 2017 5:40 AMKingston Dhasian - Friday, March 17, 2017 4:24 AMI think such requests should be handled in front-end
But, the below code should helpSELECT TOP 100 PERCENT
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.mailAddress END AS mailAddress,
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.city END AS city,
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.state END AS state,
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.zip END AS zip
FROM test.dbo.DirectCer AS DC
CROSS APPLY (
SELECT CASE
WHEN DC.mailAddress IS NULL OR DC.mailAddress = '' THEN 1
WHEN DC.city IS NULL OR DC.city = '' THEN 1
WHEN dc.state IS NULL OR DC.state = '' THEN 1
WHEN dc.zip IS NULL OR DC.zip = '' THEN 1
ELSE 0
END AS NullCheck
) AS FN
ORDER BY FN.NullCheck DESCWow why would you even try to write something like that when a simple isnull is available.
Not to mention there is a huge flaw in your code - your setting nullcheck for the entire row so if a single column has a null the entire row will be blank........
The OP probably wants it that way when he says the below in his post
What I need if any or all of the fields called mailAddress,city,state ,zip
contain null or spaces, the all 4 columns in the view need to display spaces
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 17, 2017 at 6:03 am
Kingston Dhasian - Friday, March 17, 2017 5:49 AMFridayNightGiant - Friday, March 17, 2017 5:40 AMKingston Dhasian - Friday, March 17, 2017 4:24 AMI think such requests should be handled in front-end
But, the below code should helpSELECT TOP 100 PERCENT
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.mailAddress END AS mailAddress,
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.city END AS city,
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.state END AS state,
CASE WHEN FN.NullCheck = 1 THEN '' ELSE DC.zip END AS zip
FROM test.dbo.DirectCer AS DC
CROSS APPLY (
SELECT CASE
WHEN DC.mailAddress IS NULL OR DC.mailAddress = '' THEN 1
WHEN DC.city IS NULL OR DC.city = '' THEN 1
WHEN dc.state IS NULL OR DC.state = '' THEN 1
WHEN dc.zip IS NULL OR DC.zip = '' THEN 1
ELSE 0
END AS NullCheck
) AS FN
ORDER BY FN.NullCheck DESCWow why would you even try to write something like that when a simple isnull is available.
Not to mention there is a huge flaw in your code - your setting nullcheck for the entire row so if a single column has a null the entire row will be blank........The OP probably wants it that way when he says the below in his post
What I need if any or all of the fields called mailAddress,city,state ,zip
contain null or spaces, the all 4 columns in the view need to display spaces
Fair enough, I missed that first time. A strange requirement.
March 17, 2017 at 6:09 am
wendy elizabeth - Thursday, March 16, 2017 3:57 PMI have setup a test view in t-sql 2012 which is as follows:
CREATE VIEW [dbo].[vDirectCer]
AS
select top 100 percent *
from test.dbo.DirectCer
order by mailAddress desc,city desc ,state desc ,zip desc
GOHowever the sql I listed does not work as I needed it to.
What I need if any or all of the fields called mailAddress,city,state ,zip
contain null or spaces, the all 4 columns in the view need to display spaces.In addition when these fields display spaces in the view, those records should be displayed first in the list of records that are displayed.
Thus would you show me the -t-sql 2012 that can be used to accomplish this goal?
The ORDER BY won't work. SQL Server chops it out (unless you know how to cheat). Views should not be ordered - that should be handled by the calling query. What exactly are you trying to do? How is this view to be used?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 17, 2017 at 6:40 am
As told before in this thread , the order is not guaranteed if defined inside a view. The order should be specified in the query that calls the view.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply