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???
Comments
-
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
1 -
I will post the error returned and upload the transform scripts shortly for you to look at. Thanks!1
-
Here's the error returned:
Failed while executingThe 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.0 -
Just noticed the CREATE that I posted was incorrect. I have attached the correct one being used.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive