| Redundant blank line generated [message #638006] |
Mon, 01 June 2015 01:56  |
 |
vinczej
Messages: 12
Registered: June 2015
|
Junior Member |
|
|
I have this sample sql:
with p_1 as
(
select 1 sorszam, 'X1' tipus from dual
union all select 2 sorszam, 'X2' tipus from dual
union all select 3 sorszam, 'X3' tipus from dual
)
select (
(case when p1.sorszam=1 then ('[' || chr(13) || chr(10)) else '' end) ||
p1.tipus
|| (case when p1.sorszam=(select max(sorszam) from p_1) then (chr(13) || chr(10) || ']') else '' end)
) szoveg
from p_1 p1
order by p1.sorszam
;
The result is:
SZOVEG
--------
[
X1
X2
X3
]
My question is: Why is generated the blank line after the first line?
[Edit MC: add code tags]
[Updated on: Mon, 01 June 2015 02:08] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Redundant blank line generated [message #638034 is a reply to message #638033] |
Mon, 01 June 2015 10:26  |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
@OP,
could you execute and copy and paste exactly the following:
set lines 80
set trimout on
col SZOVEG OFF
with p_1 as
(
select 1 sorszam, 'X1' tipus from dual
union all select 2 sorszam, 'X2' tipus from dual
union all select 3 sorszam, 'X3' tipus from dual
)
select (
(case when p1.sorszam=1 then ('[' || chr(13) || chr(10)) else '' end) ||
p1.tipus
|| (case when p1.sorszam=(select max(sorszam) from p_1) then (chr(13) || chr(10) || ']') else '' end)
) szoveg
from p_1 p1
order by p1.sorszam
;
Then
set recsep off
with p_1 as
(
select 1 sorszam, 'X1' tipus from dual
union all select 2 sorszam, 'X2' tipus from dual
union all select 3 sorszam, 'X3' tipus from dual
)
select (
(case when p1.sorszam=1 then ('[' || chr(13) || chr(10)) else '' end) ||
p1.tipus
|| (case when p1.sorszam=(select max(sorszam) from p_1) then (chr(13) || chr(10) || ']') else '' end)
) szoveg
from p_1 p1
order by p1.sorszam
;
You should have:
SQL> set recsep wrap
SQL> set recsepchar ' '
SQL> set lines 80
SQL> set trimout on
SQL> col SZOVEG OFF
SQL> with p_1 as
2 (
3 select 1 sorszam, 'X1' tipus from dual
4 union all select 2 sorszam, 'X2' tipus from dual
5 union all select 3 sorszam, 'X3' tipus from dual
6 )
7 select (
8 (case when p1.sorszam=1 then ('[' || chr(13) || chr(10)) else '' end) ||
9 p1.tipus
10 || (case when p1.sorszam=(select max(sorszam) from p_1) then (chr(13) || chr(10) || ']') else '' end)
11 ) szoveg
12 from p_1 p1
13 order by p1.sorszam
14 ;
SZOVEG
--------
[
X1
X2
X3
]
3 rows selected.
SQL> set recsep off
SQL> with p_1 as
2 (
3 select 1 sorszam, 'X1' tipus from dual
4 union all select 2 sorszam, 'X2' tipus from dual
5 union all select 3 sorszam, 'X3' tipus from dual
6 )
7 select (
8 (case when p1.sorszam=1 then ('[' || chr(13) || chr(10)) else '' end) ||
9 p1.tipus
10 || (case when p1.sorszam=(select max(sorszam) from p_1) then (chr(13) || chr(10) || ']') else '' end)
11 ) szoveg
12 from p_1 p1
13 order by p1.sorszam
14 ;
SZOVEG
--------
[
X1
X2
X3
]
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
)