Home » SQL & PL/SQL » SQL & PL/SQL » How to sort within concatenated columns (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit)
How to sort within concatenated columns [message #620365] Thu, 31 July 2014 14:04 Go to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Hi all,

Hopefully someone can help me here...

The below SQL produces 1 long concatenated result, and when exported to EXCEL, it formats it there correctly. When the SQL reads from the database, the trim(plv.line_num) is read...10, 9, 8, 7, etc....

I would like to sort trim(plv.line_num) if possible to be 1, 2, 3, 4, etc....

By looking at the Sample incorrect Output below, would any of you know how I can get the sort working on the line number from the smallest to largest? I've tried a few different things, but no luck because of all the concatenated fields.

Thanks,
Joe

select    'Item Section'               ||  chr(09)       ||
          'Action'                     ||  chr(09)       ||
          'Line Number'                ||  chr(09)       ||
          'Line Type'                  ||  chr(09)       ||
          'Thumbnail Image'            ||  chr(09)       ||
          'Image'                      ||  chr(09)       ||
          'Description'                ||  chr(09)
                    from dual
UNION ALL
select
          ''                               ||  chr(09)   ||
          'SYNC'                           ||  chr(09)   ||
          trim(plv.line_num)               ||  chr(09)   ||
          ''                               ||  chr(09)   ||
          icav.thumbnail_image             ||  chr(09)   ||
          icav.picture                     ||  chr(09)   ||
          'Item Image'                     ||  chr(09) 
      FROM   apps.po_headers_v phv,
             apps.po_lines_v plv,
             APPS.mtl_categories_b_kfv mc,
             icx.ICX_CAT_ATTRIBUTE_VALUES icav
 WHERE plv.po_header_id                 = phv.po_header_id
  and mc.category_id                    = plv.category_id
  and icav.po_line_id(+)                = plv.po_line_id
  and phv.segment1                      =  '1328700';



Sample Incorrect Output with Line Number sorted incorrectly:

'ITEMSECTION'||CHR(09)||'ACTION'||CHR(09)||'LINENUMBER'||CHR(09)||'LINETYPE'||CHR(09)||'THUMBNAILIMAGE'||CHR(09)||'IMAGE'||CHR(09)||'DESCRIPTION'||CHR(09)										
Item Section	Action	Line Number	Line Type	Thumbnail Image	Image	Description				
	SYNC	10				Item Image				
	SYNC	9				Item Image				
	SYNC	8				Item Image				
	SYNC	7				Item Image				
	SYNC	6				Item Image				
	SYNC	5				Item Image				
	SYNC	4				Item Image				
	SYNC	3				Item Image				
	SYNC	2				Item Image				
	SYNC	1				Item Image



Desired Output with the sort working CORRECTLY:

'ITEMSECTION'||CHR(09)||'ACTION'||CHR(09)||'LINENUMBER'||CHR(09)||'LINETYPE'||CHR(09)||'THUMBNAILIMAGE'||CHR(09)||'IMAGE'||CHR(09)||'DESCRIPTION'||CHR(09)										
Item Section	Action	Line Number	Line Type	Thumbnail Image	Image	Description				
	SYNC	1				Item Image				
	SYNC	2				Item Image				
	SYNC	3				Item Image				
	SYNC	4				Item Image				
	SYNC	5				Item Image				
	SYNC	6				Item Image				
	SYNC	7				Item Image				
	SYNC	8				Item Image				
	SYNC	9				Item Image				
	SYNC	10				Item Image

Report message to a moderator

Re: How to sort within concatenated columns [message #620366 is a reply to message #620365] Thu, 31 July 2014 14:21 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

I've tried a few different things, but no luck

What did you try?

The first thing I can think of (which might be incorrect, though) is
order by to_number(trim(plv.line_num))

Report message to a moderator

Re: How to sort within concatenated columns [message #620368 is a reply to message #620366] Thu, 31 July 2014 14:31 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Littlefoot.... Yes, that was one of the things I tried as well and got this error:

ORA-01785: ORDER BY item must be the number of a SELECT-list expression
01785. 00000 -  "ORDER BY item must be the number of a SELECT-list expression"


I also tried to do an order by, using the whole select clause. N/G.

Report message to a moderator

Re: How to sort within concatenated columns [message #620370 is a reply to message #620365] Thu, 31 July 2014 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/193746/619699/#msg_619699 and following.

Report message to a moderator

Re: How to sort within concatenated columns [message #620372 is a reply to message #620370] Thu, 31 July 2014 14:45 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Aha. ORA-01785.

How about SQL*Plus NOPRINT option?

SQL> col id_noprint noprint
SQL>
SQL> with test as
  2    (select 'SYNC' item_section, '10' action from dual union all
  3     select 'SYNC', '3' from dual union all
  4     select 'SYNC', '7' from dual
  5    )
  6  select -1 id_noprint,
  7            'Item Section'               ||  chr(09)       ||
  8            'Action'                     ||  chr(09)       ||
  9            'Line Number'
 10  from dual
 11  union
 12  select to_number(trim(t.action)) id_noprint,
 13         ''           || chr(09) ||
 14         item_section || chr(09) ||
 15        trim(action) || chr(09)
 16  from test t
 17  order by id_noprint;

'ITEMSECTION'||CHR(09)||'ACTION
-------------------------------
Item Section    Action  Line Number
        SYNC    3
        SYNC    7
        SYNC    10

SQL>

Report message to a moderator

Re: How to sort within concatenated columns [message #620377 is a reply to message #620372] Thu, 31 July 2014 15:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
How about SQL*Plus NOPRINT option?


Yes, this was the purpose of my link. Wink

Report message to a moderator

Re: How to sort within concatenated columns [message #620379 is a reply to message #620377] Thu, 31 July 2014 15:23 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I wish I read it ... /forum/fa/1604/0/ Sorry.

Report message to a moderator

Re: How to sort within concatenated columns [message #620380 is a reply to message #620379] Thu, 31 July 2014 15:29 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
You could have also done it like this

select    'Item Section'               ||  chr(09)       ||
          'Action'                     ||  chr(09)       ||
          'Line Number'                ||  chr(09)       ||
          'Line Type'                  ||  chr(09)       ||
          'Thumbnail Image'            ||  chr(09)       ||
          'Image'                      ||  chr(09)       ||
          'Description'                ||  chr(09) line
                    from dual
UNION ALL
select line 
from 
(select
          ''                               ||  chr(09)   ||
          'SYNC'                           ||  chr(09)   ||
          trim(plv.line_num)               ||  chr(09)   ||
          ''                               ||  chr(09)   ||
          icav.thumbnail_image             ||  chr(09)   ||
          icav.picture                     ||  chr(09)   ||
          'Item Image'                     ||  chr(09)  line
      FROM   apps.po_headers_v phv,
             apps.po_lines_v plv,
             APPS.mtl_categories_b_kfv mc,
             icx.ICX_CAT_ATTRIBUTE_VALUES icav
 WHERE plv.po_header_id                 = phv.po_header_id
  and mc.category_id                    = plv.category_id
  and icav.po_line_id(+)                = plv.po_line_id
  and phv.segment1                      =  '1328700'
order by to_number(trim(plv.line_num)));

Report message to a moderator

Re: How to sort within concatenated columns [message #620383 is a reply to message #620365] Thu, 31 July 2014 15:52 Go to previous messageGo to next message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Hi all,

Thanks for all of your suggestions. When I get back to the office tomorrow, I will give them a try.

I'll update you then.

Have a great rest of the day,
Joe

Report message to a moderator

Re: How to sort within concatenated columns [message #620516 is a reply to message #620383] Sun, 03 August 2014 18:03 Go to previous message
rappaj
Messages: 147
Registered: June 2010
Location: NYC
Senior Member
Mike, Littlefoot and Bill.

Both of the ways that you guys suggested work great and thanks for the help. You guys are good !

Joe

Report message to a moderator

Previous Topic: Dynamic Column Generation by using pivot
Next Topic: Avoid Exception
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 17:22:27 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.