Hello,

When I run the following SQL in Teradata SQL assistant, I brings back results. When I try to
run it in Web Focus, I get the following error

Code below


--------------------------------------------------------------------------------

No HTML Output!

--------------------------------------------------------------------------------


-SET EMPTYREPORT=ON;
-RUN
ENGINE SQLDBC SET DEFAULT_CONNECTION TDProdC
SQL SQLDBC
CREATE VOLATILE TABLE CMPN_SEG, NO LOG as (
SELECT
B.MKTG_CMPN_I
, B.MKTG_SEG_I
, B.SEG_SPLIT_SEQ_I
, B.CMPN_ANLS_PER_C
, B.CMPN_PTCP_ROLE_C
, B.CS_STR_RESP_IPRS_DLVR_Q AS DLVR_Q
, B.CS_PTCP_Q AS PTCP_Q
, B.CS_STR_TRAN_ADJD_A AS SLS_A
, B.CS_STR_STD_DEV_ADJD_A AS STD_SLS_A
, B.CS_STR_TRIP_Q AS TRIP_Q
, B.CS_STR_PRCH_PTCP_Q AS PRCH_Q
, B.CS_STR_STD_DEV_TRIP_Q AS STD_TRIPS
, B.CS_STR_UNIT_Q AS UNIT_Q
, B.CS_STR_STD_DEV_UNIT_Q AS STD_UNITS
, B.CS_STR_EXLV_REDM_TRIP_ADJD_A AS EXLV_REDM_SLS
, B.CS_STR_EXLV_REDM_Q AS EXLV_REDM_Q
, B.CS_STR_EXLV_REDM_ADJ_A AS EXLV_REDM_A
, B.CS_STR_EXLV_REDM_TRIP_Q AS EXLV_REDM_TRIPS
, B.CS_STR_EXLV_REDM_TRIP_UNIT_Q AS EXLV_REDM_UNITS
, B.CS_STR_EXLV_REDM_PTCP_Q AS EXLV_REDM_PTCP_Q
, B.CS_STR_RESP_IPRS_DLVR_Q AS IMP_Q
FROM CMP_ADW.MKTG_CMPN_DIM_V as C
INNER JOIN CMP_ADW.CMPN_SEG_FCT_V as B
ON C.MKTG_CMPN_I = B.MKTG_CMPN_I
WHERE C.CMPN_CODE_REF_I = '002971V014'
AND B.CMPN_ANLS_PER_C IN ('1','2')
AND B.CMPN_PTCP_ROLE_C IN ('TEST','NML')
)
WITH DATA
PRIMARY INDEX(MKTG_SEG_I)
ON COMMIT PRESERVE ROWS ;
END
SQL SQLDBC COMMIT WORK
END
-RUN
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HOLDC1 FORMAT SAME_DB
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
ENGINE SQLDBC SET DEFAULT_CONNECTION TDProdC
SQL SQLDBC
CREATE VOLATILE TABLE SEG_PERIOD, NO LOG as (
SELECT
B.MKTG_SEG_I
, B.SEG_SPLIT_SEQ_I
, B.CMPN_PTCP_ROLE_C
, A.DLVR_Q AS PRE_DLVR_Q
, B.DLVR_Q AS PRM_DLVR_Q
, A.PTCP_Q AS PRE_PTCP_Q
, B.PTCP_Q AS PRM_PTCP_Q
, A.SLS_A AS PRE_SLS_A
, B.SLS_A AS PRM_SLS_A
, A.STD_SLS_A AS PRE_STD_SLS_A
, B.STD_SLS_A AS PRM_STD_SLS_A
, A.TRIP_Q AS PRE_TRIP_Q
, B.TRIP_Q AS PRM_TRIP_Q
, A.PRCH_Q AS PRE_PRCH_Q
, B.PRCH_Q AS PRM_PRCH_Q
, A.STD_TRIPS AS PRE_STD_TRIPS
, B.STD_TRIPS AS PRM_STD_TRIPS
, A.UNIT_Q AS PRE_UNIT_Q
, B.UNIT_Q AS PRM_UNIT_Q
, A.STD_UNITS AS PRE_STD_UNITS
, B.STD_UNITS AS PRM_STD_UNITS
, B.EXLV_REDM_SLS AS PRM_EXLV_REDM_SLS
, B.EXLV_REDM_Q AS PRM_EXLV_REDM_Q
, B.EXLV_REDM_A AS PRM_EXLV_REDM_A
, B.EXLV_REDM_TRIPS AS PRM_EXLV_REDM_TRIPS
, B.EXLV_REDM_UNITS AS PRM_EXLV_REDM_UNITS
, B.EXLV_REDM_PTCP_Q AS PRM_EXLV_REDM_PTCP_Q
, B.IMP_Q AS PRM_IMP_Q
FROM CMPN_SEG as A
INNER JOIN CMPN_SEG as B
ON A.MKTG_SEG_I = B.MKTG_SEG_I
AND A.SEG_SPLIT_SEQ_I = B.SEG_SPLIT_SEQ_I
AND A.CMPN_PTCP_ROLE_C = B.CMPN_PTCP_ROLE_C
WHERE A.CMPN_ANLS_PER_C = '1'
AND B.CMPN_ANLS_PER_C = '2' )
WITH DATA
NO PRIMARY INDEX
ON COMMIT PRESERVE ROWS ;
END
SQL SQLDBC COMMIT WORK
END
-RUN
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HOLDC2 FORMAT SAME_DB
END
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
SET XRETRIEVAL=OFF
-EXIT



Need Help?

This message has been edited. Last edited by: <Kathryn Henning>,

Hi,

Welcome to Focal Point.

This is a friendly follow-up regarding your question - Reporting From teradata Volatile Tables - Web Focus 7.6.10

After searching the Focal Point Forum the following might be helpful:

Reporting From teradata Volatile Tables - Web Focus 7.6.10
[CLOSED] only an ET or null statement is legal after a DDL statement in WebFocus

[CLOSED] Using INSERT Statement in WebFocus


There does seem to be several posts related to "Volatile Tables" review the above and continue searching the forum.

Keep us posted on your search and if you have been able to find something that helps. If not let us know that too!.

Thank you for participating in Focal Point and sharing.

Kindest regards,
Tamra Colangelo
Focal Point Moderator