MySQL select column name from variable

Question

I have a trigger which is storing the value of a case statement in variable @SHIFTIME and this is working fine by returning values Shift1, Shift2 or Shift3. I now need to use the result of this variable in a select statement referring to the COLUMN NAME. I have tried storing this in variable @SHIFTSELECT:

  SET @SHIFTSELECT := (SELECT @SHIFTIME FROM `oee_machinenames` where `oee_machinenames`.`ID` = New.NAME);

So @SHIFTIME is the column name and I expect @SHIFTSELECT to interpret this as:

SELECT `Shift1` FROM `oee_machinenames` where `oee_machinenames`.`ID` = New.NAME

BUT the problem is that @SHIFTSELECT is returning the same value as @SHIFTIME namely the column name (Shift1, Shift2 or Shift3) instead of the required ROW result. This is my complete trigger:

CREATE TRIGGER `oee_upd_final` AFTER INSERT ON `oee_main_interim`
 FOR EACH ROW BEGIN
DECLARE SHIFTIME TEXT;
DECLARE SHIFTSELECT TEXT;
SET @SHIFTIME := ( Select 

      (Case
        When ((CurTime() > oee_machinenames.Shift1) And
        (CurTime() < oee_machinenames.Shift2)) Then 'Shift1'
        When ((CurTime() > oee_machinenames.Shift2) And
        (CurTime() < oee_machinenames.Shift3)) Then 'Shift2'
        When ((CurTime() > oee_machinenames.Shift3) Or
        (CurTime() < oee_machinenames.Shift1)) Then 'Shift3' End) 
    From
      oee_machinenames
      where 
      oee_machinenames.ID = New.NAME

    Group By
      oee_machinenames.ID);

 SET @SHIFTSELECT := (SELECT @SHIFTIME FROM `oee_machinenames` where `oee_machinenames`.`ID` = New.NAME); 


INSERT INTO `oee_main_interim_final` (id,NAME,ts,Left_IO,Left_NIO,Recovery,Right_IO,
Right_NIO,RunMode,S_TYPE,Shift,STD,curr_S_Type,Reporting_Date, Shift_TS) 
VALUES(NULL, New.NAME, New.TS, NEW.Left_IO, New.Left_NIO,  New.Recovery, New.Right_IO, New.Right_NIO, New.RunMode, New.S_TYPE, 


 (select @SHIFTIME),

  (Select
    `STD` From `oee_variant` Where `Machine_ID` = New.NAME And `S_TYPE` = 

(Select
  `S_TYPE`

From
  `v_getmaxid`
Where
  `NAME` = New.Name And
  v_getmaxid.Max_id In (Select
    Max(v_getmaxid.Max_id) As Max_Max_id
  From
    `v_getmaxid`
  Where
    `NAME` = New.Name))

     And `oee_variant`.`Operators` = 
    (Select `Operators` from `oee_machinenames` where `ID` = New.NAME)),


(Select
  `S_TYPE`

From
  `v_getmaxid`
Where
  `NAME` = New.Name And
  v_getmaxid.Max_id In (Select
    Max(v_getmaxid.Max_id) As Max_Max_id
  From
    `v_getmaxid`
  Where
    `NAME` = New.Name)),

(select((case when (((@SHIFTIME = 'Shift3') and 
  (cast(New.TS as time) >= '00:00:01')) 
or (cast(New.TS as time) <= '05:59:00')) 
then (New.TS - interval 1 day) else cast(New.TS as date) end)) AS Reporting_Date),

(SELECT @SHIFTSELECT)

        );
        END

so how can i work around this please?

elstiv
– elstiv

2015-09-17 10:32:09 +00:00
Commented Sep 17, 2015 at 10:32 — elstiv
– elstiv, Commented Sep 17, 2015 at 10:32

Rahul · Accepted Answer · 2015-09-17 20:32:15Z

2

NO, you can't use column name as variable unless it's a dynamic query (or) you are making it in your application code. A dynamic query way would be like

SET @sql = CONCAT("SELECT ", @SHIFTIME," FROM `oee_machinenames` where `oee_machinenames`.`ID` = New.NAME");
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;

Then change the prepared statement to be

SET @result = '';
SET @sql = CONCAT("SELECT ? INTO @result FROM `oee_machinenames` where `oee_machinenames`.`ID` = New.NAME");
 PREPARE stmt FROM @sql;
 EXECUTE stmt  USING @SHIFTIME;
SELECT @result; // Set the data to some other variable
 DEALLOCATE PREPARE stmt;

EDIT: Ahh!! didn't noticed that it's a trigger. Was thinking that it's a procedure. Yes, you can't but your query to get @SHIFTIME and SET @SHIFTSELECT := ... this both part can be combined to a modified query like below and so there will be no need of a dynamic query.

Select 

      (Case
        When ((CurTime() > oee_machinenames.Shift1) And
        (CurTime() < oee_machinenames.Shift2)) Then oee_machinenames.Shift1
        When ((CurTime() > oee_machinenames.Shift2) And
        (CurTime() < oee_machinenames.Shift3)) Then oee_machinenames.Shift2
        When ((CurTime() > oee_machinenames.Shift3) Or
        (CurTime() < oee_machinenames.Shift1)) Then oee_machinenames.Shift3 End) 
    From
      oee_machinenames
      where oee_machinenames.ID = New.NAME

    Group By
      oee_machinenames.ID);

edited Sep 17, 2015 at 20:32

answered Sep 17, 2015 at 10:34

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

elstiv Over a year ago

thanks but how can I call the result of this in my trigger since I need to populate a record with this resulting value? Can I use CALL function?

elstiv Over a year ago

I am getting an error saying that I cannot use Dynamic Query in a trigger.

Rahul Over a year ago

@elstiv, Yes my bad didn't noticed that it's a trigger. Anyways, see edit in answer mainly the EDIT part if it helps.

Zafar Malik · Accepted Answer · 2015-09-17 10:45:01Z

0

You can use as per below-

SET @qry = CONCAT('SELECT ',@SHIFTIME,' INTO @SHIFTSELECT FROM `oee_machinenames` where `oee_machinenames`.`ID` = New.NAME');
PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

answered Sep 17, 2015 at 10:45

Zafar Malik

6,8723 gold badges23 silver badges31 bronze badges

Collectives™ on Stack Overflow

MySQL select column name from variable

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related