· Jan 12, 2022

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)

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
   IDENTIFIER expected, reserved word SUBSTRING found

and this repeats in a similar way with other SQL Standard FUNCTIONS

- 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. 

Product version: IRIS 2021.2
$ZV: IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2021.2 (Build 617U) Thu Dec 9 2021 15:00:18 UTC
Discussion (3)1
Log in or sign up to continue