How to write dynamic sql query in dataflow?
Comments
-
You have to use "CREATE PROCEDURE" in order to do this. Here is an example:
CREATE PROCEDURE transpose()
BEGIN
SELECT product_info into @sql2 FROM for_transpose;
SET @str=concat('create table products as ',@sql2);
PREPARE q from @str;
EXECUTE q;END
You then call the function in the next transform:
CALL transpose;
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
@ST_-Superman-_ I know this is an 8 month old post, but can you use a Called Stored Procedure as an output? I did this:
Create Procedure GetAllDates()
BEGIN
Select * from `newexceltest`;
endand then the next step is just
Call GetAllDates
but it won't output anything. Any ideas?
0 -
I don't think that you would need to create a procedure for what you are trying to do. Have you tried just using the transform:
SELECT * FROM `newexceltest`
That should work. Is there something more to this query that would require using a dynamic select statement?
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
I agree with ST_-Superman-_ that you would not need a stored procedure for the example you gave but if you did it is important to understand the "CALL" statement just runs the syntax inside of your stored procedure. It doesn't actually output any tables. You would have to make a specific function call in the stored procedure. Something like ...
Create Procedure GetAllDates()
BEGIN
CREATE TABLE newexceltest_temp AS
Select * from `newexceltest`;
endThen in your next step you would do CALL GetAllDates and in your output dataset you would do
select * from newexceltest_temp
Hope this helps,
Brian
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.0 -
Thanks for the replies @ST_-Superman-_ and @Property_Ninja!
Sorry I wasn't clear, what I'm actually doing is creating a pivot table with columns that change but was just testing out the stored procedure in domo. I just dumped the results of the stored procedure into a new table, called it in the next transform and then queried the new table in the final one so I'm all set now. Thanks for your help everyone!
0 -
Hi @ST_-Superman-_ and @trafalger
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?
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive