how do I access a table built by a domo stored procedure?

kjones140
kjones140 ⚪️
in Dataflows

I have created a dataflow that in separate transform boxes does the following:

  1. Drops ta Stored Procedure 
  2. Creates a Stored Procedure
  3. Calls the Stored Procedure

 

The Drop and Create transform return valid sql and seem to be working fine.

But the Call (step 3) fails indicating that the table that I created in the procedure doesn't exist. 

 

Near the beginning of the stored procedure, I create the tables as follows: 

 

TRUNCATE TABLE STG5Adjustments;
CREATE TABLE IF NOT EXISTS STG5Adjustments(
Field1 int,
Field2int,
FiscalYear int,
FieldValue decimal(18,2),
FieldAdjustment decimal(18,2)
);

 

Then as I loop thru the records in a cursor I insert the derived output records into my table as shown below:

 

INSERT INTO STG5Adjustments(Field1, Field2, FiscalYear, FieldValue, FieldAdjustment)
VALUES(v_Field1, v_Field2, v_FiscalYear, v_FieldValue, v_FieldAdjustment);

 

When I call the procedure (in a separate transform), it cannot find the table named STG5Adjustments that I created.

How do access this table that I created in my stored procedure???

 

 

Comments

  • nicolasfeddern
    nicolasfeddern

    Can you share a screenshot of the error you're seeing when you call the stored procedure? Is it possible for you to share your script or a version of it so we can take a look at the transforms?

     

    In general, you should be able to do the following:

    1. Create a stored proc in a given transform

    2. Call the stored proc in the next transform

    3. Any tables created as an output of the proc should referencable in subsequent transforms

  • kjones140
    kjones140 ⚪️

     

     
    I will post the error returned and upload the transform scripts shortly for you to look at.  Thanks!
  • kjones140
    kjones140 ⚪️

    Here's the error returned:

    Failed while executing
    The database reported a syntax error. Table 'transform_fa4321a5c6cf4280affc87c77ccf2332.STG5AdjustedLangGiving' doesn't exist
     
    (Note: this is a different tablename than my original post, but this error reflects the current running of the attached scripts)
     
    I have attached the 3 transforms.  DROP, CREATE and CALL.  I have removed all of the procedural logic in the CREATE file to make it easy to read/follow.  And, it still returns the same error.
  • kjones140
    kjones140 ⚪️

    Just noticed the CREATE that I posted was incorrect.  I have attached the correct one being used.

Categories