Using columns in MySQL CASE WHEN THEN Query-CodePudding

Please assist me in MySQL query. I was writing a CASE WHEN THEN in mysql query but i was not allowed to mention column name in the THEN clause. It always pointed an error at the tb1.ID and when i removed the table alias and used the direct table name, it still showed the error.

Is column names not allowed to be used in the THEN part of the CASE WHEN THEN in MySQL query?

SELECT
   CASE
  WHEN tb2.creditorUserRole = 'VND' THEN
  tb1.ID 'TB1_ID', tb1.FULLNAME 'TB1_Name'
  WHEN tb2.creditorUserRole = 'AG' THEN
  tb3.ID 'TB3_ID', tb3.FULLNAME 'TB3_FULLNAME'
  ELSE ""
END
   tb2.CreditedAmount AS 'CreditedAmount', tb2.TransactionDate,
   tb2.ID, tb2.TransactionCode AS 'TransactionCode',
   tb4.ID AS 'TB4_ID', tb4.FullName AS 'TB_FullName'
   FROM table1 tb1 JOIN table2 tb2 ON
tb1.ID = tb2.CreditorID
JOIN table3 tb3 ON
tb3.ID = tb3.CreditorID
JOIN table4 tb4 ON
tb4.ID = tb2.beneficiaryID
ORDER BY tb2.TransactionDate DESC;

I will appreciate your assistance. I

CodePudding user response：

Case can return only one variable, so I would do it like this :

select 
      CASE
      WHEN tb2.creditorUserRole = 'VND' THEN
      tb1.ID
      WHEN tb2.creditorUserRole = 'AG' THEN
      tb3.ID
      ELSE ""
    END ,
      CASE
      WHEN tb2.creditorUserRole = 'VND' THEN
      tb1.FULLNAME
      WHEN tb2.creditorUserRole = 'AG' THEN
      tb3.FULLNAME
      ELSE ""
    END,
   tb2.CreditedAmount AS 'CreditedAmount', 
   tb2.TransactionDate,
   tb2.ID, 
   tb2.TransactionCode AS 'TransactionCode',
   tb4.ID AS 'TB4_ID', 
   tb4.FullName AS 'TB_FullName'
   FROM table1 tb1 JOIN table2 tb2 ON
tb1.ID = tb2.CreditorID
JOIN table3 tb3 ON
tb3.ID = tb3.CreditorID
JOIN table4 tb4 ON
tb4.ID = tb2.beneficiaryID
ORDER BY tb2.TransactionDate DESC