--- In firebird-support@yahoogroups.com, Robert martin <rob@c...>
wrote:

>
> Hi Adam
>
> In the following....
>
> select 'Hello' || '
> ' || 'World'
> from RDB$DATABASE;
>
> I assume there is a CRLF between lines 1 and 2. How do I enter

this into my editor?

>
>
> Your comment about creating a stored procedure is interesting as it

is a stored procedure that I want to return (as one of its results)
said CRLF separated string. Could you please give me an example?

I just open a quote, hit enter, then close the quote

like this '
'

Just a little trick.

>
> My SP currently has ....
>
> CREATE PROCEDURE pr_Custom_POSlabel (ControlRef Numeric(11),

NumLabels SmallInt)

> /* RM 06/09/2005 produce labels from POS */
> RETURNS
> (
> ClientCode VarChar(20),
> ClientName VarChar(40),
> DelAddress varChar(254),
> PostAddress VarChar(254),
> LabelNo Integer,
> TotBoxes Integer,
> IsWholeSale Char(1)
> )
> AS
> BEGIN
> SELECT c.ClientCode, c.ClientName, cd.Local5Flag, a.AddressTxt

|| COALESCE(a.PostCode, ''), DelAddress.AddressTxt || COALESCE
(DelAddress.PostCode, '')

> ......
>
> Obviously I would like the postcodes to be on the following line

(addressTxt is a formatted field).

I assume you don't want the extra carriage return if the postcode is
null. Because crlf || null is still null, the following will achieve
that.


SELECT c.ClientCode, c.ClientName, cd.Local5Flag, a.AddressTxt ||
COALESCE(a.PostCode, ''), DelAddress.AddressTxt || COALESCE('
' || DelAddress.PostCode, '')

If you wanted the additional crlf even if the postcode is null, then

SELECT c.ClientCode, c.ClientName, cd.Local5Flag, a.AddressTxt ||
COALESCE(a.PostCode, ''), DelAddress.AddressTxt || '
' || COALESCE(DelAddress.PostCode, '')

Hope that helps

Adam

>
> TIA
> Rob
>
>
> Rob Martin
> Software Engineer
>
> phone +64 03 377 0495
> fax +64 03 377 0496
> web www.chreos.com
>
> Wild Software Ltd
>
>
>
> Adam wrote:
>
> >--- In firebird-support@yahoogroups.com, Robert martin <rob@c...>

wrote:

> >
> >
> >>Hi
> >>
> >>I want to return StringA || StringB. I can do this fine but

would like

> >>a cr between the two strings so they begin on different lines

(when

> >>printed). How do I do this?
> >>
> >>
> >
> >I can imagine a number of methods, the easiest being
> >
> >select 'Hello' || '
> >' || 'World'
> >from RDB$DATABASE;
> >
> >Other methods are creating a stored procedure called crlf, (you can
> >guess what it returns).
> >
> >select 'Hello' || (select str from CRLF()) || 'World'
> >from RDB$DATABASE;
> >
> >Thirdly, you could create a UDF function called CRLF
> >
> >select 'Hello' || CRLF() || 'World'
> >from RDB$Database;
> >
> >The first way looks easiest though.
> >
> >Adam
> >
> >
> >
> >
> >
> >
> >
> >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> >Visit http://firebird.sourceforge.net and click the Resources item
> >on the main (top) menu. Try Knowledgebase and FAQ links !
> >
> >Also search the knowledgebases at http://www.ibphoenix.com
> >
> >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> >Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>