Home » SQL & PL/SQL » SQL & PL/SQL » break output line at certain point
break output line at certain point [message #265521] Thu, 06 September 2007 10:00 Go to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member
Hi Guys,

Could someone please help me to provide
work around for the following desired output?

SELECT 'A1' REC_TYPE ,'A','B','C', 'A2' REC_TYPE, 'D','E','F','G','H','A3' REC_TYPE, 'I','J' FROM DUAL

   
   RE ' ' ' RE ' ' ' ' ' RE ' ' 
   -- - - - -- - - - - - -- - - 
   A1 A B C A2 D E F G H A3 I J 
   

   DESIRED OUTPUT LOOKS LIKE THIS 
   
   A1 A B C
   A2 D E F G H
   A3 I J 
   
  basically want to break output line at certain point which is "REC_TYPE".
  so wherever "REC_TYPE" field comes need a break and start new line.



Thanking you all



Re: break output line at certain point [message #265527 is a reply to message #265521] Thu, 06 September 2007 10:17 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What are you actually trying to achieve?

According to your requirement this here works :

SQL> SELECT 'A1' REC_TYPE ,'A','B','C', '', '' from dual
  2  union
  3  select 'A2' REC_TYPE, 'D','E','F','G','H' from dual
  4  union
  5  select 'A3' REC_TYPE, 'I','J','', '' , '' FROM DUAL;

RE ' ' ' ' '
-- - - - - -
A1 A B C
A2 D E F G H
A3 I J


Re: break output line at certain point [message #265528 is a reply to message #265521] Thu, 06 September 2007 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> set head off
SQL> col rec_type fold_before
SQL> SELECT 'A1' REC_TYPE ,'A','B','C', 'A2' REC_TYPE, 'D','E','F','G','H','A3' REC_TYPE, 'I','J' FROM DUAL
  2  /
A1 A B C
A2 D E F G H
A3 I J

1 row selected.

Regards
Michel
Re: break output line at certain point [message #265530 is a reply to message #265528] Thu, 06 September 2007 10:28 Go to previous messageGo to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member
Thanks a lot guys!

I was trying same way but was facing this :

SQL> SELECT * FROM  ( SELECT 'A1' REC_TYPE ,'A','B','C' "REC_TYPE1" FROM DUAL) UNION
  2                 (SELECT  'A2' REC_TYPE, 'D','E','F' "REC_TYPE2" FROM DUAL) UNION
  3                 (SELECT  'A3' REC_TYPE, 'I','J','K' "REC_TYPE3" FROM DUAL)
  4  /

RE ' ' R
-- - - -
A1 A B C
A2 D E F
A3 I J K

SQL> ED
Wrote file afiedt.buf

  1  SELECT * FROM  ( SELECT 'A1' REC_TYPE ,'A','B','C' "REC_TYPE1" FROM DUAL) UNION
  2                 (SELECT  'A2' REC_TYPE, 'D','E','F','I','H' "REC_TYPE2" FROM DUAL) UNION
  3*                (SELECT  'A3' REC_TYPE, 'I','J','K' "REC_TYPE3" FROM DUAL)
SQL> /
               (SELECT  'A2' REC_TYPE, 'D','E','F','I','H' "REC_TYPE2" FROM DUAL) UNION
                *
ERROR at line 2:
ORA-01789: query block has incorrect number of result columns


SQL>



I was doing wrong. Now I know from your reply how to deal with.

Well Thank you all again!

[Updated on: Thu, 06 September 2007 11:03]

Report message to a moderator

Re: break output line at certain point [message #265698 is a reply to message #265528] Fri, 07 September 2007 01:26 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Thu, 06 September 2007 17:17
SQL> col rec_type fold_before

Hey, I didn't know that!

Another lesson learnt.

MHE
Re: break output line at certain point [message #265707 is a reply to message #265698] Fri, 07 September 2007 01:58 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are also fold_after and newline (synonym for fold_before).

Regards
Michel
Previous Topic: identifying gaps (merged)
Next Topic: MATERIALIZED VIEW PROBLEM
Goto Forum:
  


Current Time: Tue Dec 06 16:03:39 CST 2016

Total time taken to generate the page: 0.11828 seconds