CODEWITHSUNDEEP
mysqlmysql-error-1064
SimonFreeman
SimonFreeman

Reputation: 205

SQL error 1064 - MySQL issue creating view

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

Upvotes: 1

Views: 327

Answers (2)

SimonFreeman
SimonFreeman

Reputation: 205

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

Upvotes: 1

Lex Luthor
Lex Luthor

Reputation: 27

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)

Upvotes: 1

Related Questions