DDL Support in Packaged Queries
You can execute DDL statements in a packaged query. Remember that packaged queries must follow the native query syntax. After the DDL statement is executed, data source introspection is not done automatically. Right click on the data source and choose Add/Remove Resources to manually introspect the data source.
Note: Packaged Queries must have an OUT parameter which is a cursor with at least one column. Click on the parameters tab of the packaged query and define the result cursor using the Design by Example option, before executing your packaged query.
Following are examples of the DDL syntax in a Packaged query. Refer to the section
Multiple SQL Execution Statements in a Packaged Query to understand the usage of the multipartseparator used in the examples below.
Example 1
Below is an example of the DDL usage in a packaged query created in the ds_orders data source.
<version 2> multipartseparator=;+;
CREATE TABLE IF NOT EXISTS tutorial.myorders
as select *
FROM tutorial.orders
; ;
select * from tutorial.myorders
Example 2
The following packaged query example shows the syntax to create a table that is defined based on an S3 file in the path s3a://customers/northbay/
CREATE TABLE `myTable_csv` using csv options ( path 's3a://customers/northbay/myTable.csv', header 'true' )
AS
SELECT
*
FROM
VALUES(CAST(0 AS INTEGER))
AS(`col`)
WHERE
1 = 0