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;
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.