Results 1 to 7 of 7

Thread: SIngle Quotes

  1. #1
    Join Date
    Aug 2004
    Posts
    14

    Unanswered: SIngle Quotes

    Hi Guys

    I am trying to write a script which will concatenate some text (to form a delete command for a separate db) and a field value. My select statement looks like :

    Select Concat ('delete * from adifftable where field1 =','''field1''') from myaudittable where myaudittable_date>trunc(sysdate)-1;

    This currently returns:

    delete * from adifftable where field1 = 'field1'

    What I actually want to see is (i.e. I want to return the value of field1)

    delete * from adifftable where field1 = '12345';

    Can anyone give me any pointers (as I am tearing my hair out!)

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    is this oracle? oracle as a CONCAT function?
    Code:
    select concat('delete * from adifftable where field1 ='
                , ''''
                , field1
                , '''') 
      from myaudittable 
     where myaudittable_date > trunc(sysdate)-1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Posts
    14
    Hi

    yes using Oracle SQL Plus (8.1.7.0.0). I have tried your suggestion but get:

    select concat('delete * from adifftable where field1 ='
    *
    ERROR at line 1:
    ORA-00909: invalid number of arguments

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, well, well, invalid number of arguments, eh?

    why? because oracle's concat function takes only two arguments!

    so try this --
    Code:
    select concat(
           concat(
           concat('delete * from adifftable where field1 ='
                , '''' )
                , field1 )
                , '''' )
      from ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2004
    Posts
    14
    Superb - just the job thanks. Virtual pint of beer for you

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Can't Oracle just do
    Code:
    select 'delete * from adifftable where field1 =''' || field1 || ''''
    ?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Aug 2004
    Posts
    14
    Yes that works too - thanks Peter (maybe I'm inadvertedly trying to overcomplicate things)


Posting Permissions

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