How do I used a Create / Use / output table using a SQL transform SQL type / CREATE PROCEDURE?

DuncanDomo
DuncanDomo Contributor
edited March 2023 in SQL DataFlows

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..

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?

Tagged:

Best Answer

  • DuncanDomo
    DuncanDomo Contributor
    edited April 2021 Answer ✓

    Hi @ST_-Superman-_ and @jaeW_at_Onyx

    Thanks for your help!! I FIXED IT!!!

    I don't know what I was doing wrong, so I striped it back to basics with a super simple example.

    This is what I got to work:

    image.png

    It runs all the way through and fill my output table..

    For anyone trying to get Stored Procs to work in SQL transforms.. Here's the code so you have a template to work from..

    Step 1 - create your "results" table

    CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

        species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);


    Step 2 - create your stored proc (probably does something useful to results table)

    CREATE PROCEDURE FillPet()

    BEGIN

    INSERT INTO pet

        VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);


    END; 

    Step 3 - call proc

    CALL FillPet();

    Step 4 - Select the results table using SQL Transform (table type) and name the transform

    Select * from pet

    image.png


    Step 5 - finally set last transform to be your Output Table and you have it..

    image.png


    Then you end up with a useable dataset that looks like this:

    image.png


    I'm so happy to have Puffball in my output. It's like a real pet to me ;-)

    Take care,

    DuncanDomo


    p.s. Thanks again @ST_-Superman-_ and @jaeW_at_Onyx for the help & encouragement

Answers

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

    When you call the procedure, don't use the ;

    CALL transpose()

    This should be done as a SQL transform step.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • DuncanDomo
    DuncanDomo Contributor
    edited April 2021

    Hi @ST_-Superman-_

    Thanks for the reply and help.

    Unfortunately, I tried that and I get this error using a SQL transform SQL step:

    image.png


    I also tried running it as a SQL Transform Table step. And then I get this error:

    image.png


    What am I doing wrong?

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

    Sorry, I just checked the syntax that I used and I told you incorrectly. I don't use stored procedures very often.

    I actually stored a transpose procedure as well:

    -- 10)
CREATE PROCEDURE transpose()


BEGIN


SELECT goal_info into @sql2 FROM for_transpose;


SET @str=concat('create table normalized_goals as ',@sql2);


PREPARE q from @str;
EXECUTE q;


END

    The next step was just:

    call transpose;


    Let me know if this works for you


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • DuncanDomo
    DuncanDomo Contributor
    edited April 2021

    Hi @ST_-Superman-_

    Thanks for your help/response.

    Unfortunately, I tried that I'm still getting the error on "call transpose;" part see:

    image.png


    My CREATE PROCEDURE code is..

    ******************************

    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

    ******************************

    progtest is a DOMO table from a Excel upload into DOMO dataset.


    I can't see anything different to yours? What am I missing?

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    edited April 2021

    ok... so... there's no such thing as 'Domo MySQL'. It's just MySQL 5.6

    if it works in MySQL 5.6 in a local install it should work in the ETL. Domo does limit access to a few system tables but it's the same.


    There's an example here

    https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Dynamic_Pivot

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • DuncanDomo
    DuncanDomo Contributor

    Hi @jaeW_at_Onyx

    Thanks for the reply.

    Sorry I'm new and struggling to get this simple Stored Proc to run and use output table.

    If you check out the screenshot, the only error i get is:

    image.png

    "result consisted of more than one row" = presumably the table I created.

    Thanks also for the example, it looks almost the same, the only difference I can see is that in my DOMO instance, I don't have this option:

    image.png


    So in my DOMO instance there is no checkbox called "Generate Output Table" so I can't uncheck it.

    Could this be the problem? Is this one of those features that I have to ask support to turn on? Or is it a bug?

    Thanks, Duncan

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    Duncan "generate output table" is a feature that may have been deprecated since the KB was written. I've never seen it.

    I usually create an output table where i SELECT * <tableCreatedInStoredProc>

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • DuncanDomo
    DuncanDomo Contributor

    Hi @jaeW_at_Onyx

    Thanks so much for responding. Ok that's a red herring then.

    But my problem is that when I call the stored procedure (call transpose), it falls over.

    image.png

    with the error "result consisted of more than one row"

    Therefore, I can never get to the next step (to query the table created in the stored proc).

  • DuncanDomo
    DuncanDomo Contributor
    edited April 2021

    @jaeW_at_Onyx

    Here's my demo data (I import using Excel connector in DOMO) and scripts..

    this is driving me mad..

    1 - ProgTest.xlsx
    2 - Create Procedure.txt
    3 - Call transpose.txt


    I can't see the problem..

  • DuncanDomo
    DuncanDomo Contributor
    edited April 2021 Answer ✓

    Hi @ST_-Superman-_ and @jaeW_at_Onyx

    Thanks for your help!! I FIXED IT!!!

    I don't know what I was doing wrong, so I striped it back to basics with a super simple example.

    This is what I got to work:

    image.png

    It runs all the way through and fill my output table..

    For anyone trying to get Stored Procs to work in SQL transforms.. Here's the code so you have a template to work from..

    Step 1 - create your "results" table

    CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

        species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);


    Step 2 - create your stored proc (probably does something useful to results table)

    CREATE PROCEDURE FillPet()

    BEGIN

    INSERT INTO pet

        VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);


    END; 

    Step 3 - call proc

    CALL FillPet();

    Step 4 - Select the results table using SQL Transform (table type) and name the transform

    Select * from pet

    image.png


    Step 5 - finally set last transform to be your Output Table and you have it..

    image.png


    Then you end up with a useable dataset that looks like this:

    image.png


    I'm so happy to have Puffball in my output. It's like a real pet to me ;-)

    Take care,

    DuncanDomo


    p.s. Thanks again @ST_-Superman-_ and @jaeW_at_Onyx for the help & encouragement

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    congrats @DuncanDomo , also thank you for doing this write-up. that's phenomenal and a real boon to the rest of the community next time they need to build a stored proc.


    We're building a repository of dojo solutions in domo-dojo.domo.com if you're not part of the instance, I'd be happy to add you so we can put your ETL there and a link to the solution here.


    Also, I didn't want to test it, but i suspect this line from your original stored proc wasn't doing you any favors. I would have given it a real table name.

    SET @str=concat('create table products as ',@sql2);

    I know it's dorky, but I'd love to feature your solution on my YouTube Channel, I collate and collect solutions and tutorials for the community. https://www.youtube.com/channel/UCpnWmFCBWyqBMJlw6ZxNokQ,

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • DuncanDomo
    DuncanDomo Contributor

    Hey @jaeW_at_Onyx

    No problem, it was my first post on Dojo. Feels like a great community, I hope to contribute over time (once I know what I'm doing and actually have some knowledge to share).

    re: domo-dojo.domo.com = no I don't have access, be happy to be given access and add Puffball in there. Let me know how to log on..

    re: YouTube = sure thing, sounds good to me..

    Thanks, DuncanDomo

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @DuncanDomo kindly recreated his solution here: https://domo-dojo.domo.com/datacenter/dataflows/44/author

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories