SQL LOAD DATA FROM FILE - limits in VALUE clause ?
II try to explore the new SQL LOAD DATA feature in SQL comparing it to SQL INSERT
I'm stuck at this point: INSERT INTO <table> (columns...) VALUES (.....)
allows having not just simple column references but also ALL Standard SQL FUNCTIONS (at least)
example:
INSERT INTO Test (ShortName,DOB) VALUES (SUBSTRING(Name,1,4),TO_DATE(displayDate,'MM-DD_YYY'))
This works perfectly.
BUT the same VALUE clause applied to LOAD DATA fails in various ways:
LOAD DATA FROM FILE '/irisrun/repo/LoadSQL.txt'
INTO Test (ShortName) VALUES (SUBSTRING(Name,3,4))
USING {"from":{"file":{"header":"1"}}}
Refused compilation by returning:
SQL Error Details SQLCODE=-1 IDENTIFIER expected, reserved word SUBSTRING found
and this repeats in a similar way with other SQL Standard FUNCTIONS
Question:
- Do iI miss some syntactical trick to use Standard SQL FUNCTIONS ?
- Is the VALUE clause of LOAD DATA just so narrow-minded allowing only column names ??
This would be a quite disappointing explanation.