Re: Return from function forces CR/LF ???

"Bill Rowland" <billrow_at_onemain.com> wrote in message news:<fjN7a.674$wv7.384_at_news.cpqcorp.net>...
> Our local SQL expert wrote a function to return a Y/N flag. He uses it to
> set a value and
> update records, which works fine. I am trying to call the function from
> SQL*PLUS to
> output the returned flag value to a file. The returned value seems to force
> a CR/LF and
> generate a new record, rather than just being included in the current record
> with the
> defined field separator. (I am also do other SETs from an include file which
> specify
> page length, record length, etc., so it isn't a problem with wrapping
> because of
> record length.)
>
> Example:
>
> SET COLSEP '|';
> select product_number, part_number,
> (spi.is_orderable(part_number)) as order_flag
> from structure;
>
> The result is like:
>
> 33440A|33440-69001
> Y
>
> 33440A|33440-69003
> N
>
>
> rather than the following, which is what I want:
>
> 33440A|33440-69001|Y
> 33440A|33440-69003|N
>
> Of course, the SQL expert is on vacation, leaving me to figure this out.
> I'm not including all of the function code here, but it just returns a
> varchar2 (1)
> field with either 'Y' or 'N'.
>
> Any ideas how I can suppress the CR/LF in the output???
>
> Thanks,
>
> Bill Rowland

You can use SUBSTR around function call. like,

select product_number, part_number,

         SUBSTR(spi.is_orderable(part_number),1,1) as order_flag from structure;

Regards
/Rauf Sarwar Received on Sat Mar 01 2003 - 11:24:03 CST