Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    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]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What happens if you change the SQL to

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

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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
    Wendell

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    I pasted it in and ran it and this is the error and how it got converted back. I'm attaching a print screen
    Attached Images Attached Images

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by WendellB View Post
    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Leesha View Post
    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]

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HansV View Post
    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?

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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.
    Andrew

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Leesha View Post
    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.

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HansV View Post
    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!

  12. #12
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Leesha View Post
    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.
    Andrew

  13. #13
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Leesha View Post
    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]
    Andrew

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.

  15. #15
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Leesha View Post
    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.



    Andrew

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •