Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Unanswered: concatenating a '%' to a string of numbers

    I know I am being a bad database dude and I am trying to handle some presentation issues in the database. I am horrible. I know.

    how come this works...

    Code:
    SELECT CAST(CAST(CASE WHEN @BUSINESSDAYSTODATE > 0 AND @TOTALBUSINESSDAYS > 0 AND Q.HSI/@TOTALBUSINESSDAYS > 0 THEN ((T.HSI/@BUSINESSDAYSTODATE) / (Q.HSI/@TOTALBUSINESSDAYS)) * 100  ELSE 0.0 END AS NUMERIC(10,2))  AS VARCHAR(10)) 
    FROM TABLET T
    JOIN TABLEQ Q
    ON....
    but not...

    Code:
    SELECT CAST(CAST(CASE WHEN @BUSINESSDAYSTODATE > 0 AND @TOTALBUSINESSDAYS > 0 AND Q.HSI/@TOTALBUSINESSDAYS > 0 THEN ((T.HSI/@BUSINESSDAYSTODATE) / (Q.HSI/@TOTALBUSINESSDAYS)) * 100  ELSE 0.0 END AS NUMERIC(10,2))  AS VARCHAR(10))  + '%'
    FROM TABLET T
    JOIN TABLEQ Q
    ON....
    neither does

    Code:
    SELECT CAST(CAST(CASE WHEN @BUSINESSDAYSTODATE > 0 AND @TOTALBUSINESSDAYS > 0 AND Q.HSI/@TOTALBUSINESSDAYS > 0 THEN ((T.HSI/@BUSINESSDAYSTODATE) / (Q.HSI/@TOTALBUSINESSDAYS)) * 100  ELSE 0.0 END AS NUMERIC(10,2))  AS VARCHAR(10))  + char(37)
    FROM TABLET T
    JOIN TABLEQ Q
    ON....
    I see other dorks on the internet doing this all over the place,

    http://www.sqlservercentral.com/Foru....aspx#bm558766

    but I keep getting...

    Code:
    Msg 8114, Level 16, State 5, Procedure p_MyProc, Line 45
    Error converting data type varchar to numeric.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can't repolicate. Wanna change my DDL so it b0rks out?
    Code:
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.TABLET ')) BEGIN
    DROP TABLE dbo.TABLET 
    END
    
    CREATE TABLE dbo.TABLET 
    (
    HSI INT    NOT NULL
    )
    GO
    
    INSERT TABLET 
    SELECT    DISTINCT TOP 10 number
    FROM    master.dbo.spt_values
    WHERE number > 0
    
    DECLARE @BUSINESSDAYSTODATE        AS DECIMAL(10, 2)
            , @TOTALBUSINESSDAYS    AS DECIMAL(10, 2)
    
    SELECT @BUSINESSDAYSTODATE        = 998
            , @TOTALBUSINESSDAYS    = 4544
    
    SELECT    CAST(
                CAST(
                        CASE 
                            WHEN @BUSINESSDAYSTODATE > 0 AND @TOTALBUSINESSDAYS > 0 AND T.HSI/@TOTALBUSINESSDAYS > 0 THEN 
                                ((T.HSI/@BUSINESSDAYSTODATE) / (T.HSI/@TOTALBUSINESSDAYS)) * 100  
                            ELSE 
                                0.0 
                            END 
                AS NUMERIC(10,2))  
            AS VARCHAR(10))  + '%'
    FROM TABLET T
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it's because I am a big dork and I did not give complete information. i have not fixed it yet but I am certain it is because this part of a union and of course all of the columns across a union need to be the same data type.

    i need to go back to SQL school and not pass GO and not collect $200.

    I am such a scrub.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    They don't need to be the same datatype, but they must be compatible datatypes and the output datatype is determined by the first query in the UNION.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    ... output datatype is determined by the first query in the UNION.
    which is why, in some UNION queries, where placeholders are required in the first SELECT to construct a column that later SELECTs will supply values for, you will sometimes see CAST(NULL AS datatype)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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