SQL - Create table from Select + user defined columns and values

Question

Currently I have the following SELECT statement:

CREATE TABLE  TEST AS
SELECT ROW_ID,
            PROM_INTEG_ID,
            INTEGRATION_ID,
            BILL_ACCNT_ID,
            SERV_ACCT_ID,
            CFG_STATE_CD   
FROM PRODUCTS
WHERE PROD_ID = 'TestProduct'
AND STATUS_CD = 'Active';

However I have to add some additional columns which do not exist in the PRODUCTS table and define them with my own name .e.g HIERARCHY

I tried using the WITH operand in my SQL query but it keeps failing as the syntax is wrong.

    CREATE TABLE  TEST AS
SELECT ROW_ID,
            PROM_INTEG_ID,
            INTEGRATION_ID,
            BILL_ACCNT_ID,
            SERV_ACCT_ID,
            CFG_STATE_CD   
            WITH
                  PRODUCT_HEIRARCHY varchar2(30)  'Test123Value'
FROM PRODUCT
WHERE PROD_ID = 'TestProduct'
AND STATUS_CD = 'Active';

So in summary, I want to pull in columns from an existing table as well as defining some of my own.

Any help appreciated

Answer 1

Just add the columns to the select:

CREATE TABLE TEST AS 
    SELECT ROW_ID, PROM_INTEG_ID, INTEGRATION_ID, BILL_ACCNT_ID, SERV_ACCT_ID, CFG_STATE_CD,
           CAST('Test123Value' AS VARCHAR2(30)) as PRODUCT_HIERARCHY
    FROM PRODUCTS
    WHERE PROD_ID = 'TestProduct' AND STATUS_CD = 'Active';

Note that the cast() is not necessary. But it is a good idea if you want the column to have a specific type.