SQL Query pulling up nulls where there is data

Topic

New Reply

Hi,

I have a query that is being run in SQL Server 2008. I needs to pull up the [studentnameid] and then join together the name as [lastname], [firstname] [Mi]. When it is run without joining the names I get 2978 records. When I run it with the following code, I get 78 records and the rest show null values. I’m sure my code is wrong and would appreciate input. I also have not idea wher the “TOp (100 percent” comes from as I didn’t intentionally add that. Thanks!

SELECT TOP (100) PERCENT IDStudentName, LastName + N’,’ + N’ ‘ + FirstName + N’ ‘ + Mi AS [Student Name]
FROM dbo.tblStudentNames
ORDER BY [Student Name]

Reply | Quote

Replies

What happens if you change the SQL to

SELECT IDStudentName, [LastName] & ‘, ‘ & [FirstName] & ‘ ‘ & [Mi] AS [Student Name]
FROM dbo.tblStudentNames
ORDER BY [Student Name]

Reply | Quote

I pasted it in and ran it and this is the error and how it got converted back. I’m attaching a print screen

Reply | Quote

I pasted it in and ran it and this is the error and how it got converted back. I’m attaching a print screen

I thought you were running the query from Access, where & is the concatenation operator; you can’t use that in SQL Server. You need + there.

What is the purpose of the letters N in the original code?

LastName + N‘,’ + N‘ ‘ + FirstName + N‘ ‘ + Mi AS [Student Name]

Reply | Quote

I thought you were running the query from Access, where & is the concatenation operator; you can’t use that in SQL Server. You need + there.

What is the purpose of the letters N in the original code?

LastName + N‘,’ + N‘ ‘ + FirstName + N‘ ‘ + Mi AS [Student Name]

It was the only way that I could get it to have spaces between the names. How should I have it written?

Reply | Quote

It was the only way that I could get it to have spaces between the names. How should I have it written?

I’m sorry, I’m not familiar with SQL Server.

Reply | Quote

I’m sorry, I’m not familiar with SQL Server.

NP!! God only knows you’ve helped me with just about everything else I’ve had over the years!

Reply | Quote

NP!! God only knows you’ve helped me with just about everything else I’ve had over the years!

No Leesha, we know how much Hans has helped you, he has helped us all. He is an absolute marvel.
We owe him a huge debt of gratitude.

Reply | Quote

What is the purpose of the letters N in the original code?

SQL use local specific character set with CHAR, VARCHAR characters.

Char are length specific, Varchar ar variable length

N Denotes Unicode character set. which you probably do not need with standard characters.

So ‘Bob’ and N’Bob’ for most local purposes are the same, but SQL Query builder tends to put them in by default.

Reply | Quote

Your query looks suspect to me. But some questions:

Are you running that query in SQL Server as a view?
Or are you running it as a pass-through query in Access?
Or are you working with an Access Project?

What looks suspect to me is the LastName + N’,’ + N’ ‘ + FirstName + N’ ‘ + Mi

Reply | Quote

Your query looks suspect to me. But some questions:
>>

Are you running that query in SQL Server as a view?

Yes – it’s being used with an asp.net page so its being run as a view from Sql server.

>>Or are you running it as a pass-through query in Access?

I have a similar on in an access project that runs fine.

>>

What looks suspect to me is the LastName + N’,’ + N’ ‘ + FirstName + N’ ‘ + Mi

Well that’s just my lame attempt from trial and error to get the return data to be cancantuated (I know I butchered that word!) to be lastname, firstname MI —- I needed the spaces.

Reply | Quote

Try it out as………..

Code:

SELECT TOP (100) PERCENT   
IDStudentName,   LastName + ‘, ‘ + FirstName + ‘ ‘ + ISNULL(Mi,”) AS [Student Name]
FROM dbo.tblStudentNames
ORDER BY [Student Name]

IF any of the fields Lastname, Firstname, Mi are NULL, then the result will be NULL.
You can use ISNULL to fix this.
It is like Access NZ function.

You can use ISNULL on any field to return something against a NULL

e.g. ISNULL(Firstname,”) returns ” if No name rather than NULL

So for Example

ISNULL(Lastname+’, ‘,”) + ISNULL(Firstname+’ ‘,”) + ISNULL(Mi,”) AS [Student Name] would cover any chance of a NULL
causing NULL this is because unlike the Access & operator SMITH + NULL in SQL returns NULL.

You cannot use & because it is NOT supported by SQL. This is Access only.

The TOP 100 Percent is added by the Query Builder.

In SQL, if this was built as a VIEW, then it will not use the ORDER BY clause unless you use Top 100 Percent.

I do not know if the above will work for you, but I just tested it against a table with 4606 records and it returned them all.

The only difference was the field and table names.

Reply | Quote

Thanks Andrew, this did the trick! I did get an error when I tried the piece with the nulls. I copied exactly as you had but got an error stating invalid or missing expression.

Reply | Quote

Thanks Andrew, this did the trick! I did get an error when I tried the piece with the nulls. I copied exactly as you had but got an error stating invalid or missing expression.

Not sure why. Maybe it needed the spaces either side of the +

Is Mi a text field?

Try ……..

Code:

SELECT TOP (100) PERCENT 
 	IDStudentName,  
 	ISNULL(LastName + ', ','') + ISNULL(FirstName + ' ','') + ISNULL(Mi,'') AS [Student Name] 
FROM dbo.PERSONNEL
ORDER BY [Student Name] 

Reply | Quote

All three are nvarchar. That last code gave me a similar error to the print screen that I uploaded earlier. I did change the table name to be the name of my table but still got the error.

Reply | Quote

All three are nvarchar. That last code gave me a similar error to the print screen that I uploaded earlier. I did change the table name to be the name of my table but still got the error.

Not sure what is going on there sorry.
Just stick with the one that works.

Reply | Quote

Not sure what is going on there sorry.
Just stick with the one that works.

I keep getting an incorrect syntax near ‘)’. I’ve tried all sorts of combo’s but can’t get it. I’ll keep plugging but in the meantime wanted to say thank you so much!!!

