mysqlstored-proceduresaliasworkbenchmysql-variables

Error on "User Defined Variable" assigned as dynamic Field Alias


I have created MySQL query interpolation below in Workbench. The query was so fit to my needs except that I could not effect the "User Defined Variable" @var1/2/3 as my field aliases.

I am getting an error on AS @var1 in this line (SELECT field4 FROM view_table LIMIT 0, 1) AS @var1 which I suspect the field Alias is not accepting "User Defined Variables" as illustrated in my below MySQL query:

DROP VIEW IF EXISTS view_table;

CREATE VIEW view_table AS
     SELECT table3.field1, table1.field2
     FROM table1
     JOIN table2 ON table1.table2_id = table2.id
     JOIN table3 ON table1.table3_id = table3.id
     JOIN table4 ON item_var.table4_id = table4.id
     WHERE table2.id = 1
     ORDER BY table1 ASC LIMIT 3;

SET @var1 := (SELECT table3.field1 FROM view_table LIMIT 0, 1);  -- created for the @var1 variable
SET @var2 := (SELECT table3.field1 FROM view_table LIMIT 1, 1);  -- created for the @var2 variable
SET @var3 := (SELECT table3.field1 FROM view_table LIMIT 2, 1);  -- created for the @var3 variable

SELECT table4.field1, table2.field2, table2.field3,
     (SELECT field4 FROM view_table LIMIT 0, 1) AS @var1,  -- @var1 is not recognized as alias
     (SELECT field5 FROM view_table LIMIT 1, 1) AS @var2,  -- @var2 is not recognized as alias
     (SELECT field6 FROM view_table LIMIT 2, 1) AS @var3   -- @var3 is not recognized as alias
FROM table2
JOIN table4 ON table2.id = table4.id
WHERE table2.id = 1;

Here's the query result I want:

+-------------+------------+------------+------------+------------+-------------+------------+
|name         |Field1      |Field2      |Field3      |@var1       |@var2        |@var3       |
+-------------+------------+------------+------------+------------+-------------+------------+
|XYZ Company  |Field1Data  |Field2Data  |Field3Data  |Field4Data  | Field5Data  |Field6Data  |
+-------------+------------+------------+------------+------------+------------+-------------+

Can anybody help me figure-out the right MySQL syntax for the header variable as field Aliases?

UPDATE: I have posted the solution below for reference.


Solution

  • At last! I got the solution!

    I just need to concatenate my last query above to be able to insert the field variables I created. Then, pass it on a statement, execute the statement and deallocate to release it.

    SET @statement_var = CONCAT("
    SELECT table4.field1, table2.field2, table2.field3, 
        (SELECT field4 FROM view_table LIMIT 0, 1) AS '", @var1, "' ,   
        (SELECT field5 FROM view_table LIMIT 1, 1) AS '", @var1, "' , 
        (SELECT field6 FROM view_table LIMIT 2, 1) AS '", @var1, "' 
    FROM table2
    JOIN table4 ON item_table2.id = table4.id
    WHERE table2.id = 1;");
    
    PREPARE statement FROM @statement_var;
    
    EXECUTE statement;
    
    DEALLOCATE PREPARE statement;
    

    Hope this helps for others with interpolation issue. Cheers!