Article
Oliver Wilms · Jan 8 2m read

dataset-finance and my first experience with SQL LOAD DATA

I read under Topics and Rules of InterSystems Datasets Contest:

  • Class naming convention. Start the class names with: dc.data.your_name.class. E.g. if there is a dataset on trading data the class names could be: dc.data.finance.transaction, dc.data.finance.instrument.

I created a class “dc.data.finance.transaction”. I expected that it would not compile, because I remembered “Date” was a reserved word in SQL, and one of its properties was named “Date.”  To my surprise, the class compiled successfully. When I tried to execute query “select * from dc_data_finance.transaction,”  I got an error referencing reserved word TRANSACTION. I browsed tables listing on the left, found “dc.data.finance.transaction”, and dragged it into query textarea. I saw the query displayed like this:

SELECT

ID, "Date", "Check", Merchant, Category, SubCategory, Memo, Credit, Debit, BillPay, DebitCard, Account, Status

FROM dc_data_finance."transaction"

 

I tried SQL Load command from SQL Shell:

[SQL]USER>>LOAD DATA FROM FILE "/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv" into dc_data_finance."transaction"

 

ERROR #5540: SQLCODE: -1 Message:  Delimited identifier expected, delimited identifier containing invalid character '.' found^LOAD DATA FROM FILE "/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv"

I tried to use single quotes around my file:

[SQL]USER>>LOAD DATA FROM FILE '/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv' into dc_data_finance."transaction"

1.      LOAD DATA FROM FILE '/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv' into dc_data_finance."transaction"

 

[SQLCODE: <-1>:<Invalid SQL statement>]

[%msg: < IDENTIFIER expected, reserved word TRANSACTION found^ SELECT * FROM dc_data_finance . TRANSACTION>]

0 Rows Affected

statement prepare time(s)/globals/cmds/disk: 0.0091s/2,113/15,967/0ms

          execute time(s)/globals/cmds/disk: 0.1604s/48,014/304,365/5ms

                          cached query class: %sqlcq.USER.cls13

---------------------------------------------------------------------------

 

When I tried the SQL Load commands in Management Portal, I got similar error messages as previously in SQL Shell.

I shortened the filename from “finance-transactions-08-Jan-2022_08-25.csv” to “finance.csv” and then just “finance.”  I still got error because of TRANSACTION table name. Maybe it is time to recreate the transaction class avoiding reserved words as Table name and properties (Date and Check).

0
0 66
Discussion (2)2
Log in or sign up to continue

I created a new class called "dc.data.finance.transact" avoiding RESERVED words DATE, CHECK, and TRANSACTION. Then I ran this command in SQL Management Portal:

LOAD DATA FROM FILE '/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv' into dc_data_finance.transact

Row count: 76 Performance: 4.318 seconds  91420 global references 1837548 commands executed 3 disk read latency (ms)  Cached Query: %sqlcq.USER.cls23  Last update: 2022-01-09 00:04:31.471

Print

  76 row(s) affected