Search code examples
sql-loader

How to use substring in when condition

We try to use a substring condition in when clause, using sql loader, but it seems that only fixed values are admitted

LOAD DATA
INFILE 'TO_IMPORT.csv'
APPEND INTO TABLE AUDIOCODE_TRACK_01
WHEN INTERFACE_SIP = 'SIP_Sud_BT' and DURATION= substr(:duration,1,1)='4'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
SBC_SESSION,
CALL_DIRECTION,
SOURCE_IP,
DESTINATION_IP,
SOURCE_PARTY,
DESTINATION_PARTY,
DURATION,
CALL_START,
CALL_END,
IPGROUP,
INTERFACE_SIP,
PROXY_SIP,
TRANSFERRED
)

this generate that error :

SQL*Loader-350: Syntax error at line 4.
Expecting quoted string or hex identifier, found "substr".
WHEN INTERFACE_SIP = 'SIP_Sud_BT' and DURATION= substr(:duration,1,1)=
Solution

  • As per documentation there is no substr even worse it's a reserved word

    https://docs.oracle.com/database/121/SUTIL/GUID-4E710755-36CA-4F2D-AFFF-E8A707FF3EBB.htm#SUTIL1448

    If you can ask the data provider of data to reorder the fields and put DURATION to the beginning. Then you can use the range_start:range_end syntax

    LOAD WHEN (1:1) = '4'

    Otherwise you have to filter afterwards. But this can lead to other issues when you have different format dependent on first digit of duration. 3rd solution would be a preprocessor which filters/changes the data.