Leesha

Reply | Quote

All three are nvarchar. That last code gave me a similar error to the print screen that I uploaded earlier. I did change the table name to be the name of my table but still got the error.

Take the parens out of the TOP statement. It should read

Code:

SELECT TOP 100 PERCENT

Reply | Quote

Take the parens out of the TOP statement. It should read

Code:

SELECT TOP 100 PERCENT

Definitely worth a try but it shouldn’t make any difference.
I tried it with both

TOP 100 Percent
and
TOP (100) Percent

and got the same results.
Unless this is specific to SQL 2008, because I was running it under SQL 2005.

Reply | Quote

I went in and tried taking out the ()but got the same error. That is when I noted I had spelled the table name wrong! Fixed that it works fine. Thanks so much to both of you!
Leesha

Reply | Quote

I went in and tried taking out the ()but got the same error. That is when I noted I had spelled the table name wrong! Fixed that it works fine. Thanks so much to both of you!
Leesha

No problem. SQL Server is not particularly good at reporting the cause of errors.

Reply | Quote

Tell me about it! I spent all day chasing around an error only to find out that is means that I need to have the database convert a null value to an integer! I seriously feel like you little .gif guy.

So, how what would the syntax look like in sql server if I want to convert a [dischargestatus] to the number 8 if the field is null?

Thanks!
Leesha

Reply | Quote

Tell me about it! I spent all day chasing around an error only to find out that is means that I need to have the database convert a null value to an integer! I seriously feel like you little .gif guy.

So, how what would the syntax look like in sql server if I want to convert a [dischargestatus] to the number 8 if the field is null?

Thanks!
Leesha

If you just want to see an 8 where the field is null then use

ISNULL([dischargestatus],8) AS [DischargeStat] or Whatever you want to call the field

The IsNull function in SQL is a substitution function that uses the second parameter as a substitution for NULL in the first field argument.
If the field is NOT NULL then the normal field value will be used.
It is equivalent to the Access NZ function.

Reply | Quote

If you just want to see an 8 where the field is null then use

