Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Unanswered: Problem in writing a sql query

    Hi,

    I am facing an issue in writing a sql query.

    DECLARE @QUERY varchar(8000),@s as varchar
    SELECT @Query =
    ' SELECT CONVERT(INT, ' + '0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6) + ') / 100.0 as AccountBalance'
    FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530
    exec (@query)

    In this query, I am converting a hexadecimal value to Int. My problem is in a single select statement I want few mpre fields (eg:ww8_mtr_no, Int datatype) to be extracted.

    I tried writing it like this:


    DECLARE @QUERY varchar(8000),@s as varchar
    SELECT @Query =
    ' SELECT ww8_mtr_no,CONVERT(INT, ' + '0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6) + ') / 100.0 as AccountBalance'
    FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530
    exec (@query)

    Error: Invalid column name 'ww8_mtr_no'.

    Then I tried:

    DECLARE @QUERY varchar(8000),@s as varchar
    SELECT @Query =
    ' SELECT' + ww8_mtr_no+',CONVERT(INT, ' + '0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6) + ') / 100.0 as AccountBalance'
    FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530
    exec (@query)

    error: Syntax error converting the varchar value ' SELECT ' to a column of data type int.

    Can anyone plz suggest how to write it.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why are you using dynamic SQL here?
    Code:
    SELECT b.ww8_mtr_no
         , Convert(int, '0X' + SubString(a.DPS_POSTAGE_ID, 21 ,6)) / 100.0 As [AccountBalance]
    FROM   DpSettings a
     INNER
      JOIN WW008T b
        ON a.DPS_MTR_NO = b.WW8_MTR_NO
    WHERE  b.ww8_acct_no = 88944530
    Any good?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2009
    Posts
    5

    Re

    That will not work as it is a hex to int conversion:

    SELECT b.ww8_mtr_no
    , Convert(int, '0X' + SubString(a.DPS_POSTAGE_ID, 21 ,6)) / 100.0 As [AccountBalance]
    FROM DpSettings a
    INNER
    JOIN WW008T b
    ON a.DPS_MTR_NO = b.WW8_MTR_NO
    WHERE b.ww8_acct_no = 88944530

    Error msg: Syntax error converting the varchar value '0X107AC0' to a column of data type int.

    see other solutions I tried,


    Sloution 1: DECLARE @QUERY varchar(8000),@s as varchar
    SELECT @Query =
    ' SELECT ww8_mtr_no,CONVERT(INT, ' + '0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6) + ') / 100.0 as AccountBalance'
    FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530
    print (@query)

    O/p: SELECT ww8_mtr_no,CONVERT(INT, 0X107AC0) / 100.0 as AccountBalance

    Since in the Query I have not mentioned the table name, "Invalid column name 'ww8_mtr_no'." is coming.

    Solution2: DECLARE @QUERY varchar(8000),@s as varchar
    SELECT @Query =
    ' SELECT' + ww8_mtr_no+',CONVERT(INT, ' + '0X' + SUBSTRING(DPS_POSTAGE_ID, 21 ,6) + ') / 100.0 as AccountBalance'
    FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530
    print (@query)

    O/p: Not getting output, "Syntax error converting the varchar value ' SELECT ' to a column of data type int", becoz @query is string and ww8_mtr_no is integer.

    Solution 3:


    DECLARE @QUERY varchar(8000),@s as varchar

    SELECT @Query =

    ' SELECT ' + 'ww8_mtr_no'+',CONVERT(INT, ' + '0X' + 'SUBSTRING(DPS_POSTAGE_ID, 21 ,6)' + ') / 100.0 as AccountBalance '

    + 'FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530'

    print (@query)

    O/p:

    SELECT ww8_mtr_no,CONVERT(INT, 0XSUBSTRING(DPS_POSTAGE_ID, 21 ,6)) / 100.0 as AccountBalance FROM DpSettings a, WW008T b WHERE DPS_MTR_NO=WW8_MTR_NO and ww8_acct_no=88944530


    See error in 0X concatenation, this concatenation is required to convert hex into int.while executing it will say "Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'SUBSTRING'."

    Please suggest what can be done.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also posted here: Problem in writing a select query : Transact-SQL : SQL Server : MSDN Forums

    Tiwari, please include links to other places that you've posted so that the contributers do not duplicate their efforts.
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you tried my suggestion above?

    What table does ww8_mtr_no exist in?
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tiwari_neha
    That will not work as it is a hex to int conversion
    Actually, hex to int converts fine. Your problem is you are converting a string to int (albeit the string is a representation of a hex value).

    Here is a common method for converting a string hex value to int:
    HexToINT - SQL Server Central
    Note, you can dump the last two additons from that as your hex value is six characters.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2009
    Posts
    5
    I have already replied that I executed but its giving error.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT b.ww8_mtr_no
         , dbo.HexToInt(SubString(a.DPS_POSTAGE_ID, 21 ,6)) / 100.0 As [AccountBalance]
    FROM   DpSettings a
     INNER
      JOIN WW008T b
        ON a.DPS_MTR_NO = b.WW8_MTR_NO 
    WHERE  b.ww8_acct_no = 88944530
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Still moonlighting at MSDN huh? Why not just slap us in the face?
    What do three medals mean? Are they awarded for valiance in the face of a Flame Warrior?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2009
    Posts
    5
    Quote Originally Posted by pootle flump
    Code:
    SELECT b.ww8_mtr_no
         , dbo.HexToInt(SubString(a.DPS_POSTAGE_ID, 21 ,6)) / 100.0 As [AccountBalance]
    FROM   DpSettings a
     INNER
      JOIN WW008T b
        ON a.DPS_MTR_NO = b.WW8_MTR_NO 
    WHERE  b.ww8_acct_no = 88944530

    Can you please send me the code for dbo.HexToInt function.

    Thanks in advance

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    See link in post 6 above, post by Poots.
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    Still moonlighting at MSDN huh? Why not just slap us in the face?
    What do three medals mean? Are they awarded for valiance in the face of a Flame Warrior?
    Yep, I frequent that one nearly as often as this one; I only hang around the Transact-SQL topic though.

    The forums on MSDN work on a recognition system ("medals" are awarded at certain levels of points), meaning that you get points for each post, answer and helpful response. I hate the whoring out that goes on over there; people are more interested in gaining points for something that "just works". I like to contradict people on there in as constructive a way as possible

    P.S. aren't you a SQLTeamer?
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tiwari_neha
    Ya I tried that, but I am unable to open the link.
    Rly? What happens?
    Code:
    http://www.sqlservercentral.com/scripts/T-SQL+Aids/31486/
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2009
    Posts
    5
    Quote Originally Posted by georgev
    See link in post 6 above, post by Poots.
    Ya I tried that, but I am unable to open the link.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    P.S. aren't you a SQLTeamer?
    I think I burned my bridges over there when this site went down. I told them I intended to do to that forum what I did to this one - they weren't too thrilled
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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