5 Replies Latest reply on Jan 9, 2019 12:04 AM by Gaz in Oz

    SQLCL Bridge syntax error sql

    Bill S3

      The sql works fine when run with sqldeveloper on the server; are there any sql limitations?

       

      BRIDGE ODS_ORDKEYS as "jdbc:oracle:thin:username/password@server/service.world"(WITH GETDATES AS ( SELECT to_date('2018-12-22','yyyy-mm-dd') - 7 start_dt, (to_date('2018-12-22','yyyy-mm-dd') + 3) + 86399 / 86400 end_dt FROM DUAL ) SELECT SLS.SLS_4_PART_KEY sls4pk_ods, cast(to_number( Trim(To_char(AUD.RTL_STOR_ID, '0009')) ||Trim(To_char(AUD.BSNS_DAY_DT, 'YYYYMMDD')) ||Trim(To_char(AUD.WRKSTN_ID, '00009')) ||Trim(To_char(AUD.REG_TRANS_NBR, '0000000009'))) as number(30)) sls4pk_fdm, LN_.LN_ITEM_NBR, LN_.VOID_FLG, ln_.item_id item_sku, LN_.XTND_AMT, AUD.RTL_STOR_ID, AUD.WRKSTN_ID, AUD.BSNS_DAY_DT, AUD.REG_TRANS_NBR, TPT.ORD_NBR, TPT.ORD_LN_KEY, SLS.SLS_AUDIT_RVSN_NBR, SLS.SLS_AUDIT_ACTN_CDE FROM SLS_ODS_SCH01.AUDIT_SLS_TRANS AUD inner join getdates g on 1=1 inner join SLS_ODS_SCH01.SLS_TRANS_ENC SLS on AUD.SLS_4_PART_KEY=SLS.SLS_4_PART_KEY inner join SLS_ODS_SCH01.LN_ITEM_DTL_ENC LN_ on SLS.SLS_TRANS_KEY=LN_.SLS_TRANS_KEY inner join SLS_ODS_SCH01.TPTY_ORD TPT on LN_.LN_ITEM_DTL_KEY=TPT.LN_ITEM_DTL_KEY WHERE AUD.partition_Col between g.start_dt and g.end_dt and sls.partition_col between g.start_dt and g.end_dt and ln_.partition_col between g.start_dt and g.end_dt and tpt.partition_col between g.start_dt and g.end_dt and nvl(tpt.ord_nbr,'~') <> '~');