How do I used a Create / Use / output table using a SQL transform SQL type / CREATE PROCEDURE?
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?
Best Answer
-
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
0
Answers
-
When you call the procedure, don't use the ;
CALL transpose()
This should be done as a SQL transform step.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
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?
0 -
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
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
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?
0 -
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
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"0 -
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
0 -
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>
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"0 -
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).
0 -
Here's my demo data (I import using Excel connector in DOMO) and scripts..
this is driving me mad..
I can't see the problem..
0 -
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
0 -
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,
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"1 -
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
0 -
@DuncanDomo kindly recreated his solution here: https://domo-dojo.domo.com/datacenter/dataflows/44/author
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"0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive