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.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 282 Workbench
- 3 Cloud Amplifier
- 4 Federated
- 2.8K Transform
- 86 SQL DataFlows
- 548 Datasets
- 2.2K Magic ETL
- 3.2K Visualize
- 2.3K Charting
- 544 Beast Mode
- App Studio
- 26 Variables
- 566 Automate
- 134 Apps
- 411 APIs & Domo Developer
- 21 Workflows
- DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 345 Distribute
- 87 Domo Everywhere
- 257 Scheduled Reports
- 1 Software Integrations
- 85 Manage
- 84 Governance & Security
- 8 Product Release Questions
- Community Forums
- 41 Getting Started
- 27 Community Member Introductions
- 81 Community Announcements
- 4.8K Archive