Can anyone please let me know if there is a possibility of writing dynamic Sql query in dataflow? If so, how?
You have to use "CREATE PROCEDURE" in order to do this. Here is an example:
CREATE PROCEDURE transpose()
SELECT product_info into @sql2 FROM for_transpose;
SET @str=concat('create table products as ',@sql2);
PREPARE q from @str;EXECUTE q;
You then call the function in the next transform:
@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()BEGINSelect * from `newexceltest`;end
and then the next step is just
but it won't output anything. Any ideas?
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?
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()BEGINCREATE TABLE newexceltest_temp ASSelect * from `newexceltest`;end
Then in your next step you would do CALL GetAllDates and in your output dataset you would do
select * from newexceltest_temp
Hope this helps,
**Please mark "Accept as Solution" if this post solves your problem**Say "Thanks" by clicking the "heart" in the post that helped you.
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!
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:
SELECT Progid into @sql2 FROM progtest;
PREPARE q from @str;
It runs OK..
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
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:
What am I doing wrong?