Re: Query Question

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 19 Jun 2008 22:49:34 +0200
Message-ID: <485ac65e$0$14346$e4fe514c@news.xs4all.nl>

"Shakespeare" <whatsin_at_xs4all.nl> schreef in bericht news:485ac5f6$0$14347$e4fe514c_at_news.xs4all.nl...
>
> "Mtek" <mtek_at_mtekusa.com> schreef in bericht
> news:fff31b21-f8b5-4714-bf21-274992545138_at_x41g2000hsb.googlegroups.com...
>> On Jun 19, 2:40 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>>> Mtek schrieb:
>>>
>>>
>>>
>>> > On Jun 19, 1:21 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>>> >> Mtek schrieb:
>>>
>>> >>> Hi,
>>> >>> Is it possible to return all rows from a query on one line?
>>> >>> I have this query:
>>> >>> SELECT cnt || ':' || t_id
>>> >>> FROM (SELECT COUNT(*) cnt, t_id
>>> >>> FROM zc_body zb, zc_type zt
>>> >>> WHERE zb.t_id = zt.id
>>> >>> group by t_id);
>>> >>> It returns data like this:
>>> >>> 86:3
>>> >>> 994:7
>>> >>> 99:18
>>> >>> 38:27
>>> >>> 45:36
>>> >>> However, they want it in one line: 86:3,994:7,99:18,38:27,45:36
>>> >>> Can this be done?
>>> >>> Thanks!
>>> >>> John
>>> >> SQL> with t as (
>>> >> 2 select '86:3' cnt_t_id from dual union all
>>> >> 3 select '994:7' from dual union all
>>> >> 4 select '99:18' from dual union all
>>> >> 5 select '38:27' from dual union all
>>> >> 6 select '45:36' from dual
>>> >> 7 )
>>> >> 8 -- End test data
>>> >> 9 select wm_concat(cnt_t_id)
>>> >> 10 from t
>>> >> 11 ;
>>>
>>> >> WM_CONCAT(CNT_T_ID)
>>> >> --------------------------------------------------
>>> >> 86:3,994:7,99:18,38:27,45:36
>>>
>>> >> or search on asktom for "stragg"
>>>
>>> >> Best regards
>>>
>>> >> Maxim
>>>
>>> > Trying to work with your idea. Still gives everything on a separate
>>> > line. Although, I do not understand fully thw 'with' query. Got to
>>> > read on that......
>>>
>>> > with t as (
>>> > SELECT cnt || ':' || t_id cnt_t_id
>>> > FROM (SELECT COUNT(*) cnt, t_id
>>> > FROM zc_body zb, zc_type zt
>>> > WHERE zb.t_id = zt.id
>>> > group by t_id))
>>> > select cnt_t_id from t;
>>>
>>> > Also, I do not think WM_CONCAT is available in Oracle 10g??
>>>
>>> > John
>>>
>>> SQL> select * from v$version where rownum<=1;
>>>
>>> BANNER
>>> ----------------------------------------------------------------
>>> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
>>>
>>> SQL> desc wm_concat
>>> FUNCTION wm_concat RETURNS VARCHAR2
>>> Argument Name Type In/Out Default?
>>> ------------------------------ ----------------------- ------ --------
>>> P1 VARCHAR2 IN
>>>
>>> "With" is not relevant to your question, i used it only to get the test
>>> data to demonstrate the query, simply put it is equivalent to an inline
>>> view which can be referenced as table, anyway, i appreciate your
>>> intention to read on that.
>>>
>>> Best regards
>>>
>>> Maxim
>>
>>
>> Strange,
>>
>> SQL> select * from v$version where rownum<=1;
>>
>> BANNER
>> ----------------------------------------------------------------
>> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
>>
>> Elapsed: 00:00:00.02
>> SQL> desc wm_concat
>> ERROR:
>> ORA-04043: object wm_concat does not exist
>>
>>
>> SQL> select owner from dba_objects where lower(object_name) =
>> 'wm_concat';
>>
>> no rows selected
>>
>> Weird......
>>
>> John
>>
>
> You need to have ultrasearch installed. WM_CONCAT is owned by WMSYS
> (ultrasearch schema)
>
> Shakespeare
>

Sorry, should be workspace manager, which explains the WM ;-)

Shakespeare Received on Thu Jun 19 2008 - 15:49:34 CDT

Original text of this message