SQL error 1064 - MySQL issue creating view

Question

I have this query

SET @regex_bl = (SELECT GROUP_CONCAT(DISTINCT word ORDER BY word DESC SEPARATOR '|') FROM t_black_list);
SET @regex_sl = (SELECT GROUP_CONCAT(DISTINCT word ORDER BY word DESC SEPARATOR '|') FROM t_suspect_list);
SELECT
p_f_A.ID,
p_f_A.id_st,
p_f_A.source_query,
p_f_A.st_text,
if (p_f_A.st_image regexp 'http' = 1,p_f_A.st_image,'NO IMAGE') AS st_IMAGE_URL,
if (p_f_A.st_text regexp @regex_bl = 1,'YES','NO') AS BLACK_LIST,
if (p_f_A.st_text regexp @regex_sl = 1,'YES','NO') AS SUSPECT_LIST,
(select f_f_image_recog.TAG from f_f_image_recog WHERE (f_f_image_recog.id_st=p_f_A.ID AND f_f_image_recog.TAG NOT LIKE '%Failure%' ) ORDER BY f_f_image_recog.value DESC  LIMIT 1) AS CLARIFAI_1ST_TAG,
(SELECT "X" as X from f_f_tensor_ws WHERE (f_f_tensor_ws.id_st=p_f_A.ID) AND (f_f_tensor_ws.value > 0.98) LIMIT 1 ) AS TS_B_GREEN_LABEL_98,
(SELECT f_f_text_recog.matched_text from f_f_text_recog WHERE (f_f_text_recog.id_st =p_f_A.ID) AND (f_f_text_recog.type LIKE '%Money%') LIMIT 1) AS NLP_MONEY_VALUE,
(SELECT f_f_text_recog.matched_text from f_f_text_recog WHERE (f_f_text_recog.id_st =p_f_A.ID) AND (f_f_text_recog.type LIKE '%Company%') LIMIT 1) AS NLP_COMPANY
from p_f_A

All works as expected if I run it in a query box in my tool (HeidiSQL). When I try to create a view an error is raised:

Errore SQL (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @regex_bl = (SELECT GROUP_CONCAT(DISTINCT word ORDER BY word DESC SEPARATOR ' at line 1

I did use HeidiSQL function to create view and simply insert above Query — SimonFreeman, May 03 '18 at 10:35

Lex Luthor · Answer 1 · 2018-05-03T12:52:07.887

1

I cant see from your code how you declare your variables, I just see you setting the values. This could make all the difference.

Your problem could be because you can't use variables in views in the same way as you could in a stored procedure or a function.

Please see the following post for some options. SQL Views - no variables?

Give this answer from the above referenced post a try:

CREATE VIEW MyView

AS

  WITH MyVars (SomeVar, Var2)

  AS (

    SELECT

      'something' AS 'SomeVar',

      123 AS 'Var2'

  )

  SELECT *

  FROM MyTable

  WHERE x = (SELECT SomeVar FROM MyVars)

edited May 03 '18 at 12:52

answered May 03 '18 at 10:53

Lex Luthor

27
7

Hello, the code is just as above I do only set values – SimonFreeman May 03 '18 at 11:31
Do you know if is possible to call a subquery here "regexp @regex_bl" instead of creating/setting a variable? If I can avoid variable I think I could create a view – SimonFreeman May 03 '18 at 11:41
Im pretty sure its only a syntax error because of the rules with views. Have you tried to use the options in the older post I referenced? Im going too edit my answer to show you the code I would suggest. – Lex Luthor May 03 '18 at 12:46
Hello, sorry I'm unable to elaborate how interact with your code suggestion and regexp syntax. – SimonFreeman May 04 '18 at 08:07

score 1 · Accepted Answer · answered May 08 '18 at 06:28

Hello I made some mods on the query as follow, and now it works:

SELECT
p_f_A.ID,
p_f_A.id_st,
p_f_A.source_query,
p_f_A.st_text,
if (p_f_A.st_image regexp 'http' = 1,p_f_A.st_image,'NO IMAGE') AS st_IMAGE_URL,
if (p_f_A.st_text regexp (SELECT GROUP_CONCAT(DISTINCT word ORDER BY word DESC SEPARATOR '|') FROM t_black_list) = 1,'YES','NO') AS BLACK_LIST,
if (p_f_A.st_text regexp (SELECT GROUP_CONCAT(DISTINCT word ORDER BY word DESC SEPARATOR '|') FROM t_suspect_list) = 1,'YES','NO') AS SUSPECT_LIST,
(select f_f_image_recog.TAG from f_f_image_recog WHERE (f_f_image_recog.id_st=p_f_A.ID AND f_f_image_recog.TAG NOT LIKE '%Failure%' ) ORDER BY f_f_image_recog.value DESC  LIMIT 1) AS CLARIFAI_1ST_TAG,
(SELECT "X" as X from f_f_tensor_ws WHERE (f_f_tensor_ws.id_st=p_f_A.ID) AND (f_f_tensor_ws.value > 0.98) LIMIT 1 ) AS TS_B_GREEN_LABEL_98,
(SELECT f_f_text_recog.matched_text from f_f_text_recog WHERE (f_f_text_recog.id_st =p_f_A.ID) AND (f_f_text_recog.type LIKE '%Money%') LIMIT 1) AS NLP_MONEY_VALUE,
(SELECT f_f_text_recog.matched_text from f_f_text_recog WHERE (f_f_text_recog.id_st =p_f_A.ID) AND (f_f_text_recog.type LIKE '%Company%') LIMIT 1) AS NLP_COMPANY
from p_f_A

SQL error 1064 - MySQL issue creating view

2 Answers2