Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: SQL help needed.

    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
    Last edited by DBFinder; 10-20-09 at 08:46.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You could write the query as a recursive query
    Or, you can write the query with XMLAGG to concatenate strings in multiple rows.

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Code:
    --------------------------------------------------------
     -- This UDF takes in promo_id and returns CasinoList 
    --------------------------------------------------------
    DROP FUNCTION DB2ADMIN.GetCasinoList@
    
    CREATE FUNCTION DB2ADMIN.GetCasinoList(promoid Varchar(13))
      RETURNS varchar(20)
      SPECIFIC GetCasinoList
      LANGUAGE SQL
    BEGIN ATOMIC
    
        DECLARE  Casinolist varchar(20) default '';
    
    For cur as SELECT casino_id FROM promo_casino WHERE promo_id =promoid 
      do
    
       Set Casinolist = CasinoList CONCAT cur.casino_id;
    	
    END for;
       
       RETURN VALUES (Casinolist);
      END
    @

    Can some one give me recursive SQL code for this.

    Thanks
    DBFinder

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Search for string aggregation because that's what you need. It is very frequently asked, so you'll find lots of examples.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Stolze,
    Thanks for the clue. For now our developers has accepted the function. I will study futher as per your clue.

    Thanks

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •