#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    malaysia
    Posts
    13
    Rep Power
    0

    'Select a||b||c as test' issue


    I write a sql statement to add 4 string to 1 , it gives strange result

    Select a||b||c as test

    if field c is null , then the result test is null . Otherwise it is normal .

    Is this a bug , or I need to modify the statement .

    any help is appreciated.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    This is ansi sql standards. Try using
    select ifnull(a, '') || ifnull(b, '') || ifnull(c, '') as test
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    malaysia
    Posts
    13
    Rep Power
    0
    Thanks for advise . Unfortunately it doesn't work ,error 'function unknown'
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    use coalesce unstead of nullif
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    malaysia
    Posts
    13
    Rep Power
    0
    Sorry I don't understand . Would you mind showing me the statement .
    thx so much
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Code:
    select coalesce(a, '') || coalesce(b, '') || coalesce(c, '') as test
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    malaysia
    Posts
    13
    Rep Power
    0
    Thanks SwamBoogie.
    I try this before your post ,but remain "function unknown"
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Well as coalesce is standard I assumed it was supported.

    You could try

    select nz(a, '')

    Maybe you have to write an IB function for this.

IMN logo majestic logo threadwatch logo seochat tools logo