ISNULL([dischargestatus],8) AS [DischargeStat] or Whatever you want to call the field

The IsNull function in SQL is a substitution function that uses the second parameter as a substitution for NULL in the first field argument.
If the field is NOT NULL then the normal field value will be used.
It is equivalent to the Access NZ function.

Hi!
I finally had a chance to try this out and of course I’m struggling. The rationale makes perfect sense, its writing it that I’m messing up on. The sql presently says
SELECT DischargeReason
FROM tblStudentStatus
ORDER BY StatusDefined DESC

No matter where I insert the code you gave me I get an error or no change. I’m sure I’m doing it wrong. The goal is for DischargeReason to = 8 if it is null. I tried:

SELECT DischargeReason
FROM tblStudentStatus
ORDER BY StatusDefined DESC
WHERE ISNULL([DischargeReason],8)

Leesha

Reply | Quote

Hi!
I finally had a chance to try this out and of course I’m struggling. The rationale makes perfect sense, its writing it that I’m messing up on. The sql presently says
SELECT DischargeReason
FROM tblStudentStatus
ORDER BY StatusDefined DESC

No matter where I insert the code you gave me I get an error or no change. I’m sure I’m doing it wrong. The goal is for DischargeReason to = 8 if it is null. I tried:

SELECT DischargeReason
FROM tblStudentStatus
ORDER BY StatusDefined DESC
WHERE ISNULL([DischargeReason],8)

Leesha

Not sure what you are trying to do here.
Are you just trying to see an 8 where the DischargeReason field is NULL, otherwise see the Reason Entered?

The first 2 examples below just view the data in the table.

If you are trying to show all the records and Discharge Reasons,
and show an 8 where it is NULL then this should do

Code:

SELECT 	ISNULL(DischargeReason,8) AS [Discharge Reason]
FROM 		tblStudentStatus
ORDER BY StatusDefined DESC

You should never need it in the where Clause

If on the other hand you only wanted to see the ones that were NULL then

Code:

SELECT 	ISNULL(DischargeReason,8) AS [Discharge Reason]
FROM 		tblStudentStatus
WHERE 	DischargeReason IS NULL
ORDER BY StatusDefined DESC

As a final option.
If you are intending UPDATING the tblStudentStatus table
so that all existing NULLS are replaced with an 8 then you should use the last query

*** Be careful with this one it will change your data ***
This final one would actually change the existing data in the table
so you should test it on a Copy of the database or have a backup of the table

Code:

–This last one will change the value in the Discharge Reason Field from NULL to 8 permanently
UPDATE      tblStudentStatus
SET            DischargeReason =  8
WHERE      DischargeReason IS NULL

Reply | Quote

Thanks! The first option worked perfectly. I’m so used to Access Queries being horizontal, that setting up the sql queries vertically still messes me up.

Thanks again,
Leesha

Reply | Quote

No problem at all.

It can be written horizontally as well in SQL.
SQL does not use lines, it just makes it easier to see each part

It would have worked just the same as

Code:

 SELECT  ISNULL(DischargeReason,8) AS [Discharge Reason]  FROM  tblStudentStatus  ORDER BY StatusDefined DESC  

Reply | Quote

I meant when in the query builder view, SQL is vertical vs access being horizontal. I’m horrid at writing code so I depend on the query builder, although SQL servers is much easier to write than Access.

Leesah

Reply | Quote

Actually, writing SQL statements in Access is about the same as SQL Server, until you start using expressions. Then the VBA functions that you have available in Access are much more friendly. Conditional statements in T-SQL are a real pain and usually involve use the CASE statement. But the builder in SQL Server 2008 is just about as useful as the Access builder, and sometimes the ability to see the field grid (vertically) as well as the SQL string and sample data is useful.

Reply | Quote

I haven’t had to do any conditional statements yet! I think I’ll try to avoid those. I do agree that seeing both the grid and sql in the same view is nice. I’m much more comfortable in Access, however am having to learn SQL Server in order to use asp.net for websites. It’s been a struggle to say the least, but I do love a challenge.

