Error: Field not found - <CUSTOMERCODE>

[vireshkolagimat]

Hi All,

I've scheduled task which extracts the data from database and saves it in qvd format for every two hours (incremental reload).

The problem is every now and then i am getting the error as field not found but actually the field is available in the script. 

Please find the attached log file for the reference. 

Thanks for the support.

Regards,

Viresh

28/02/2019 8:48:30 AM: Execution started.
28/02/2019 8:48:30 AM: QlikView Version:11.20.13507.0
28/02/2019 8:48:30 AM: CPU Target x64
28/02/2019 8:48:30 AM: Operating System Windows 7 Professional Service Pack 1 (64 bit edition)
28/02/2019 8:48:30 AM: Wow64 mode Not using Wow64
28/02/2019 8:48:30 AM: MDAC Version 6.1.7601.17514
28/02/2019 8:48:30 AM: MDAC Full Install Version 6.1.7601.17514
28/02/2019 8:48:30 AM: PreferredCompression 2
28/02/2019 8:48:30 AM: EnableParallelReload 1
28/02/2019 8:48:30 AM: ParallelizeQvdLoads 1
28/02/2019 8:48:30 AM: AutoSaveAfterReload 0
28/02/2019 8:48:30 AM: BackupBeforeReload 0
28/02/2019 8:48:30 AM: EnableFlushLog 0
28/02/2019 8:48:30 AM: SaveInfoWhenSavingFile 0
28/02/2019 8:48:30 AM: UserLogfileCharset 1200
28/02/2019 8:48:30 AM: OdbcLoginTimeout -1
28/02/2019 8:48:30 AM: OdbcConnectionTimeout -1
28/02/2019 8:48:30 AM: ScriptWantsDbWrite false
28/02/2019 8:48:30 AM: ScriptWantsExe false
28/02/2019 8:48:30 AM: LogFile CodePage Used: 1200
28/02/2019 8:48:30 AM: Reload Executed By MDCOMAN\MDCIT
28/02/2019 8:48:30 AM: Process Executing: QVB
28/02/2019 8:48:30 AM: Process ID: 109280
28/02/2019 8:48:30 AM: 0002 SET ThousandSep=','
28/02/2019 8:48:30 AM: 0003 SET DecimalSep='.'
28/02/2019 8:48:30 AM: 0004 SET MoneyThousandSep=','
28/02/2019 8:48:30 AM: 0005 SET MoneyDecimalSep='.'
28/02/2019 8:48:30 AM: 0006 SET MoneyFormat='$#,##0.00;($#,##0.00)'
28/02/2019 8:48:30 AM: 0007 SET TimeFormat='h:mm:ss TT'
28/02/2019 8:48:30 AM: 0008 SET DateFormat='DD/MM/YYYY'
28/02/2019 8:48:30 AM: 0009 SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT'
28/02/2019 8:48:30 AM: 0010 SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'
28/02/2019 8:48:30 AM: 0011 SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'
28/02/2019 8:48:30 AM: 0013 ODBC CONNECT*XUserId*XPassword*
28/02/2019 8:48:31 AM: 0015 sql alter session set nls_language = 'AMERICAN'
28/02/2019 8:48:31 AM: 0019 set _qvd_path = '..\Data\Qvds\Raw_Qvds\'
28/02/2019 8:48:31 AM: 0020 set _qvd_name = 'SalesHistory'
28/02/2019 8:48:31 AM: 0021 set vKeyField = ORDER_LINE_ID
28/02/2019 8:48:31 AM: 0022 set vUpdateField = INV_LAST_UPDATE_DATE
28/02/2019 8:48:31 AM: 0026 let vFileLen = FileSize('..\Data\Qvds\Raw_Qvds\SalesHistory.qvd')
28/02/2019 8:48:31 AM: 0028 trace '>> QVD Size: '11889834
28/02/2019 8:48:31 AM: 0028 '>> QVD Size: '11889834

28/02/2019 8:48:31 AM: 0032 if len('11889834') = 0 then
28/02/2019 8:48:31 AM: 0137 TRACE 'Incremental reload begins...'
28/02/2019 8:48:31 AM: 0137 'Incremental reload begins...'

28/02/2019 8:48:31 AM: 0141 set vKeyQVD = 'OrdersMaxKey_history'
28/02/2019 8:48:31 AM: 0143 OrdersMaxKey_history:
28/02/2019 8:48:31 AM: 0144 load
28/02/2019 8:48:31 AM: 0145 date(MaxKey) as MaxKey
28/02/2019 8:48:31 AM: 0146 from
28/02/2019 8:48:31 AM: 0147 ..\Data\Qvds\Raw_Qvds\OrdersMaxKey_history.qvd(qvd)
28/02/2019 8:48:31 AM: 1 fields found: MaxKey, 1 lines fetched
28/02/2019 8:48:31 AM: 0149 let vMaxKey = Peek('MaxKey')
28/02/2019 8:48:31 AM: 0151 trace '>>> Last Max Key value stored: ' & 28/02/2019
28/02/2019 8:48:31 AM: 0151 '>>> Last Max Key value stored: ' & 28/02/2019

