Toad World® Forums

Reading Date as a variable from File in Automation

Hey Guys,

I am trying to automated one of my report using select to file activity.
I query i have only date as dynamic but want to read from excel/access file. So each time it is giving different type of errors when trying to export the data.
I tried using both :Variable and #Variable# to define the variable but seems it is not working.



4/22/2020 3:29:02 PM: Starting Automation Script
4/22/2020 3:29:02 PM: Setting up environment
4/22/2020 3:29:02 PM: Script run by Toad Data Point 5.1.2.166 as rvijay on JCPSI7HIJGH2
4/22/2020 3:29:02 PM: AppDataDir = C:\Users\rvijay\AppData\Roaming\Quest Software\Toad Data Point 5.1
4/22/2020 3:29:02 PM: Temp Dir = C:\Users\rvijay\AppData\Local\Temp
4/22/2020 3:29:02 PM: Build started
4/22/2020 3:29:02 PM: Compiling script
4/22/2020 3:29:02 PM: Start CompileWorkflow
4/22/2020 3:29:02 PM: Before Compile
4/22/2020 3:29:04 PM: Return from CompileWorkflow
4/22/2020 3:29:04 PM: Workflow Compiled without Errors
4/22/2020 3:29:04 PM: Workflow Compiled has warnings
4/22/2020 3:29:04 PM: Warning: No Exception Handler. To send an Email when there is an error, change the value of Email on Error to true and enter email values.
4/22/2020 3:29:04 PM: Build completed
4/22/2020 3:29:04 PM: Create Workflow instance
4/22/2020 3:29:04 PM: Started workflow instance
4/22/2020 3:29:04 PM: Begin execution script activities
4/22/2020 3:29:04 PM: Variable "ROOT_PATH" set to
4/22/2020 3:29:04 PM: Variable "RUN_MODE" set to Test
4/22/2020 3:29:04 PM: Scanning the script for prompt bind variables
4/22/2020 3:29:04 PM: Connecting to InputFile_Markdown
4/22/2020 3:29:04 PM: Variable "StartDate" final formatted value: 2/3/2020 12:00:00 AM
4/22/2020 3:29:04 PM: Connecting to InputFile_Markdown
4/22/2020 3:29:04 PM: Variable "EndDate" final formatted value: 3/6/2020 12:00:00 AM
4/22/2020 3:29:04 PM: Begin Export of File_1
4/22/2020 3:29:04 PM: Connection description = LPB1-SCAN.JCPENNEY.COM;MOMPODS (RVIJAY), RVIJAY
4/22/2020 3:29:04 PM: Connection node found
4/22/2020 3:29:04 PM: Connection to: LPB1-SCAN.JCPENNEY.COM;MOMPODS (RVIJAY), RVIJAY
4/22/2020 3:29:04 PM: Connection successful
4/22/2020 3:29:04 PM: Connection logon: RVIJAY
4/22/2020 3:29:04 PM: Last schema: RVIJAY
4/22/2020 3:29:04 PM: Default schema: RVIJAY
4/22/2020 3:29:04 PM: Auto Commit: False
4/22/2020 3:29:04 PM: SQL to execute: select th.store_num
, sum(item_ord_qty) as man_mkdn_count
, (sum(case when (TID.disc_reason_txt = 'IA1') and (TID.adj_type_code in ('4', '6', '14')) then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end)

  • sum(case when (TID.disc_reason_txt = 'IA1') and (TID.adj_type_code = '12') then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end)
  • sum(case when TID.disc_reason_txt like 'IA2%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end)
  • sum(case when TID.disc_reason_txt like 'IA3%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end)
  • sum(case when TID.disc_reason_txt like 'IA4%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end)
  • sum(case when TID.disc_reason_txt like 'IA5%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end)
  • sum(case when TID.disc_reason_txt like 'IA6%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end)
  • sum(case when TID.disc_reason_txt like 'IA7%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end)
  • sum(case when TID.disc_reason_txt like 'IA8%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end)
    +sum(case when (TID.disc_reason_txt like 'Disc' and TID.ADJ_TYPE_CODE = 1) THEN (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end)
  • SUM(case when (TID.DISC_REASON_TXT = 'Coupon Disc' and TID.ADJ_TYPE_CODE in (22,23)) then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end)) as TOTAL
    , sum(case when (TID.disc_reason_txt = 'IA1') and (TID.adj_type_code in ('4', '6', '14')) then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end) as ticket_wrong_regular
    , sum(case when (TID.disc_reason_txt = 'IA1') and (TID.adj_type_code = '12') then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end) as ticket_wrong_clearance
    , sum(case when TID.disc_reason_txt like 'IA2%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end) as PRICE_MATCH
    , sum(case when TID.disc_reason_txt like 'IA3%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end) as not_as_signed
    , sum(case when TID.disc_reason_txt like 'IA4%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end) as IMPERFECTION
    , sum(case when TID.disc_reason_txt like 'IA5%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end) as coupon_or_cert
    , sum(case when TID.disc_reason_txt like 'IA6%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end) as APPEASEMENT
    , sum(case when TID.disc_reason_txt like 'IA7%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end) as MARKDOWN_ZERO
    , sum(case when TID.disc_reason_txt like 'IA8%' then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end) as even_exchange
    , sum(case when (TID.disc_reason_txt like 'Disc' and TID.ADJ_TYPE_CODE = 1) THEN (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end) AS OTHER_ITM_MAN_ADJ
    , SUM(case when (TID.DISC_REASON_TXT = 'Coupon Disc' and TID.ADJ_TYPE_CODE in (22,23)) then (tid.starting_rtl_amt - tid.adj_rtl_amt) else 0 end) AS TRAN_WIDE_MAN_ADJ
    from JCPRMS.JCP_TRAN_HEAD TH
    INNER JOIN JCPRMS.JCP_TRAN_ITEM TI
    ON TH.JCP_TRAN_HEAD_SEQ = TI.JCP_TRAN_HEAD_SEQ
    AND TH.STORE_NUM = TI.STORE_NUM
    AND TH.PARTITIONING_DATE = TI.PARTITIONING_DATE
    INNER JOIN JCPRMS.JCP_TRAN_ITEM_DISC TID
    ON TI.STORE_NUM = TID.STORE_NUM
    AND TI.PARTITIONING_DATE = TID.PARTITIONING_DATE
    AND TI.JCP_TRAN_ITEM_SEQ = TID.JCP_TRAN_ITEM_SEQ
    where TH.PARTITIONING_DATE BETWEEN to_date(:StartDate,'MM/DD/YYY') AND to_date(:EndDate,'MM/DD/YYY')
    AND (TID.DISC_REASON_TXT LIKE 'IA%' or (TID.DISC_REASON_TXT = 'Disc' and TID.ADJ_TYPE_CODE = 1 AND TID.ADJ_MANUAL_CODE = 'Y') OR (TID.DISC_REASON_TXT = 'Coupon Disc' and TID.ADJ_TYPE_CODE in (22,23) AND TID.ADJ_MANUAL_CODE = 'Y'))
    and th.void_info_ind <> 'Y'
    and ti.ln_voided_ind <> 'Y'
    and th.store_num <> 2857
    and th.jcp_tran_head_seq not in (select jcp_tran_head_seq from jcp_tran_head where void_info_ind = 'Y' and PARTITIONING_DATE BETWEEN to_date(:StartDate,'MM/DD/YYY') AND to_date(:EndDate,'MM/DD/YYY'))
    group by th.store_num
    4/22/2020 3:29:04 PM: ValidateSuffix: Using base suffix:
    4/22/2020 3:29:04 PM: ValidateSuffix: Suffix is predefined literal - returning empty suffix instead
    4/22/2020 3:29:04 PM: Export Template: <Quest.Toad.ImportExport.ExportEngine></Quest.Toad.ImportExport.ExportEngine>
    4/22/2020 3:29:19 PM: 3:29:04 PM Thread (4) Adding Export Notification Subscriber {0}
    4/22/2020 3:29:19 PM: 3:29:04 PM Thread (4) Export Started [4/22/2020 3:29:04 PM]
    4/22/2020 3:29:19 PM: 3:29:04 PM Thread (4) ** START **
    4/22/2020 3:29:19 PM: 3:29:04 PM Thread (4) Export using connection: LPB1-SCAN.JCPENNEY.COM;MOMPODS (RVIJAY), RVIJAY
    4/22/2020 3:29:19 PM: 3:29:11 PM Thread (4) Export Wizard: Building object list
    4/22/2020 3:29:19 PM: 3:29:11 PM Thread (4) Build List done.
    4/22/2020 3:29:19 PM: 3:29:11 PM Thread (4) Exporting Data (1 of 1)
    4/22/2020 3:29:19 PM: 3:29:11 PM Thread (4) BEGIN Query :
    4/22/2020 3:29:19 PM: 3:29:11 PM Thread (4) Object SQL Query started.
    4/22/2020 3:29:19 PM: 3:29:18 PM Thread (20) InternalReadBackground - exception ORA-01858: a non-numeric character was found where a numeric was expected
    4/22/2020 3:29:19 PM: 3:29:18 PM Thread (20) InternalReadBackground - stack at Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()
    4/22/2020 3:29:19 PM: 3:29:18 PM Thread (6) DoneReadTreadWriter
    4/22/2020 3:29:19 PM: 3:29:18 PM Thread (6) DoneReadThreadWriter finally - adapter.RowsRead 0
    4/22/2020 3:29:19 PM: 3:29:19 PM Thread (4) END Query :
    4/22/2020 3:29:19 PM: 3:29:19 PM Thread (4) Object SQL Query finished with error: ORA-01858: a non-numeric character was found where a numeric was expected
    4/22/2020 3:29:19 PM: 3:29:19 PM Thread (4) at Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()
    4/22/2020 3:29:19 PM: 3:29:19 PM Thread (4) Export Finished [4/22/2020 3:29:19 PM]
    4/22/2020 3:29:19 PM: 3:29:19 PM Thread (4) Export time = 00:00:14.3782394
    4/22/2020 3:29:19 PM: Thread (4) Build Time 00:00:06.6611209
    4/22/2020 3:29:19 PM: Thread (4) Complete Time 00:00:14.3782394
    4/22/2020 3:29:19 PM: 3:29:19 PM Thread (4) ** END **
    4/22/2020 3:29:19 PM: File_1 - ORA-01858: a non-numeric character was found where a numeric was expected

4/22/2020 3:29:19 PM: ORA-01858: a non-numeric character was found where a numeric was expected
4/22/2020 3:29:19 PM: System.Exception - File_1 - ORA-01858: a non-numeric character was found where a numeric was expected

Inner exception:
System.ApplicationException - ORA-01858: a non-numeric character was found where a numeric was expected
at Quest.Toad.Workflow.Activities.Database.SelectToExcelActivity.Execute(ActivityExecutionContext executionContext)

4/22/2020 3:29:19 PM: Failed

Sharing the log file too in case of use!