How can I use the MATCH() function with a declared variable string in a MySQL dataflow?

JacobFolsom
JacobFolsom Domo Employee
edited March 2023 in SQL DataFlows

I am looking for assistance in determining how to use the value of a string variable to DECLARE a persistent variable AS a STRING that can be passed into the MATCH() function in MySQL transform.

Jacob Folsom
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Tagged:

Best Answer

  • JacobFolsom
    JacobFolsom Domo Employee
    Answer ✓

    The solution here requires using the SQL transform instead of the Table transform option in MySQL flows with a few steps below. Disclaimer: I would probably solve this in other ways, but to answer the specific question gives some detail on how stored procedures work in MySQL flows.

    Step 1: Write a SQL Transform to add a FULLTEXT index to the column you need to match. In this sample, I want to search the column customerName. (This is a required index type for the match function to properly evaluate.)

    image.png

    Step 2: Write a SQL Transform with a stored procedure declaring your variable

    This step creates a procedure, declares a variable, and creates a table output that can be referenced later

    image.png

    Step 3: Write a SQL Transform to call the procedure

    image.png

    Step 4: Write a Table Transform or Output by selecting from the table created in the procedure.

    Notice that the variable declared 'justin' properly fed into the MATCH()

    image.png


    Jacob Folsom
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • JacobFolsom
    JacobFolsom Domo Employee
    Answer ✓

    The solution here requires using the SQL transform instead of the Table transform option in MySQL flows with a few steps below. Disclaimer: I would probably solve this in other ways, but to answer the specific question gives some detail on how stored procedures work in MySQL flows.

    Step 1: Write a SQL Transform to add a FULLTEXT index to the column you need to match. In this sample, I want to search the column customerName. (This is a required index type for the match function to properly evaluate.)

    image.png

    Step 2: Write a SQL Transform with a stored procedure declaring your variable

    This step creates a procedure, declares a variable, and creates a table output that can be referenced later

    image.png

    Step 3: Write a SQL Transform to call the procedure

    image.png

    Step 4: Write a Table Transform or Output by selecting from the table created in the procedure.

    Notice that the variable declared 'justin' properly fed into the MATCH()

    image.png


    Jacob Folsom
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • JacobFolsom
    JacobFolsom Domo Employee

    posted duplicate to new internal dojo board https://dojo.domo.com/internal/discussion/55255/how-can-i-use-the-match-function-with-a-declared-variable-string-in-a-mysql-dataflow#latest

    Jacob Folsom
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories