Calling a procedure in MySQL ETL flow

user073741
user073741 Member
in Magic ETL

I'm trying to create a pivot table output using a stored procedure, but I'm getting an error while call the procedure.

Screen Shot 2020-04-20 at 12.56.16 PM.png

 

Screen Shot 2020-04-20 at 12.55.57 PM.png

 

Screen Shot 2020-04-20 at 12.56.46 PM.png

when I to execute the procudure, I'm getting the error as:

Screen Shot 2020-04-20 at 1.00.17 PM.pngLet me know what wrong I'm doing here. 

 

Thanks in Advance,

Srinivas V

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    @user073741 ,

     

    i would start by paring back my stored proc to ensure that i had the proper syntax for CREATE-ing and calling a stored proc.  keep in mind we're on mysql v 5.6 i believe.

     

    THEN i would take a really close look at the error message.

     

    it says <number> FROM.

     

    I would guess that you have values in your table that contain spaces so SQL is 'mininterpreting' how to handle table creation, OR you're not properly separating the values as column names in your GROUP_CONCAT statement in a way that SQL can interpret into separate columns.

     

    Try taking your SQL statement out of the Stored PROC and running it as a straight SELECT statement.  Make sure the results are as you expect.

     

    LASTLY, MySQL is not strong at Pivot Statements.  Consider using MagicETL.  I know it's a pain to setup and ideally you don't want to have to maintain code, but long term it will probably be more performant than SQL.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    @user073741 ,

     

    i would start by paring back my stored proc to ensure that i had the proper syntax for CREATE-ing and calling a stored proc.  keep in mind we're on mysql v 5.6 i believe.

     

    THEN i would take a really close look at the error message.

     

    it says <number> FROM.

     

    I would guess that you have values in your table that contain spaces so SQL is 'mininterpreting' how to handle table creation, OR you're not properly separating the values as column names in your GROUP_CONCAT statement in a way that SQL can interpret into separate columns.

     

    Try taking your SQL statement out of the Stored PROC and running it as a straight SELECT statement.  Make sure the results are as you expect.

     

    LASTLY, MySQL is not strong at Pivot Statements.  Consider using MagicETL.  I know it's a pain to setup and ideally you don't want to have to maintain code, but long term it will probably be more performant than SQL.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user073741
    user073741 Member

    Thanks.

     

    As you correctly mentioned, The column names were not getting populated correctly from the procedure. Executed the code manually and found that out.

Categories