Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

Tip of the Day
Language: SQL Server
Expertise: Beginner
Nov 30, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Forcing a Linefeed in SQL

How do I force a linefeed character using SQL? I created a report (see below) but when I e-mailed this report from Unix the carriage return was not interpreted correctly.

I want the results to look like the following:

Invoice Number: 123456
Order Number: 444422222
Customer Number: abc123

When I e-mail this file using elm (to Microsoft Outlook or cc:Mail), I get the following :

Invoice Number: 123456 Order Number:44422222 Customer Number: abc123

The line break is not recognized. Any thoughts??

Here's the code:

set verify off
set echo off
set feedback off
set linesize 30
set space 0
set heading off

column invoice_num format  fold_after 1
column sales_ord_num format  fold_after 1
column rapid_txn_id format  fold_after 1
column launch_dt format  fold_after 1
column inv_dt format  fold_after 1

spool &1

select  'Invoice Number    :  &1' invoice_num,
        'Sales Order Number:  ', sales_ord_num,
        'RAPID Txn Number  :  ',  oh.mepr rapid_txn_id,
        'Order Date        :  ', to_char(oh.timestamp,'MM/DD/YY') launch_dt,
        'Ship Date         :  ', to_char(inv_dt,'MM/DD/YY') inv_dt
from    sales_ord so, inv_hdr inv, ord_hdr oh
where   so.sales_ord_id = inv.sales_ord_id

Hm, this looks like Oraclese... :)

I don't know if this will work, but you might try sending a carriage return and line feed combination—like CHAR(10)+CHAR(13)—at the end of each column name. That may do it for you.

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date