creating a view on a SQL query starting with "WITH" clause

Database
Teradata Employee

Re: creating a view on a SQL query starting with "WITH" clause

WITH clause is allowed in create VIEW starting in Teradata 16.
Enthusiast

Re: creating a view on a SQL query starting with "WITH" clause

Yes.  THANK YOU!

Enthusiast

Re: creating a view on a SQL query starting with "WITH" clause

example workaround please..... ;-)

Visitor

Re: creating a view on a SQL query starting with "WITH" clause

Can someone please help me converting this to a SQL, which I can then create a view from? (TD 15.10):

 

[Edit: I understand I can create a separate view for SRCI_SRC and then refer that from the main query, however I looking for a way to do so in one step, not two.]

 

WITH SRCI_SRC AS
(
  SELECT
    EVENT_ID,
    OBJECT_ID,
    EVENT_TYPE, 
    EVENT_REFERENCE,
    EVENT_ATTRIBUTE4, 
    DW_STRT_TS,
    DW_END_TS,
    REC_DEL_IND,
    PRCS_EXEQ_ID,
    UPDT_PRCS_EXEQ_ID,
    DATA_QUAL_ID
  FROM BDWDEV1VR_SRCI.SI001_COMMERCIAL_EVENT
  WHERE EVENT_TYPE LIKE 'PO_%'
    AND PRCS_EXEQ_ID = 10013462
)
SELECT 
  SRCI_SRC.OBJECT_ID,
  SRCI_SRC.EVENT_TYPE, 
  SRCI_SRC.EVENT_REFERENCE,
  STRTOK(SPLITS.TOKEN,'~',1)COS_ORD_LN_ID,
  STRTOK(SPLITS.TOKEN,'~',2)COS_SOO_EVENT_ITEM_QTY,
  DW_STRT_TS,
  DW_END_TS,
  REC_DEL_IND,
  PRCS_EXEQ_ID,
  UPDT_PRCS_EXEQ_ID,
  DATA_QUAL_ID
FROM SRCI_SRC,TABLE
  (STRTOK_SPLIT_TO_TABLE(SRCI_SRC.EVENT_ID,STRTOK(SRCI_SRC.EVENT_ATTRIBUTE4,':',2),',')
  RETURNS
  (
    OUTKEY   INTEGER,
    TOKENNUM INTEGER,
    TOKEN    VARCHAR(256) CHARACTER SET UNICODE)
  )AS SPLITS
WHERE SRCI_SRC.EVENT_ID = SPLITS.OUTKEY;

 

Highlighted
Junior Contributor

Re: creating a view on a SQL query starting with "WITH" clause

Can't be done before TD16, sorry.

Teradata Employee

Re: creating a view on a SQL query starting with "WITH" clause

Any non-recursive WITH expression can be rewritten as a derived table. Wrap the query from the WITH in parens and place it in the FROM clause of the main queryas a derived table. This will make it a single query without the need for a view or two separate queries.