Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: sql

  1. #1
    Regular Coder
    Join Date
    Sep 2011
    Posts
    128
    Thanks
    2
    Thanked 21 Times in 21 Posts

    sql

    Code:
    USE myDB
    /****** Script for SelectTopNRows command from SSMS  ******/
    SELECT	
    	CASE
    		WHEN ISNULL(CAST([Geheugen] as varchar(50)),'')!='' THEN [Geheugen] + 'mb'
    	END as Geheugen,
    
      FROM table
      WHERE [ID] = 65
    This piece of code gives me the following error: Error converting data type varchar to numeric.

    The type from the column 'Geheugen' is numeric.

    This code needs to make it to a string a return the column data as: 126mb (The 126 is the value in the database)

    How can i fix this problem?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    And what database is this??

    If it is MySQL, you can't use + to concatenate strings. You *must* use the CONCAT function.

    And your code doesn't really make sense: What will the value of Geheugen be when the CASE is *not* true? You don't provide an ELSE for other values.

    And if Geheugen in the table is a numeric type (it must be, based on the error message), then why do you need the cast? Numeric types can only be null or numbers, they can never be a blank string.

    So...
    Code:
    SELECT CASE WHEN Geheugen IS NOT NULL THEN CONCAT(Geheugen,'mb')
                ELSE 'n/a'
                END AS Geheugen
    FROM table
    WHERE id = 66
    You provide whatever value you want there for the ELSE, in place of my 'n/a'

    **********

    HOWEVER...

    I don't think this is MySQL, since you used [Geheugen] in one place, and that's more likely to be SQL Server, yes?

    And SQL Server doesn't allow you to CONCAT text and numbers, so this is for SQL Server:
    Code:
    SELECT CASE WHEN Geheugen IS NOT NULL THEN CONVERT(VARCHAR(10),Geheugen) + 'mb'
                ELSE 'n/a'
                END AS Geheugen
    FROM table
    WHERE id = 66
    ***********

    EDIT: I just noticed the comment about "from SSMS", so of course this is indeed SQL Server. But next time say what DB you are using. As you can see, it makes a difference.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Sep 2011
    Posts
    128
    Thanks
    2
    Thanked 21 Times in 21 Posts
    I'm sorry, it is indeed SQL Server.
    I already solved it with help of a other forum (The SQL Server forums :P)

    Here is the code that helped:

    Code:
    	CASE
    		WHEN ISNULL(CAST([GS].[Geheugen] as varchar(50)),'')!='' THEN CAST([GS].[Geheugen] AS varchar(50)) + 'mb'
    	END as Geheugen,
    The value will be either a numeric or a null.

    If the value is a null it will return null (This is how my code works :P)
    If it isn't null then it must return a varchar string, 2 mb in this case.

    I tried this on several ways before i asked here and on the SQL Server forum but they helped me with a working code.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    You still are *NOT* handling the case when ISNULL(CAST([GS].[Geheugen] as varchar(50)),'')!='' is false!

    When the field is indeed NULL, then you will have NO VALUE AT ALL for the final value of Geheugen.

    On top of that, my code is more efficient. It doesn't need to do a CAST twice. (To be fair, the code optimizer in SQL Server will probably only actually do the CAST once, but you can't know that.)

    There's really not a much more efficient comparison in SQL Server than IS NULL or IS NOT NULL.
    Code:
         Geheugen IS NOT NULL
    is going to execute much faster than the code you show.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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