[SQL] Can't convert numeric to_char() in catenate function

2003-10-16 Thread Yusuf 

Hi there.

Im trying to do the usual row catenation function, but for a "numeric" type column.  I 
have this:

CREATE OR REPLACE FUNCTION "verticat" (text, numeric) RETURNS text AS '
SELECT CASE WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL OR $1 =  
THEN to_char($2,'9D99')
ELSE $1 || '', '' || to_char($2,'D99')
   END
' LANGUAGE 'sql';

This works without problem with "float" or other type with direct casting to text 
(after replacing the "to_char(...)" with $2::text), but this one gives me an "ERROR:  
parser: parse error at or near "9""... wath is wrong with it??

It would also be nice to trim the spaces placed by to_char, with something like 
"ltrim(' ', to_char($2,'9D99'))", but that yields yet anoter error:  "ERROR:  
parser: parse error at or near "'"".

Any help 'preciated.  Thanks!

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] Can't convert numeric to_char() in catenate function

2003-10-16 Thread Yusuf 
I 'must' quote the quotes. I 'must' quote the quotes. I 'must' quote the quotes. I 
'must' quote the quotes. I 'must' quote the quotes. I 'must' quote the quotes. I 
'must' quote the quotes. 

DANG!!

I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. 
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. 
I ''must'' quote the quotes. I ''must'' quote the quotes. 

Err, sorry about that, I'm trying to keep something in my mind.  I just wanted to tell 
ya to forget my previous mail, I got it (tipical) just after sending it:

CREATE OR REPLACE FUNCTION "verticat" (text, numeric) RETURNS text AS '
SELECT CASE WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL OR $1 =  
THEN ltrim('' '', to_char($2,''9D99''))
ELSE $1 || '', '' || ltrim('' '',  to_char($2,''D99''))
   END
' LANGUAGE 'sql';

Ciao.

I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. 
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. 
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. 
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. 
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. 
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. 
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. 
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the 
quotes...


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])