How to write dynamic sql query in dataflow?

Kaushal
Kaushal Member
in Archive

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

Comments

  • ST_-Superman-_
    ST_-Superman-_ Coach

    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.” -Superman
  • trafalger
    trafalger Coach

    @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-_
    ST_-Superman-_ Coach

    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.” -Superman
  • Property_Ninja
    Property_Ninja Contributor

    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.

     


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

    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
    DuncanDomo Contributor

    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?

Categories