Thanks!
Leesha

Reply | Quote

I haven’t had to do any conditional statements yet! I think I’ll try to avoid those. I do agree that seeing both the grid and sql in the same view is nice. I’m much more comfortable in Access, however am having to learn SQL Server in order to use asp.net for websites. It’s been a struggle to say the least, but I do love a challenge.

Thanks!
Leesha

To be honest, once you get the hang of them, they are just as easy as Access IIF’s.
In fact if you have multiple criteria then they can be a lot easier than the Access nested IIF functions.

Reply | Quote

To be honest, once you get the hang of them, they are just as easy as Access IIF’s.
In fact if you have multiple criteria then they can be a lot easier than the Access nested IIF functions.

LOL! Well you can count on the fact that I’ll be looking for help when I branch into these.

Leesha

Reply | Quote

To be honest, once you get the hang of them, they are just as easy as Access IIF’s.
In fact if you have multiple criteria then they can be a lot easier than the Access nested IIF functions.

Well I told you I’d be back. I’ll bet you didn’t figure it would be so quickly! The code I’m using works great with the exception of when LastName, FirstName and MI are all null. Then the query returns a comma only. I want this to return a null field with no comma at all. How would I write that? The code presently is:

SELECT TOP (100) PERCENT IDStudentName,
ISNULL(LastName + ‘, ‘, ”) + ISNULL(FirstName + ‘ ‘, ”) + ISNULL(Mi, ”) AS [Student Name], FROM dbo.tblStudentNames
ORDER BY [Student Name]

Thanks!
Leesha

Reply | Quote

Leesha,

Your code seems to be good as you can see in the attachment. Can you post your full SELECT statement ?
Added later :
Could it be that there are spaces in the fields LastName, FirstName or Mi ?

Reply | Quote

Leesha,

Your code seems to be good as you can see in the attachment. Can you post your full SELECT statement ?
Added later :
Could it be that there are spaces in the fields LastName, FirstName or Mi ?

Hi Francois!
Yes, there are no values in those fields. In that instance, I need the comma not to be inserted, or I should say, the fields not to be joined together. It would ONLY be when nothing is in ALL THREE fields. Is that possible?

Thanks,
Leesha

Reply | Quote

You can use :
SELECT CASE LTRIM(LastName) WHEN ” THEN ” ELSE LastName + ‘, ‘ + CASE LTRIM(FirstName) WHEN ” THEN ” ELSE FirstName + ‘ ‘ + Mi END END AS [Student Name]
FROM tblStudentNames

Look out !!! This line don’t work (always) in the query window of the SQL Server Enterprise Manager.
You should use the SQL Query Analyzer to build the SQL Statement

Reply | Quote

This one works and also caters for spaces in Lastname and Firstname fields

It is also a lesson in using CASE

Code:

SELECT TOP (100) PERCENT 
    IDStudentName,
    CASE 
        WHEN LTRIM(LastName) + LTRIM(FirstName) + LTRIM(Mi) = ” THEN ” 
        WHEN LTRIM(LastName)  ” AND LTRIM(FirstName)=” THEN LastName + ‘ ‘ + ISNULL(Mi,”)
        WHEN LTRIM(LastName)  ” AND FirstName IS NULL THEN LastName + ‘ ‘ + ISNULL(Mi,”) 
        ELSE ISNULL(LastName + ‘, ‘, ”) + ISNULL(FirstName + ‘ ‘, ”) + ISNULL(Mi, ”) 
    END 
    AS [Student Name] 
FROM dbo.tblStudentNames
ORDER BY [Student Name]

All that said, the problem is caused by the database allowing spaces only for LastName and FirstName fields.
The original solution allows for NULLS, but not SPACES only.

You’d be better off tidying the data and NOT allowing spaces ONLY in the LastName and FirstName fields.

The query below would replace fields with just spaces with NULL in LastName and Firstname
BUT if you are going to use it, I’d test it on a copy first.

Code:

