Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    13

    Unanswered: Using Single Quotes in Dynamic SQL

    Okay, here's the deal. I've got a stored procedure on UDB DB2 and I'm passing in a parm that is integer that needs to be compared with a field on a table that was defined as character, but is really numeric.

    In the stored procedure, I'm building a dynamic SQL statement and I need to put single quote marks around the passed-in parm so it can be compared to the table.

    For example:

    WHEN 'SubClass' THEN
    IF in_sub > 0 AND in_class > 0 THEN
    SET WHERESTMT = WHERESTMT || 'AND A.lot = ' || in_class ;
    END IF;
    In the above, in_class needs to have single quotes around it. I've tried using double quotes, triple-single quotes, double-single quotes (like in ASP), double quotes around single quotes and escape characters (\'), but no luck. It simply doesn't want to do it.

    Any ideas how I can do it?

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by mferrell
    Okay, here's the deal. I've got a stored procedure on UDB DB2 and I'm passing in a parm that is integer that needs to be compared with a field on a table that was defined as character, but is really numeric.

    In the stored procedure, I'm building a dynamic SQL statement and I need to put single quote marks around the passed-in parm so it can be compared to the table.

    For example:



    In the above, in_class needs to have single quotes around it. I've tried using double quotes, triple-single quotes, double-single quotes (like in ASP), double quotes around single quotes and escape characters (\'), but no luck. It simply doesn't want to do it.

    Any ideas how I can do it?
    '' (ie. two single quotes) is supposed to do it, that's the way to escape a single quote in db2... that's not working???
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I suspect mferrell can't concatenate an integer to a string, aside from the quotes problem. The statement should look something like
    Code:
    ... SET WHERESTMT = WHERESTMT || 'AND A.lot =''' || ltrim(char(in_class)) || ''''

  4. #4
    Join Date
    Aug 2003
    Posts
    13
    You guys were right on both counts. Simple mistakes, simple mistakes.

    Here's the final version that worked:

    SET WHERESTMT = WHERESTMT || 'AND A.lot = ''' || cast(in_class as char(10)) || '''';
    Much appreciated!

Posting Permissions

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