Robert Cemper · 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
0 123
Discussion (3)1
Log in or sign up to continue

OK!  I just learned from @Benjamin De Boe  on Discord

LOAD DATAQ is a DDL statement!
Not a SQL statement.

So Goodbye to SQL-Functions, ClassMethod_Procedures, and whatever nice stuff.
Therefore VALUES clause is just a borrowed syntax without deeper meaning! 

I start to doubt the advantage of this feature.


hang on there. What I said on discord is only that the DDL nature of this command prevents it from supporting query parameters. We are looking into expression support inside the VALUES clause. You still have time to stress the importance of this in the survey ;-)

Thanks for the clarification.
So I have some hope