MySQL: Using REPLACE as part of an IN statement

Question

I am trying to use REPLACE to substitute spaces for commas.

If I try SELECT REPLACE('1 2 3 4 5 6 7 86 9', ' ', ', '); then I get exactly what I need in a string.

However, if I try this as part of an IN statement I only get returned the first match (ie, 1)

Here is my entire query:

SELECT aa.category_id, 
       aa.yyyymm, 
       aa.cat_balance_ytd 
  FROM gl_view_cat_balances AS aa 
 WHERE aa.gl_id = '/JOB//9' 
   AND aa.fin_years_ago = 0 
 **AND aa.category_id IN (REPLACE((SELECT detail_2 
                                     FROM gl_options 
                                    WHERE option_id = 'GLREPFUNCJOB01'),' ', ', '))** 
   AND aa.yyyymm = (SELECT max(bb.yyyymm) 
                      FROM gl_rep_cat_bals as bb 
                     WHERE bb.gl_unique_id = aa.gl_unique_id 
                       AND bb.category_id = aa.category_id 
                       AND bb.yyyymm <= 200910);

Field detail_2 in record GLREPFUNCJOB01 contains '1 2 3 4 5 6 7 86 9'

If anyone has some helpful hints on how I can get the commas into the string and can use it in the IN I would love to hear about them.

Answer 1

Gosh, I can't find anything wrong. After fruitlessly desk checking, I created the minimal schema needed and it executes okay, though the tables are empty. Maybe you could show a few records of data?

SELECT aa.category_id, aa.yyyymm, aa.cat_balance_ytd
FROM gl_view_cat_balances AS aa
WHERE aa.gl_id = '/JOB//9' AND
      aa.fin_years_ago = 0 AND
      aa.category_id IN (REPLACE((SELECT detail_2
                                   FROM gl_options
                                   WHERE option_id = 'GLREPFUNCJOB01'),' ', ', ')) AND
      aa.yyyymm = (SELECT max(bb.yyyymm)
                    FROM gl_rep_cat_bals as bb
                    WHERE bb.gl_unique_id = aa.gl_unique_id AND
                          bb.category_id = aa.category_id AND
                          bb.yyyymm <= 200910);

Answer 2

You can't use REPLACE to create a comma delimited list for use in an IN clause. To use that as-is, you'd have to utilize MySQL's Prepared Statements (effectively dynamic SQL) - creating the comma separated list first, and inserting that into the SQL query constructed as a string before executing it.

SELECT a.category_id, 
       a.yyyymm, 
       a.cat_balance_ytd 
  FROM GL_VIEW_CAT_BALANCES a
  JOIN GL_OPTIONS o ON INSTR(o.detail2, a.category_id)
                   AND o.option_id = 'GLREPFUNCJOB01'
 JOIN (SELECT b.category_id,
              b.gl_unique_id,
              MAX(b.yyyymm) 'yyyymm'
         FROM GL_REPCAT_BALSs b 
        WHERE b.yyyymm <= 200910
     GROUP BY b.category_id, b.gl_unique_id) x ON x.category_id = a.category_id
                                              AND x.gl_unique_id = a.unique_id
                                              AND x.yyyymm = a.yyyymm
 WHERE a.gl_id = '/JOB//9' 
   AND a.fin_years_ago = 0

Here's an untested, possible non-dynamic SQL alternative, using FIND_IN_SET:

SELECT a.category_id, 
       a.yyyymm, 
       a.cat_balance_ytd 
  FROM GL_VIEW_CAT_BALANCES a
  JOIN (SELECT REPLACE(o.detail_2, ' ', ', ') 'detail2_csv'
          FROM GL_OPTIONS o
         WHERE o.option_id = 'GLREPFUNCJOB01') y ON FIND_IN_SET(a.category, y.detail2_csv) > 0
  JOIN (SELECT b.category_id,
               b.gl_unique_id,
               MAX(b.yyyymm) 'yyyymm'
          FROM GL_REPCAT_BALSs b 
         WHERE b.yyyymm <= 200910
      GROUP BY b.category_id, b.gl_unique_id) x ON x.category_id = a.category_id
                                               AND x.gl_unique_id = a.unique_id
                                               AND x.yyyymm = a.yyyymm
 WHERE a.gl_id = '/JOB//9' 
   AND a.fin_years_ago = 0

Answer 3

Thanks for your help, but we ended up creating a temporary table out of the values in glrepfuncjob01 and using that as the sub-select in the IN statement.

Worked a treat.

Many thanks,

Brendon