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
- 10.5K All Categories
- 7 Connect
- 917 Connectors
- 250 Workbench
- 463 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 190 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 246 Distribute
- 62 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 173 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive