mySQL Group_Concat and Case when query gives error-CodePudding

I am getting the error "Incorrect parameters in the call to native function 'CONCAT': on the query below:

SELECT
  *,
  GROUP_CONCAT(
    CASE
      WHEN `REASONORINSTRUCTIONCODE` = 'R'
      THEN CONCAT(
        "name-",
        `USERWHOENTEREDTHISLINE`,
        ",reason-",
        RTRIM(
          `REASONSORSHIPPINGINSTRUCTIONS`
        )
      END,
      ", "
    )
  ) AS reason,
  GROUP_CONCAT(
    CASE
      WHEN [ `REASONORINSTRUCTIONCODE` ] = 'S'
      THEN CONCAT(
        "name-",
        `USERWHOENTEREDTHISLINE`,
        ",shipping instruction-",
        RTRIM(
          `REASONSORSHIPPINGINSTRUCTIONS`
        )
      END,
      ", "
    )
  ) AS shipping instruction
FROM
  TABLE
GROUP BY `PICKUP_NO`

CodePudding user response：

You have several issues with your query. First, you're not closing your CONCAT with an end ), next your AS shipping instruction cannot contain a space. Next, you have [REASONORINSTRUCTIONCODE], remove the []

Take a look at the formatted query below:

SELECT
  *,
  GROUP_CONCAT(
    CASE
      WHEN `REASONORINSTRUCTIONCODE` = 'R'
      THEN CONCAT(
        "name-",
        `USERWHOENTEREDTHISLINE`,
        ",reason-",
        RTRIM(
          `REASONSORSHIPPINGINSTRUCTIONS`
        ))
      END,
      ", "
    )
   AS reason,
  GROUP_CONCAT(
    CASE
      WHEN `REASONORINSTRUCTIONCODE` = 'S'
      THEN CONCAT(
        "name-",
        `USERWHOENTEREDTHISLINE`,
        ",shipping instruction-",
        RTRIM(
          `REASONSORSHIPPINGINSTRUCTIONS`
        ))
      END,
      ", "
    )
   AS shipping_instruction
FROM
  `TABLE`
GROUP BY `PICKUP_NO`