How do I used a Create / Use / output table using a SQL transform SQL type / CREATE PROCEDURE?

DuncanDomo

I'm trying to just work out the basic syntax / method in DOMO SQL transform of:

• Create Stored Procedure (creates a result table after some manipulation of the data)
• Execute Stored Procedure (to get result table)
• Output Result table into DOMO for use in charts etc..

I'm trying to get this to work but I can't get the results of the new table.

I've tried this:

• SQL Transform SQL type

CREATE PROCEDURE transpose()

BEGIN

SELECT Progid into @sql2 FROM progtest;

SET @str=concat('create table products as ',@sql2);

PREPARE q from @str;

EXECUTE q;

END

It runs OK..

• Then I add another SQL Transform SQL type

CALL transpose();

and I get an error "Result consisted of more than one row"

i've also tried adding it as SQL Transform Table type, again just

CALL transpose();

and I get an error "Table transform must be a SELECT clause"

I'm trying to just work out the basic syntax / method in DOMO SQL transform of:

• Create Stored Procedure (creates a result table after some manipulation of the data)
• Execute Stored Procedure (to get result table)
• Output Result table into DOMO for use in charts etc..

What am I doing wrong?

DuncanDomo

Hi @ST_-Superman-_ and @jaeW_at_Onyx

Thanks for your help!! I FIXED IT!!!

I don't know what I was doing wrong, so I striped it back to basics with a super simple example.

This is what I got to work:

It runs all the way through and fill my output table..

For anyone trying to get Stored Procs to work in SQL transforms.. Here's the code so you have a template to work from..

Step 1 - create your "results" table

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

    species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

Step 2 - create your stored proc (probably does something useful to results table)

CREATE PROCEDURE FillPet()

BEGIN

INSERT INTO pet

    VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

END; 

Step 3 - call proc

CALL FillPet();

Step 4 - Select the results table using SQL Transform (table type) and name the transform

Select * from pet

Step 5 - finally set last transform to be your Output Table and you have it..

Then you end up with a useable dataset that looks like this:

I'm so happy to have Puffball in my output. It's like a real pet to me ;-)

Take care,

DuncanDomo

p.s. Thanks again @ST_-Superman-_ and @jaeW_at_Onyx for the help & encouragement

ST_Superman

When you call the procedure, don't use the ;

CALL transpose()

This should be done as a SQL transform step.

DuncanDomo

Hi @ST_-Superman-_

Thanks for the reply and help.

Unfortunately, I tried that and I get this error using a SQL transform SQL step:

I also tried running it as a SQL Transform Table step. And then I get this error:

What am I doing wrong?

ST_Superman

Sorry, I just checked the syntax that I used and I told you incorrectly. I don't use stored procedures very often.

I actually stored a transpose procedure as well:

-- 10)
CREATE PROCEDURE transpose()


BEGIN


SELECT goal_info into @sql2 FROM for_transpose;


SET @str=concat('create table normalized_goals as ',@sql2);


PREPARE q from @str;
EXECUTE q;


END

The next step was just:

call transpose;

Let me know if this works for you

DuncanDomo

Hi @ST_-Superman-_

Thanks for your help/response.

Unfortunately, I tried that I'm still getting the error on "call transpose;" part see:

My CREATE PROCEDURE code is..

******************************

CREATE PROCEDURE transpose()

BEGIN

SELECT Progid into @sql2 FROM progtest;

SET @str=concat('create table products as ',@sql2);

PREPARE q from @str;

EXECUTE q;

END

******************************

progtest is a DOMO table from a Excel upload into DOMO dataset.

I can't see anything different to yours? What am I missing?

jaeW_at_Onyx

ok... so... there's no such thing as 'Domo MySQL'. It's just MySQL 5.6

if it works in MySQL 5.6 in a local install it should work in the ETL. Domo does limit access to a few system tables but it's the same.

There's an example here

https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Dynamic_Pivot

DuncanDomo

Hi @jaeW_at_Onyx

Thanks for the reply.

Sorry I'm new and struggling to get this simple Stored Proc to run and use output table.

If you check out the screenshot, the only error i get is:

"result consisted of more than one row" = presumably the table I created.

Thanks also for the example, it looks almost the same, the only difference I can see is that in my DOMO instance, I don't have this option:

So in my DOMO instance there is no checkbox called "Generate Output Table" so I can't uncheck it.

Could this be the problem? Is this one of those features that I have to ask support to turn on? Or is it a bug?

Thanks, Duncan

jaeW_at_Onyx

Duncan "generate output table" is a feature that may have been deprecated since the KB was written. I've never seen it.

I usually create an output table where i SELECT * <tableCreatedInStoredProc>

DuncanDomo

Hi @jaeW_at_Onyx

Thanks so much for responding. Ok that's a red herring then.

But my problem is that when I call the stored procedure (call transpose), it falls over.

with the error "result consisted of more than one row"

Therefore, I can never get to the next step (to query the table created in the stored proc).

DuncanDomo

@jaeW_at_Onyx

Here's my demo data (I import using Excel connector in DOMO) and scripts..

this is driving me mad..

1 - ProgTest.xlsx

2 - Create Procedure.txt

3 - Call transpose.txt

I can't see the problem..

DuncanDomo

Hi @ST_-Superman-_ and @jaeW_at_Onyx

Thanks for your help!! I FIXED IT!!!

I don't know what I was doing wrong, so I striped it back to basics with a super simple example.

This is what I got to work:

It runs all the way through and fill my output table..

For anyone trying to get Stored Procs to work in SQL transforms.. Here's the code so you have a template to work from..

Step 1 - create your "results" table

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

    species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

Step 2 - create your stored proc (probably does something useful to results table)

CREATE PROCEDURE FillPet()

BEGIN

INSERT INTO pet

    VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

END; 

Step 3 - call proc

CALL FillPet();

Step 4 - Select the results table using SQL Transform (table type) and name the transform

Select * from pet

Step 5 - finally set last transform to be your Output Table and you have it..

Then you end up with a useable dataset that looks like this:

I'm so happy to have Puffball in my output. It's like a real pet to me ;-)

Take care,

DuncanDomo

p.s. Thanks again @ST_-Superman-_ and @jaeW_at_Onyx for the help & encouragement

jaeW_at_Onyx

congrats @DuncanDomo , also thank you for doing this write-up. that's phenomenal and a real boon to the rest of the community next time they need to build a stored proc.

We're building a repository of dojo solutions in domo-dojo.domo.com if you're not part of the instance, I'd be happy to add you so we can put your ETL there and a link to the solution here.

Also, I didn't want to test it, but i suspect this line from your original stored proc wasn't doing you any favors. I would have given it a real table name.

SET @str=concat('create table products as ',@sql2);

I know it's dorky, but I'd love to feature your solution on my YouTube Channel, I collate and collect solutions and tutorials for the community. https://www.youtube.com/channel/UCpnWmFCBWyqBMJlw6ZxNokQ,

DuncanDomo

Hey @jaeW_at_Onyx

No problem, it was my first post on Dojo. Feels like a great community, I hope to contribute over time (once I know what I'm doing and actually have some knowledge to share).

re: domo-dojo.domo.com = no I don't have access, be happy to be given access and add Puffball in there. Let me know how to log on..

re: YouTube = sure thing, sounds good to me..

Thanks, DuncanDomo

jaeW_at_Onyx

@DuncanDomo kindly recreated his solution here: https://domo-dojo.domo.com/datacenter/dataflows/44/author