UPDATE   	dbo.tblStudentNames
SET         	LastName = CASE LTRIM(LastName) WHEN ” THEN NULL ELSE LTRIM(RTRIM(LastName)) END,
                FirstName = CASE LTRIM(FirstName) WHEN ” THEN NULL ELSE LTRIM(RTRIM(FirstName)) END  

Reply | Quote

Thanks to both of you!

The database is actually pretty clean in that at the present time there are no spaces in the first and last names that I can tell anyway. There is one row that has no data in any of the fields. This is done on purpose so that when the asp.net page loads it doesn’t default to a record name, but rather to a blank field. The user is required to choose a name from the dropdownlist, they can’t save the page if no name is chosen. The problem I’m running into is that when there is a comma in the dropdownlist (which is the case when there LastName, FirstName MI are blank) the web page “thinks” that there is a name in the dropdownlist and allows the page to be save with just the comma and doesn’t force the user to choose a name. I need to somehow have the row appear without the comma, thus why I thought having the query return is as an empty cell would work. Does that make sense?

Leesha

Reply | Quote

Thanks to both of you!

The database is actually pretty clean in that at the present time there are no spaces in the first and last names that I can tell anyway. There is one row that has no data in any of the fields. This is done on purpose so that when the asp.net page loads it doesn’t default to a record name, but rather to a blank field. The user is required to choose a name from the dropdownlist, they can’t save the page if no name is chosen. The problem I’m running into is that when there is a comma in the dropdownlist (which is the case when there LastName, FirstName MI are blank) the web page “thinks” that there is a name in the dropdownlist and allows the page to be save with just the comma and doesn’t force the user to choose a name. I need to somehow have the row appear without the comma, thus why I thought having the query return is as an empty cell would work. Does that make sense?

Leesha

If this is just for a Combo Box, and the only issue is the case where they are all blank
and in all other cases you will have LastName and FirstName
then you could probably simplify it to

Code:

SELECT TOP (100) PERCENT 
    IDStudentName,
    ISNULL(LastName + ‘, ‘, ”) + ISNULL(FirstName + ‘ ‘, ”) + ISNULL(Mi, ”)  AS [Student Name] 
FROM dbo.tblStudentNames
WHERE (LTRIM(LastName) + LTRIM(FirstName) + LTRIM(Mi))  ”
ORDER BY [Student Name]

This Gives Just a List of Valid Names, NOT the Empty one

Reply | Quote

The only problem with this recent code is that the row that has cells that are blank(not null) is filtred out leaving only the rows with names. Normally this would be fine but what I run into is that when the page loads, either the first or last student name is shown, depending on the sort order. I need this to show the row with no data WITHOUT the comma. This way if the user saves the page, without entering / choosing a name, the cell remains blank in that row. Otherwise, they end up saving the wrong name if they don’t make a change. It’s a pain I know. I really do hate asp.net and web basd pages!!!

Thanks,
Leesha

Reply | Quote

The only problem with this recent code is that the row that has cells that are blank(not null) is filtred out leaving only the rows with names. Normally this would be fine but what I run into is that when the page loads, either the first or last student name is shown, depending on the sort order. I need this to show the row with no data WITHOUT the comma. This way if the user saves the page, without entering / choosing a name, the cell remains blank in that row. Otherwise, they end up saving the wrong name if they don’t make a change. It’s a pain I know. I really do hate asp.net and web basd pages!!!

Thanks,
Leesha

In that case, best to stick with

Code:

SELECT TOP (100) PERCENT
IDStudentName,
CASE
WHEN LTRIM(LastName) + LTRIM(FirstName) + LTRIM(Mi) = '' THEN ''
WHEN LTRIM(LastName) '' AND LTRIM(FirstName)='' THEN LastName + ' ' + ISNULL(Mi,'')
WHEN LTRIM(LastName) '' AND FirstName IS NULL THEN LastName + ' ' + ISNULL(Mi,'')
ELSE ISNULL(LastName + ', ', '') + ISNULL(FirstName + ' ', '') + ISNULL(Mi, '')
END
AS [Student Name]
FROM dbo.tblStudentNames
ORDER BY [Student Name]

Reply | Quote

Perfect! thanks!

Reply | Quote