Eliminating duplicate data, possibly using decode

[hazelsisson]

Hello,
I have a query that gathers data from 3 tables. Some of the data is duplicated, like this:

TITLE ORDER NO FIRM USED
----- -------- ---------
June job 191 firm1
June job 192 firm2
(It's a similar situation to thread759-615918).

I would like the output to be something like this, so that I can show all the information in one table row:
TITLE ORDER NO FIRM USED
----- -------- ---------
June job 191, 192 firm1, firm2

Would it be possible to build this into the query somehow, possibly using the decode function as I saw in the thread I mentioned before?

I had a go myself, and got this far:

select worksheets.TITLE, 
decode(count(orders.ORDER_NO),0,'None',1,'One',2,'Two','default') orderNuumber,
decode(count(companies.NAME),0,'None',1,'One',2,'Two','default') compName
from worksheets, orders, outwork_company, companies
where worksheets.JOB_NO = orders.JOB_NO(+)
and worksheets.ARCHIVE = 'N'
and orders.OUTWORK_ID = outwork_company.OUTWORK_ID(+)
and outwork_company.used(+) = 'Y'
and outwork_company.COMPANY_ID = companies.COMPANY_ID(+)
group by worksheets.TITLE
order by worksheets.TITLE

Which produces this output:
TITLE ORDER NO FIRM USED
----- -------- ---------
June job Two Two

So now I just need to get it to display the values from the table, rather than a count.
Thanks for any ideas/advice.
Hazel

 

[nagornyi]

Is it really important to use pure SQL, not PL/SQL? Because in the latter it's done pretty simple. For example, just create 2 functions returning concatenated strings of orders and firms for given title, and then just
select distinct title, f_orders(title), f_firms(title) from worksheets

 

[hazelsisson]

That's a good idea, I'll give it a go.

I wasn't sure if I would be able to use other methods as the value needs to be searchable, i.e. people may type in a Firm to search by, and the search clause is added to the query (which is then opened in a ref cursor).

That should still work using your method shouldn't it?!
Thanks for your advice.
Hazel

 

[nagornyi]

Sure. For example if p_firm is parameter, you can do like this:

select * from
(select distinct title, f_orders(title) orders, f_firms(title) firms from worksheets)
where firms like '%&p_firm%'

 

[hazelsisson]

Thanks very much nagornyi - I got it working in the end.

For anyone that might try it in the future and has problems, I had to do these things before it worked:
-Make sure the functions are declared in the package head.
-Prefix the functions with the schema and package name in the SQL query.

Hazel