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

Dev Shed Forums Sponsor:

July 9th, 2003, 03:42 AM

myelite

Junior Member

Join Date: Jul 2003

Location: malaysia

Posts: 13

Time spent in forums: < 1 sec
Reputation 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.

July 9th, 2003, 06:47 PM

victorpendleton

Contributing User

Join Date: Jan 2003

Location: No es importante

Posts: 2,065

Time spent in forums: 6 h 50 m 52 sec
Reputation Power: 13

This is ansi sql standards. Try using
select ifnull(a, '') || ifnull(b, '') || ifnull(c, '') as test

July 9th, 2003, 09:36 PM

myelite

Junior Member

Join Date: Jul 2003

Location: malaysia

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks for advise . Unfortunately it doesn't work ,error 'function unknown'

July 10th, 2003, 03:53 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,352

Time spent in forums: 1 Month 4 Days 9 h 55 m 58 sec
Reputation Power: 390

use coalesce unstead of nullif

July 10th, 2003, 05:16 AM

myelite

Junior Member

Join Date: Jul 2003

Location: malaysia

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

Sorry I don't understand . Would you mind showing me the statement .
thx so much

July 10th, 2003, 05:44 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,352

Time spent in forums: 1 Month 4 Days 9 h 55 m 58 sec
Reputation Power: 390

Code:

select coalesce(a, '') || coalesce(b, '') || coalesce(c, '') as test

July 10th, 2003, 06:54 AM

myelite

Junior Member

Join Date: Jul 2003

Location: malaysia

Posts: 13

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks SwamBoogie.
I try this before your post ,but remain "function unknown"

July 10th, 2003, 04:40 PM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,352

Time spent in forums: 1 Month 4 Days 9 h 55 m 58 sec
Reputation Power: 390

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.

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

Developer Shed Advertisers and Affiliates