Re: Does CHR(10) add an extra newline?

On Thu, 22 Nov 2007 04:32:22 -0800 (PST), Frank van Bortel <frank.van.bortel_at_gmail.com> wrote:

>On 21 nov, 20:53, Brian Tkatch <N/A> wrote:
>> On Wed, 21 Nov 2007 11:40:24 -0800 (PST), "fitzjarr..._at_cox.net"
<SNIP>
>You normally would not create views "on the fly"...

True. But i don't see what that has to do with anything. I did not mean to sound like i was complaining how i want something to work. I am asking why it works the way that it does.

>Apart from that - there's no need for carriage returns in DDL,

There is, if i want it to look pretty. :)

Actually, i despise redundancy, and enjoy an elegant solution. The extra CR just plain bothers me, that is, if i know there is a way to avoid it.

>but if you really want them, just embed the carriage return in the
>statement:
>
>SQL> select 'hello
> 2 world' from dual;
>
>'HELLOWORLD'
>------------
>hello
> world

I never even thought of doing that. Thanx!

>
>And yes- there's an extra space before Hello on line 2.

Heh, i used CHAR(9). :)

Perhaps i should just post the query i used. I'll never learn if i'm too embarrassed to be shown my own mistakes. The TABLE names all start with a four character prefix, a letter or number, two numbers, and an underscore. The column names are prefixed similarly, minus the very first character. It also has a SYNONYM with the number pointing to it, for compliance with the numbered system.

WITH
        Info
AS

	(
	 SELECT
		Owner,
		Table_Name,
		MAX(Column_Id)				Total_Columns,
		MAX(FLOOR(LENGTH(Column_Name) / 8))	Tabs
	 FROM
		All_Tab_Columns
	 GROUP BY
		Owner,
		Table_Name
	)
SELECT
	SUBSTR
	(
	 CASE
		 WHEN Columns.Column_Id = 1 THEN
		'CREATE OR REPLACE VIEW ' ||
INITCAP(SUBSTR(Columns.Table_Name, 9))
		|| CHR(10) || 'AS'
		|| CHR(10) || ' SELECT'
		|| CHR(10)
	 END
	 || CHR(09) || Columns.Column_Name
	 || RPAD(CHR(09), (Tabs - FLOOR(LENGTH(Column_Name) / 8)) + 1,
CHR(09))
	 || INITCAP(SUBSTR(Columns.Column_Name, 8,
LENGTH(Columns.Column_Name) - 9))
	 || CASE
		WHEN Columns.Column_Id = Info.Total_Columns THEN
			   CHR(10) || 'FROM'
			|| CHR(10) || CHR(09) || Columns.Table_Name ||
';'
			|| CHR(10) || CHR(10)
			|| 'CREATE OR REPLACE SYNONYM V'
			|| SUBSTR(Columns.Table_Name, 5, 3)
			|| ' FOR ' ||
INITCAP(SUBSTR(Columns.Table_Name, 9)) || ';'
		ELSE ','
	    END,
	 1,
	 150
	) Statement
FROM
	All_Tab_Columns	Columns,
	Info
WHERE
	Columns.Owner		= 'xxx'
  AND	Columns.Table_Name	LIKE 'xxxxx%'
  AND	Columns.Owner		= Info.Owner
  AND	Columns.Table_Name	= Info.Table_Name
ORDER BY
	Columns.Table_Name,
	Columns.Column_Id;

B. Received on Mon Nov 26 2007 - 08:21:29 CST