1

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.

RickyBelmont
  • 619
  • 4
  • 11
  • Have you tried the answer given here by Somwang Souksavatd: https://stackoverflow.com/questions/3075147/select-into-variable-in-mysql-declare-causes-syntax-error – wosi Jun 25 '20 at 13:50
  • I looked into it and it is different from my case. I am trying the @vendor1/2/3 here to be my string fieldname as Alias. I think I am having trouble with converting this variable to string perhaps. Not actually sure yet but still investigating and searching for some documentations or same cases with me at least. – RickyBelmont Jun 25 '20 at 22:34
  • What happens if set your vendors variable manually, e.g. SET your_vendor_variable = 'vendorX' instead of using the select-statements and then use your_vendor_variable as your alias. Do you then get the same error? – wosi Jun 26 '20 at 08:29
  • Hi @wosi Apologies if I took so long to response. I having trouble now with my code above. The alias "min_prices" assigned to prices is getting an error now. Previously its working. The error is confined here "FROM price AS min_prices — Error Code: 1146. Table 'project_x.min_prices' doesn't exist". If I remove the alias it defeats my purpose and longer useful to me. Otherwise, I will bring back the View instead. But wondering so sudden it gets an error. I will have to fix this one first and try your suggestion. – RickyBelmont Jun 26 '20 at 21:53
  • Confirmed "FROM price AS min_prices" is getting an error. I replaced it with "DROP VIEW IF EXISTS min_prices; CREATE VIEW min_prices AS" and now its working. I am so surprised how this has happen. – RickyBelmont Jun 26 '20 at 21:59
  • I tried your suggestion "SET @vendor1 := (SELECT vendor_name FROM min_prices LIMIT 0, 1);" but its giving me the same result. However, the SET variable is okay and it is returning the value I want. Same the SELECT variable I did. Both are returning same error. – RickyBelmont Jun 26 '20 at 22:19

1 Answers1

0

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!

RickyBelmont
  • 619
  • 4
  • 11