Hi guys,

Here is the query that is running in production

SELECT HEX(adjust_code) AS adjust_code, desc, pc.start_date, pc.end_date, promo_mul FROM adjust a

LEFT JOIN promo_config pc ON a.adjust_code = pc.promo_id

LEFT JOIN promo_cashin_rel pcr ON a.adjust_code = pcr.promo_id

WHERE promo_type=2 AND state = 'A' ORDER BY desc



20070117161336388862000000 AllGroupAllSegment 17/01/2007 12:00:00 AM 19/01/2007 12:00:00 AM 2


20090917150744078202000000 Demo Thursday 01/09/2009 12:00:00 AM 30/09/2009 12:00:00 AM 20


20070109212723188801000000 DescriptionW9 01/01/2004 12:00:00 AM 02/01/2007 12:00:00 AM 0


20070110142350691378000000 DescriptionWQ 01/01/2007 12:00:00 AM 10/01/2007 12:00:00 AM 0





I need to add the casinos each promotion is associated with but as a single column.

The casinos information is held in another table you can retrieve the casinos for a promotion like this:



SELECT * FROM promo_casino fetch first 10 rows only

ID CASINO_ID
----------------------- ---------
0128214355728171000000' GP
0128214355729465000000' GP
0128214355730665000000' GP
0128214355731928000000' GP
0128214355733124000000' GP
0128214355734323000000' GP
0128214355735549000000' GP
0226220537497097000000' GP
0226220537498479000000' GP
0226220537499773000000' GP

10 record(s) selected.

What I need to do is create an additional column in the original report that displays the associated casinos.

Here is a modified display of the original results (as needed):



20070117161336388862000000 AllGroupAllSegment 17/01/2007 12:00:00 AM 19/01/2007 12:00:00 AM 2 AB,HG,ZD,

20090917150744078202000000 Demo Thursday 01/09/2009 12:00:00 AM 30/09/2009 12:00:00 AM 20 BH,

20070109212723188801000000 DescriptionW9 01/01/2004 12:00:00 AM 02/01/2007 12:00:00 AM 0 CG,BH,

20070110142350691378000000 DescriptionWQ 01/01/2007 12:00:00 AM 10/01/2007 12:00:00 AM 0 CG,BH,KL,



I am assuming this is possible, trying to get to that but no success yet.

DBFinder

You have 2 options. You could write the query as a recursive query, or you could write a UDF that will do the work for you.

Andy

Thanks,

Let me start writing UDF for now, I might need some help with recursive query. I think I should evaluate the cost for both and choose the lower cost one.

BTW, our programmers usually like recursive queries.

DBFinder

Or, you can write the query with XMLAGG to concatenate strings in multiple rows.

Can some one give me recursive SQL code for this.

Thanks
DBFinder

Search for string aggregation because that's what you need. It is very frequently asked, so you'll find lots of examples.

Stolze,
Thanks for the clue. For now our developers has accepted the function. I will study futher as per your clue.

Thanks

For performance reasons, I would be rather careful with such a procedural function. Relational database systems are very good at set processing. Such a UDF breaks the set processing due to its procedural nature. But that really depends on your requirements and if performance is that critical.