Results 1 to 2 of 2
  1. #1
    Join Date
    May 2006
    Posts
    6

    Unanswered: Query GROUP_CONCAT issue

    Need help in query group_concat issue, Here is my query
    Code:
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(ps.name = ''',
          name,
          ''', ch.state, 0)) AS ',
          name,
          
          
          ',MAX(IF(ps.name = ''',
          name,
          ''', ch.data, NULL)) AS ',
          name,'_data', '_time'
    		
    		
        )
      )
    FROM phases where phase_id > 7
    It returns me

    Code:
    MAX(IF(ps.name = 'logistics_receiving', ch.state, 0)) AS logistics_receiving,MAX(IF(ps.name = 'logistics_receiving', ch.data, NULL)) AS logistics_receiving_data_time,MAX(IF(ps.name = 'logistics_buying', ch.state, 0)) AS logistics_buying,MAX(IF(ps.name = 'logistics_buying', ch.data, NULL)) AS logistics_buying_data_time,MAX(IF(ps.name = 'logistics_sku_generation', ch.state, 0)) AS logistics_sku_generation,MAX(IF(ps.name = 'logistics_sku_generation', ch.data, NULL)) AS logistics_sku_generation_data_time,MAX(IF(ps.name = 'logistics_quality_control', ch.state, 0)) AS logistics_quality_control,MAX(IF(ps.name = 'logistics_quality_control', ch.data, NULL)) AS logistics_quality_control_data_time,MAX(IF(ps.name = 'logistics_sorting', ch.state, 0)) AS logistics_sorting,MAX(IF(ps.name = 'logistics_sorting', ch.data, NULL)) AS logistics_sorting_data_time
    I want it to return me:
    MAX(IF(ps.name = 'logistics_receiving', ch.state, 0)) AS logistics_receiving,
    MAX(IF(ps.name = 'logistics_receiving', ch.data, NULL)) AS logistics_receiving_data,
    MAX(IF(ps.name = 'logistics_receiving', IF(ch.state = 2,DATEDIFF(ch.time_stamp_last_updated,c.time_stamp _in),NULL),NULL)) AS logistics_receiving_completion_time,

    MAX(IF(ps.name = 'logistics_buying', ch.state, 0)) AS logistics_buying,
    MAX(IF(ps.name = 'logistics_buying', ch.data, NULL)) AS logistics_buying_data,
    MAX(IF(ps.name = 'logistics_buying', IF(ch.state = 2,DATEDIFF(ch.time_stamp_last_updated,c.time_stamp _in),NULL),NULL)) AS logistics_buying_completion_time,

    MAX(IF(ps.name = 'logistics_sku_generation', ch.state, 0)) AS logistics_sku_generation,
    MAX(IF(ps.name = 'logistics_sku_generation', ch.data, NULL)) AS logistics_sku_generation_data,
    MAX(IF(ps.name = 'logistics_quality_control', IF(ch.state = 2,DATEDIFF(ch.time_stamp_last_updated,c.time_stamp _in),NULL),NULL)) AS logistics_quality_control_time,

    MAX(IF(ps.name = 'logistics_quality_control', ch.state, 0)) AS logistics_quality_control,
    MAX(IF(ps.name = 'logistics_quality_control', ch.data, NULL)) AS logistics_quality_control_data,
    MAX(IF(ps.name = 'logistics_sorting', IF(ch.state = 2,DATEDIFF(ch.time_stamp_last_updated,c.time_stamp _in),NULL),NULL)) AS logistics_sorting_time,

    MAX(IF(ps.name = 'logistics_sorting', ch.state, 0)) AS logistics_sorting,
    MAX(IF(ps.name = 'logistics_sorting', ch.data, NULL)) AS logistics_sorting_data
    MAX(IF(ps.name = 'logistics_sorting', IF(ch.state = 2,DATEDIFF(ch.time_stamp_last_updated,c.time_stamp _in),NULL),NULL)) AS logistics_sorting_time,


    Need help please

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Firstly if you look at the code you have specified above:

    MAX(IF(ps.name = 'logistics_sorting', ch.state, 0)) AS logistics_sorting,

    Why bother having a MAX there is only a single value returned either ch.state or 0. Change this first and retry this will simplify your overall code.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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