Results 1 to 12 of 12

Thread: cast to varchar

  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: cast to varchar

    On v9.7:


    db2 "select varchar(count(0)) from (select count(c1) from test) as tmp"

    1
    -----------
    1

    1 record(s) selected.




    On v8.2:

    db2 "select varchar(count(0)) from (select count(c1) from test) as tmp"
    SQL0440N No authorized routine named "VARCHAR" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884



    I need to cast the result of the following query into a varchar:

    db2 "select count(0) from (select count(c1) from test) as tmp"


    v8 doesn't accept integer as an input to varchar(). Is there a way to accomplish this on v8?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try CAST(blah as VARCHAR(88))
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    By the way, "select '1' from sysibm.sysdummy1" will also work, as the query always returns 1...
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by n_i View Post
    Try CAST(blah as VARCHAR(88))
    Make it CAST(CAST(blah as CHAR(88)) as VARCHAR(88))
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Thanks, Nick. Using two CAST worked:

    db2 "select cast(cast(count(0) as char(88)) as varchar(88)) from (select count(c1) from test) as tmp"

    1
    ----------------------------------------------------------------------------------------
    1

    1 record(s) selected.


    I'll try using sysibm.sysdummy1

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by db2girl View Post
    I need to cast the result of the following query into a varchar:

    db2 "select count(0) from (select count(c1) from test) as tmp"
    why even run a query? the answer is always going to be 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Shh, Bella is booking over time
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by r937 View Post
    why even run a query? the answer is always going to be 1

    This is not my query and I'm still trying to understand the purpose of count(0)... doing multiple things at the same time so had no time to really think about your response to my other posting but I will.

    Could you please explain why the following query (attaching it here) returns 0.
    Attached Files Attached Files
    Last edited by db2girl; 03-23-10 at 14:43.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by db2girl View Post
    Could you please explain why the following query (attaching it here) returns 0.
    most likely it is because the subquery returned no rows -- try running the subquery by itself

    by the way, apologies to n_i, who had the same answer as i did in post #3 (i.e. it's always going to return 1)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Or how to replace "SELECT COUNT(0) AS "COUNT" " without changing the purpose of it. This count(0) is causing the query to fail with sql0901n which is what I'm trying to resolve.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change it to SELECT COUNT(*) ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think logically the entire query is equivalent to something like
    Code:
    count (distinct POL_STATUS_CD) from < that table> where <those conditions
    By the way, since the subquery is an outer join, the subtraction one of the SUM()s can be problematic, because both values could be null.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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