HANA Newbie, why do my field names need to be quoted?

I'm just starting out on SAP HANA - testing the water.

My first move was to open an SQL Console in Eclipse Luna to run a simple query on the SAP Business One Demo Database.

I've found that "SELECT * FROM OCRD" works fine, but "SELECT CardCode, CardName FROM OCRD" results in an invalid field error.

Putting double quotes around field names results in a working query.

I have concerns as follows:

None of the SAP HANA examples indicate a requirement for double quotes in queries.
We have about 2000 lines of T-SQL queries to convert, and it would be best if I can use queries without the double quotes.

Are the double quotes a syntax thing, a JDBC thing, a SAP B1 thing, a Column Store thing or an Eclipse thing?

How do I run queries without the need for such quotation marks?

Thanks for any help in getting me up and running.

CREATE SYNTAX

CREATE COLUMN TABLE "SBODEMOGB"."OCRD" ("CardCode" NVARCHAR(15) NOT NULL ,
 "CardName" NVARCHAR(100),
 "CardType" CHAR(1) CS_FIXEDSTRING DEFAULT 'C',
 "GroupCode" SMALLINT CS_INT,
 "CmpPrivate" CHAR(1) CS_FIXEDSTRING DEFAULT 'C',
 "Address" NVARCHAR(100),
 "ZipCode" NVARCHAR(20),
 "MailAddres" NVARCHAR(100),
 "MailZipCod" NVARCHAR(20),
 "Phone1" NVARCHAR(20),
 "Phone2" NVARCHAR(20),
 "Fax" NVARCHAR(20),
 "CntctPrsn" NVARCHAR(90),
 "Notes" NVARCHAR(100),
 "Balance" DECIMAL CS_DECIMAL_FLOAT,
 "ChecksBal" DECIMAL CS_DECIMAL_FLOAT,
 "DNotesBal" DECIMAL CS_DECIMAL_FLOAT,
 "OrdersBal" DECIMAL CS_DECIMAL_FLOAT,
 "GroupNum" SMALLINT CS_INT DEFAULT -1,
 "CreditLine" DECIMAL CS_DECIMAL_FLOAT,
 "DebtLine" DECIMAL CS_DECIMAL_FLOAT,
 "Discount" DECIMAL CS_DECIMAL_FLOAT,
 "VatStatus" CHAR(1) CS_FIXEDSTRING DEFAULT 'Y',
 "LicTradNum" NVARCHAR(32),
 "DdctStatus" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "DdctPrcnt" DECIMAL CS_DECIMAL_FLOAT,
 "ValidUntil" LONGDATE CS_LONGDATE,
 "Chrctrstcs" INTEGER CS_INT,
 "ExMatchNum" INTEGER CS_INT,
 "InMatchNum" INTEGER CS_INT,
 "ListNum" SMALLINT CS_INT,
 "DNoteBalFC" DECIMAL CS_DECIMAL_FLOAT,
 "OrderBalFC" DECIMAL CS_DECIMAL_FLOAT,
 "DNoteBalSy" DECIMAL CS_DECIMAL_FLOAT,
 "OrderBalSy" DECIMAL CS_DECIMAL_FLOAT,
 "Transfered" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "BalTrnsfrd" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "IntrstRate" DECIMAL CS_DECIMAL_FLOAT,
 "Commission" DECIMAL CS_DECIMAL_FLOAT,
 "CommGrCode" SMALLINT CS_INT DEFAULT 0,
 "Free_Text" NCLOB MEMORY THRESHOLD 1000,
 "SlpCode" INTEGER CS_INT DEFAULT -1,
 "PrevYearAc" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "Currency" NVARCHAR(3),
 "RateDifAct" NVARCHAR(15),
 "BalanceSys" DECIMAL CS_DECIMAL_FLOAT,
 "BalanceFC" DECIMAL CS_DECIMAL_FLOAT,
 "Protected" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "Cellular" NVARCHAR(50),
 "AvrageLate" SMALLINT CS_INT,
 "City" NVARCHAR(100),
 "County" NVARCHAR(100),
 "Country" NVARCHAR(3),
 "MailCity" NVARCHAR(100),
 "MailCounty" NVARCHAR(100),
 "MailCountr" NVARCHAR(3),
 "E_Mail" NVARCHAR(100),
 "Picture" NVARCHAR(200),
 "DflAccount" NVARCHAR(50),
 "DflBranch" NVARCHAR(50),
 "BankCode" NVARCHAR(30) DEFAULT '-1',
 "AddID" NVARCHAR(64),
 "Pager" NVARCHAR(30),
 "FatherCard" NVARCHAR(15),
 "CardFName" NVARCHAR(100),
 "FatherType" CHAR(1) CS_FIXEDSTRING DEFAULT 'P',
 "QryGroup1" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup2" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup3" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup4" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup5" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup6" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup7" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup8" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup9" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup10" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup11" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup12" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup13" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup14" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup15" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup16" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup17" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup18" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup19" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup20" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup21" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup22" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup23" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup24" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup25" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup26" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup27" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup28" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup29" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup30" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup31" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup32" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup33" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup34" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup35" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup36" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup37" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup38" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup39" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup40" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup41" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup42" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup43" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup44" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup45" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup46" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup47" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup48" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup49" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup50" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup51" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup52" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup53" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup54" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup55" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup56" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup57" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup58" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup59" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup60" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup61" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup62" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup63" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "QryGroup64" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "DdctOffice" NVARCHAR(10),
 "CreateDate" LONGDATE CS_LONGDATE,
 "UpdateDate" LONGDATE CS_LONGDATE,
 "ExportCode" NVARCHAR(8),
 "DscntObjct" SMALLINT CS_INT DEFAULT -1,
 "DscntRel" CHAR(1) CS_FIXEDSTRING DEFAULT 'L',
 "SPGCounter" SMALLINT CS_INT DEFAULT 0,
 "SPPCounter" INTEGER CS_INT DEFAULT 0,
 "DdctFileNo" NVARCHAR(9),
 "SCNCounter" SMALLINT CS_INT,
 "MinIntrst" DECIMAL CS_DECIMAL_FLOAT,
 "DataSource" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "OprCount" INTEGER CS_INT,
 "ExemptNo" NVARCHAR(50),
 "Priority" INTEGER CS_INT DEFAULT -1,
 "CreditCard" SMALLINT CS_INT DEFAULT -1,
 "CrCardNum" NVARCHAR(64),
 "CardValid" LONGDATE CS_LONGDATE,
 "UserSign" SMALLINT CS_INT,
 "LocMth" CHAR(1) CS_FIXEDSTRING DEFAULT 'Y',
 "validFor" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "validFrom" LONGDATE CS_LONGDATE,
 "validTo" LONGDATE CS_LONGDATE,
 "frozenFor" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "frozenFrom" LONGDATE CS_LONGDATE,
 "frozenTo" LONGDATE CS_LONGDATE,
 "sEmployed" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "MTHCounter" INTEGER CS_INT,
 "BNKCounter" INTEGER CS_INT,
 "DdgKey" INTEGER CS_INT DEFAULT -1,
 "DdtKey" INTEGER CS_INT DEFAULT -1,
 "ValidComm" NVARCHAR(30),
 "FrozenComm" NVARCHAR(30),
 "chainStore" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "DiscInRet" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "State1" NVARCHAR(3),
 "State2" NVARCHAR(3),
 "VatGroup" NVARCHAR(8),
 "LogInstanc" INTEGER CS_INT DEFAULT 0,
 "ObjType" NVARCHAR(20) DEFAULT '2',
 "Indicator" NVARCHAR(2),
 "ShipType" SMALLINT CS_INT,
 "DebPayAcct" NVARCHAR(15),
 "ShipToDef" NVARCHAR(50),
 "Block" NVARCHAR(100),
 "MailBlock" NVARCHAR(100),
 "Password" NVARCHAR(32),
 "ECVatGroup" NVARCHAR(8),
 "Deleted" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "IBAN" NVARCHAR(50),
 "DocEntry" INTEGER CS_INT NOT NULL ,
 "FormCode" INTEGER CS_INT,
 "Box1099" NVARCHAR(20),
 "PymCode" NVARCHAR(15) DEFAULT '-1',
 "BackOrder" CHAR(1) CS_FIXEDSTRING DEFAULT 'Y',
 "PartDelivr" CHAR(1) CS_FIXEDSTRING DEFAULT 'Y',
 "DunnLevel" INTEGER CS_INT,
 "DunnDate" LONGDATE CS_LONGDATE,
 "BlockDunn" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "BankCountr" NVARCHAR(3),
 "CollecAuth" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "DME" NVARCHAR(5),
 "InstrucKey" NVARCHAR(30),
 "SinglePaym" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "ISRBillId" NVARCHAR(9),
 "PaymBlock" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "RefDetails" NVARCHAR(20),
 "HouseBank" NVARCHAR(30) DEFAULT '-1',
 "OwnerIdNum" NVARCHAR(15),
 "PyBlckDesc" INTEGER CS_INT DEFAULT -1,
 "HousBnkCry" NVARCHAR(3),
 "HousBnkAct" NVARCHAR(50),
 "HousBnkBrn" NVARCHAR(50),
 "ProjectCod" NVARCHAR(20),
 "SysMatchNo" INTEGER CS_INT DEFAULT -1,
 "VatIdUnCmp" NVARCHAR(32),
 "AgentCode" NVARCHAR(32),
 "TolrncDays" SMALLINT CS_INT,
 "SelfInvoic" CHAR(1) CS_FIXEDSTRING,
 "DeferrTax" CHAR(1) CS_FIXEDSTRING,
 "LetterNum" NVARCHAR(20),
 "MaxAmount" DECIMAL CS_DECIMAL_FLOAT,
 "FromDate" LONGDATE CS_LONGDATE,
 "ToDate" LONGDATE CS_LONGDATE,
 "WTLiable" CHAR(1) CS_FIXEDSTRING,
 "CrtfcateNO" NVARCHAR(20),
 "ExpireDate" LONGDATE CS_LONGDATE,
 "NINum" NVARCHAR(20),
 "AccCritria" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "WTCode" NVARCHAR(4),
 "Equ" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "HldCode" NVARCHAR(20),
 "ConnBP" NVARCHAR(15),
 "MltMthNum" INTEGER CS_INT,
 "TypWTReprt" CHAR(1) CS_FIXEDSTRING DEFAULT 'C',
 "VATRegNum" NVARCHAR(32),
 "RepName" NVARCHAR(15),
 "Industry" NCLOB MEMORY THRESHOLD 1000,
 "Business" NCLOB MEMORY THRESHOLD 1000,
 "WTTaxCat" NCLOB MEMORY THRESHOLD 1000,
 "IsDomestic" CHAR(1) CS_FIXEDSTRING DEFAULT 'Y',
 "IsResident" CHAR(1) CS_FIXEDSTRING DEFAULT 'Y',
 "AutoCalBCG" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "OtrCtlAcct" NVARCHAR(15),
 "AliasName" NCLOB MEMORY THRESHOLD 1000,
 "Building" NCLOB MEMORY THRESHOLD 1000,
 "MailBuildi" NCLOB MEMORY THRESHOLD 1000,
 "BoEPrsnt" NVARCHAR(15),
 "BoEDiscnt" NVARCHAR(15),
 "BoEOnClct" NVARCHAR(15),
 "UnpaidBoE" NVARCHAR(15),
 "ITWTCode" NVARCHAR(4),
 "DunTerm" NVARCHAR(25),
 "ChannlBP" NVARCHAR(15),
 "DfTcnician" INTEGER CS_INT,
 "Territory" INTEGER CS_INT,
 "BillToDef" NVARCHAR(50),
 "DpmClear" NVARCHAR(15),
 "IntrntSite" NVARCHAR(100),
 "LangCode" INTEGER CS_INT,
 "HousActKey" INTEGER CS_INT,
 "Profession" NVARCHAR(50),
 "CDPNum" SMALLINT CS_INT,
 "DflBankKey" INTEGER CS_INT,
 "BCACode" NVARCHAR(3),
 "UseShpdGd" CHAR(1) CS_FIXEDSTRING DEFAULT 'Y',
 "RegNum" NVARCHAR(32),
 "VerifNum" NVARCHAR(32),
 "BankCtlKey" NVARCHAR(2),
 "HousCtlKey" NVARCHAR(2),
 "AddrType" NVARCHAR(100),
 "InsurOp347" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "MailAddrTy" NVARCHAR(100),
 "StreetNo" NVARCHAR(100),
 "MailStrNo" NVARCHAR(100),
 "TaxRndRule" CHAR(1) CS_FIXEDSTRING DEFAULT 'D',
 "VendTID" INTEGER CS_INT,
 "ThreshOver" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "SurOver" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "VendorOcup" NVARCHAR(15),
 "OpCode347" CHAR(1) CS_FIXEDSTRING DEFAULT 'A',
 "DpmIntAct" NVARCHAR(15),
 "ResidenNum" CHAR(1) CS_FIXEDSTRING DEFAULT '1',
 "UserSign2" SMALLINT CS_INT,
 "PlngGroup" NVARCHAR(10),
 "VatIDNum" NVARCHAR(32),
 "Affiliate" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "MivzExpSts" CHAR(1) CS_FIXEDSTRING DEFAULT 'B',
 "HierchDdct" CHAR(1) CS_FIXEDSTRING DEFAULT 'Y',
 "CertWHT" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "CertBKeep" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "WHShaamGrp" CHAR(1) CS_FIXEDSTRING DEFAULT '1',
 "IndustryC" INTEGER CS_INT,
 "DatevAcct" INTEGER CS_INT,
 "DatevFirst" CHAR(1) CS_FIXEDSTRING DEFAULT 'Y',
 "GTSRegNum" NVARCHAR(20),
 "GTSBankAct" NVARCHAR(80),
 "GTSBilAddr" NVARCHAR(80),
 "HsBnkSwift" NVARCHAR(50),
 "HsBnkIBAN" NVARCHAR(50),
 "DflSwift" NVARCHAR(50),
 "AutoPost" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "IntrAcc" NVARCHAR(15),
 "FeeAcc" NVARCHAR(15),
 "CpnNo" INTEGER CS_INT,
 "NTSWebSite" SMALLINT CS_INT,
 "DflIBAN" NVARCHAR(50),
 "Series" SMALLINT CS_INT,
 "Number" INTEGER CS_INT,
 "EDocExpFrm" INTEGER CS_INT,
 "TaxIdIdent" CHAR(1) CS_FIXEDSTRING DEFAULT '3',
 "Attachment" NCLOB MEMORY THRESHOLD 1000,
 "AtcEntry" INTEGER CS_INT,
 "DiscRel" CHAR(1) CS_FIXEDSTRING DEFAULT 'L',
 "NoDiscount" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "SCAdjust" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "DflAgrmnt" INTEGER CS_INT,
 "GlblLocNum" NVARCHAR(50),
 "SenderID" NVARCHAR(50),
 "RcpntID" NVARCHAR(50),
 "MainUsage" INTEGER CS_INT,
 "SefazCheck" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 "SefazReply" NVARCHAR(254),
 "SefazDate" LONGDATE CS_LONGDATE,
 "DateFrom" LONGDATE CS_LONGDATE,
 "DateTill" LONGDATE CS_LONGDATE,
 "RelCode" NVARCHAR(2),
 "OKATO" NVARCHAR(11),
 "OKTMO" NVARCHAR(12),
 "KBKCode" NVARCHAR(20),
 "TypeOfOp" CHAR(1) CS_FIXEDSTRING,
 "OwnerCode" INTEGER CS_INT,
 "MandateID" NVARCHAR(35),
 "SignDate" LONGDATE CS_LONGDATE,
 "Remark1" INTEGER CS_INT,
 "ConCerti" NVARCHAR(20),
 "TpCusPres" INTEGER CS_INT DEFAULT 9,
 "RoleTypCod" NVARCHAR(2),
 "BlockComm" CHAR(1) CS_FIXEDSTRING DEFAULT 'N',
 PRIMARY KEY ("CardCode")) UNLOAD PRIORITY 5 AUTO MERGE;

Mark

Solution

In general you already have the answer in the comments. Quoted identifiers are case sensitive and unquoted identifiers are case insensitive - in a way. Unquoted identifiers are basically 'converted' to uppercase. So

CREATE COLUMN TABLE tab (id INT);

and

CREATE COLUMN TABLE "TAB" ("ID" INT);

are basically identical. And it also works vice versa. So

CREATE COLUMN TABLE "TAB" ("ID" INT);
SELECT id FROM tab;
SELECT Id FROM Tab;
SELECT "ID" FROM "TAB";

Will all work, however

SELECT "Id" FROM "Tab";

will not. So summarizing:

Yes, a_horse_with_no_name is very right, quoted identifiers are regarded case sensitive. However, when created all upper case, even when quoted, you won't need to use quotes all the time.

A good hint might be to look at the column titles that the HANA Studio show when displaying a result, they will give you a hint. If they are all uppercase the columns are basically case-insensitive otherwise you have to match the exact case with quotes.

Cheers, Goldfishslayer