How to write dynamic sql query in dataflow?

Kaushal

Can anyone please let me know if there is a possibility of writing dynamic Sql query in dataflow? If so, how?

ST_Superman

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;

trafalger

@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`;
end

 

and then the next step is just 

 

Call GetAllDates

 

but it won't output anything. Any ideas? 

 

ST_Superman

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?

Property_Ninja

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`;

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,

 

Brian

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

 

trafalger

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! 

DuncanDomo

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?