Calling a procedure in MySQL ETL flow

Reply
Highlighted
White Belt

Calling a procedure in MySQL ETL flow

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


Accepted Solutions
Highlighted
Major Red Belt

Re: Calling a procedure in MySQL ETL flow

@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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post


All Replies
Highlighted
Major Red Belt

Re: Calling a procedure in MySQL ETL flow

@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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

Highlighted
White Belt

Re: Calling a procedure in MySQL ETL flow

Thanks.

 

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

Announcements
What is your Dojo rank and what badges have you earned? How am I doing? Get a refresher on how to up your rank and collect badges in the Dojo here.