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

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

Drops ta Stored Procedure
Creates a Stored Procedure
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???

Find more posts tagged with

Comments

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

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

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.

CALL.txt

CREATE.txt

DROP.txt

kjones140

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

CREATE.txt