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