28/02/2019 8:48:31 AM: 0153 drop table OrdersMaxKey_history
28/02/2019 8:48:31 AM: 0157 SalesHistory:
28/02/2019 8:48:31 AM: 0158 LOAD
28/02/2019 8:48:31 AM: 0159 CUSTOMERCODE as CustomerCode,
28/02/2019 8:48:31 AM: 0160 WAREHOUSECODE as WarehouseCode,
28/02/2019 8:48:31 AM: 0161 PRODUCTCODE as ProductCode,
28/02/2019 8:48:31 AM: 0162 INVOICENO as InvoiceNo,
28/02/2019 8:48:31 AM: 0163 INVOICEDATE as InvoiceDate,
28/02/2019 8:48:31 AM: 0164 SALESQTY as SalesQty,
28/02/2019 8:48:31 AM: 0165 SALESVALUE as SalesValue,
28/02/2019 8:48:31 AM: 0166 SALESCOST as SalesCost,
28/02/2019 8:48:31 AM: 0167 SALESLISTVALUE as SalesListValue,
28/02/2019 8:48:31 AM: 0168 SALESCOMMISSION as SalesCommission,
28/02/2019 8:48:31 AM: 0169 SALESCUSTOMERGROUP as SalesCustomerGroup,
28/02/2019 8:48:31 AM: 0170 SALESTERRITORY as SalesTerritory,
28/02/2019 8:48:31 AM: 0171 REGIONCODE as RegionCode,
28/02/2019 8:48:31 AM: 0172 CHANNELCODE as ChannelCode,
28/02/2019 8:48:31 AM: 0173 SALESMANCODE as SalesmanCode,
28/02/2019 8:48:31 AM: 0174 ROUTECODE as RouteCode,
28/02/2019 8:48:31 AM: 0175 SALESUNIT as SalesUnit,
28/02/2019 8:48:31 AM: 0176 SALESUNITFACTOR as SalesUnitFactor,
28/02/2019 8:48:31 AM: 0177 RETURNTYPE as ReturnType,
28/02/2019 8:48:31 AM: 0178 ORDER_TYPE,
28/02/2019 8:48:31 AM: 0179 ORDER_LINE_ID,
28/02/2019 8:48:31 AM: 0180 ORDER_NUMBER,
28/02/2019 8:48:31 AM: 0181 ORDEREDDATE as OrderedDate,
28/02/2019 8:48:31 AM: 0182 floor(INV_LAST_UPDATE_DATE) as INV_LAST_UPDATE_DATE
28/02/2019 8:48:31 AM: 0183 SQL
28/02/2019 8:48:31 AM: 0184 SELECT
28/02/2019 8:48:31 AM: 0185 CUSTOMERCODE,
28/02/2019 8:48:31 AM: 0186 WAREHOUSECODE,
28/02/2019 8:48:31 AM: 0187 WAREHOUSENAME,
28/02/2019 8:48:31 AM: 0188 ORDER_NUMBER,
28/02/2019 8:48:31 AM: 0189 PRODUCTCODE,
28/02/2019 8:48:31 AM: 0190 INVOICENO,
28/02/2019 8:48:31 AM: 0191 INVOICEDATE,
28/02/2019 8:48:31 AM: 0192 SALESCOST,
28/02/2019 8:48:31 AM: 0193 SALESLISTVALUE,
28/02/2019 8:48:31 AM: 0194 SALESCOMMISSION,
28/02/2019 8:48:31 AM: 0195 SALESCUSTOMERGROUP,
28/02/2019 8:48:31 AM: 0196 SALESTERRITORY,
28/02/2019 8:48:31 AM: 0197 REGIONCODE,
28/02/2019 8:48:31 AM: 0198 CHANNELCODE,
28/02/2019 8:48:31 AM: 0199 SALESMANCODE,
28/02/2019 8:48:31 AM: 0200 ROUTECODE,
28/02/2019 8:48:31 AM: 0201 SALESUNIT,
28/02/2019 8:48:31 AM: 0202 SALESUNITFACTOR,
28/02/2019 8:48:31 AM: 0203 RETURNTYPE,
28/02/2019 8:48:31 AM: 0204 SALESQTY,
28/02/2019 8:48:31 AM: 0205 SALESVALUE,
28/02/2019 8:48:31 AM: 0206 ORDER_TYPE,
28/02/2019 8:48:31 AM: 0207 ORDER_LINE_ID,
28/02/2019 8:48:31 AM: 0208 ORDEREDDATE,
28/02/2019 8:48:31 AM: 0209 INV_LAST_UPDATE_DATE
28/02/2019 8:48:31 AM: 0210 FROM APPS.XXEGC_QV_SALES_HISTORY_V
28/02/2019 8:48:31 AM: 0211 where DIVISION_CODE='CA MDC OU' and INV_LAST_UPDATE_DATE >= trunc(to_date('28/02/2019','DD/MM/YYYY'))
28/02/2019 9:03:33 AM: Error: Field not found - <CUSTOMERCODE>
28/02/2019 9:03:33 AM: General Script Error
28/02/2019 9:03:33 AM: Execution Failed
28/02/2019 9:03:33 AM: Execution finished.

[Omarov]

Same issue.
How were you able to solve this?
Thanks !

[Or]

This can sometimes be the result of a query that fails to run, but for whatever reason Qlik and Oracle DB get their signals crossed on the error. This seems to happen most often when the issue is either single-row subquery returns more than one row, or a type conversion error with TO_CHAR, TO_NUMBER, or TO_DATE. Running the query in a different tool, such as PL/SQL, may help in isolating any potential errors.

Of course, it could be something else entirely. But I've noticed that popping up.

[marcus_sommer]

The error-message may be misleading by showing a subsequent error and not the real cause. There are probably various possibilities for such behaviour, for example if no error is sent from the data-base and/or the driver and Qlik displayed then the first error on its side - missing the first field from the load-statement. It might be any locking of the data-base, timeouts, lost connection, ...

Helpful would be to enable the error-messages within the data-base and driver. In this regard also to use the ERRORMODE feature of Qlik to get more details and/or to repeat the task or branching into any error-handling.

Further useful could also be to implement a pre-load of one records but all fields in beforehand which is then used to check the exists of fields and/or the data-type/data-interpretation.

Before going in this direction I would try to look into the log-files of the data-base and driver - maybe the cause is directly written.