Re: Query Question

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 19 Jun 2008 22:47:49 +0200
Message-ID: <485ac5f6$0$14347$e4fe514c@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 Received on Thu Jun 19 2008 - 15:47:49 CDT

Original text of this message