Content-Disposition: inline Hmm… I am having trouble with this table and the inserts:
SQL version of EXCHANGE table columns:
EX_ID CHAR(6) NOT NULL PRIMARY KEY,
EX_NAME CHAR(100) NOT NULL,
EX_NUM_SYMB NUMERIC(6) NOT NULL,
EX_OPEN NUMERIC(4) NOT NULL,
EX_CLOSE NUMERIC(4) NOT NULL,
EX_DESC CHAR(150),
EX_AD_ID NUMERIC(11) NOT NULL
My conversion to q table:
EXCHANGE:([EX_ID:`symbol$()]EX_NAME:`symbol$() ;EX_NUM_SYMB:`int$();EX_OPEN:`int$() ;EX_CLOSE:`int$() ;EX_DESC:`symbol$() ;EX_AD_ID:`long$() )
SQL insert statement:
insert into EXCHANGE values(“NASDAQ”,“NASDAQ (National Association of Security Dealers and Quotations)”,848,930,1600,“Simulates the NASDAQ”,4300000002);
My insert then as :
insert [EXCHANGE;(NASDAQ;NASDAQ National Association of Security Dealers and Quotations ;848;930;1600;Simulates the NASDAQ;4300000002)]
The insert fails in multiple places:
`4300000002 \cannot place this into a long type, auto discovery fails, symbol works but this is not a varchar type!!
`NASDAQ National Association of Security Dealers and Quotations \guess that symbol cannot have spaces
`Simulates the NASDAQ \probably same reason as 2
So, what would be a reasonable schema translation from SQL to q and accordingly, what should the insert look like?
Thanks,
Yuva
On Thu, Jul 3, 2008 at 3:50 PM, Yuvaraj Athur Raghuvir <yuvaraj.a.r@gmail.com> wrote:
Hello,
I want to persist a NUMERIC(11) data. I used long as the type for the q table.
When I append a record with a value 4300000002, I get an error in the insert
q)`4300000002
What is the equivalent data type in q in this case?
symbols can have spaces, but in that case you’ll have to create themfrom a stringthisis fine, but you have to do$“this and that"or"S”$"this and that"to get one symbol (cast a string to symbol)you’d be better off keeping the long char fields as text, and onlykeeping the id as a symbolsymbols are meant for shortish strings that occur frequently - likestock symbols2008/7/4 Yuvaraj Athur Raghuvir <yuvaraj.a.r>:> Hmm… I am having trouble with this table and the inserts:> SQL version of EXCHANGE table columns:> EX_ID CHAR(6) NOT NULL PRIMARY KEY,> EX_NAME CHAR(100) NOT NULL,> EX_NUM_SYMB NUMERIC(6) NOT NULL,> EX_OPEN NUMERIC(4) NOT NULL,> EX_CLOSE NUMERIC(4) NOT NULL,> EX_DESC CHAR(150),> EX_AD_ID NUMERIC(11) NOT NULL> My conversion to q table:> EXCHANGE:([EX_ID:`symbol$()]EX_NAME:`symbol$()> ;EX_NUM_SYMB:`int$();EX_OPEN:`int$() ;EX_CLOSE:`int$()> ;EX_DESC:`symbol$() ;EX_AD_ID:`long$() )>> SQL insert statement:> insert into EXCHANGE values(“NASDAQ”,“NASDAQ (National Association of> Security Dealers and Quotations)”,848,930,1600,“Simulates the> NASDAQ”,4300000002);>> My insert then as :> insert [`EXCHANGE;(`NASDAQ;`NASDAQ National Association of Security Dealers> and Quotations ;848;930;1600;`Simulates the NASDAQ;4300000002)]>> The insert fails in multiple places:> 1) `4300000002 \cannot place this into a long type, auto discovery fails,> symbol works but this is not a varchar type!!> 2) `NASDAQ National Association of Security Dealers and Quotations \guess> that symbol cannot have spaces> 3) `Simulates the NASDAQ \probably same reason as 2>> So, what would be a reasonable schema translation from SQL to q and> accordingly, what should the insert look like?>> Thanks,> Yuva>>>> On Thu, Jul 3, 2008 at 3:50 PM, Yuvaraj Athur Raghuvir> <yuvaraj.a.r> wrote:>>>> Hello,>>>> I want to persist a NUMERIC(11) data. I used long as the type for the q>> table.>>>> When I append a record with a value 4300000002, I get an error in the>> insert>> q)`4300000002>>>> What is the equivalent data type in q in this case?>>>> Thanks,>> Yuva>>>>>>>>> >></yuvaraj.a.r></yuvaraj.a.r>
schema and insert:q)EXCHANGE:([EX_ID:`symbol$()]EX_NAME:();EX_NUM_SYMB:`int$();EX_OPEN:`int$();EX_CLOSE:`int$();EX_DESC:();EX_AD_ID:`long$())q)`EXCHANGE upsert(`NASDAQ;“NASDAQ National Association of SecurityDealers and Quotations”;848;930;1600;“Simulates theNASDAQ”;4300000002j)`EXCHANGEq)EXCHANGEEX_ID | EX_NAME EX_NUM_SYMB EX_OPEN EX_CLOSE EX_DESC EX_AD_ID------| --------------------------------------------------------------------------------------------------------------------------------NASDAQ| “NASDAQ National Association of Security Dealers andQuotations” 848 930 1600 "Simulates the NASDAQ"4300000002q)meta EXCHANGEc | t f a-----------| -----EX_ID | sEX_NAME | CEX_NUM_SYMB| iEX_OPEN | iEX_CLOSE | iEX_DESC | CEX_AD_ID | jq)2008/7/4 Yuvaraj Athur Raghuvir <yuvaraj.a.r>:> Hmm… I am having trouble with this table and the inserts:> SQL version of EXCHANGE table columns:> EX_ID CHAR(6) NOT NULL PRIMARY KEY,> EX_NAME CHAR(100) NOT NULL,> EX_NUM_SYMB NUMERIC(6) NOT NULL,> EX_OPEN NUMERIC(4) NOT NULL,> EX_CLOSE NUMERIC(4) NOT NULL,> EX_DESC CHAR(150),> EX_AD_ID NUMERIC(11) NOT NULL> My conversion to q table:> EXCHANGE:([EX_ID:`symbol$()]EX_NAME:`symbol$()> ;EX_NUM_SYMB:`int$();EX_OPEN:`int$() ;EX_CLOSE:`int$()> ;EX_DESC:`symbol$() ;EX_AD_ID:`long$() )>> SQL insert statement:> insert into EXCHANGE values(“NASDAQ”,“NASDAQ (National Association of> Security Dealers and Quotations)”,848,930,1600,“Simulates the> NASDAQ”,4300000002);>> My insert then as :> insert [`EXCHANGE;(`NASDAQ;`NASDAQ National Association of Security Dealers> and Quotations ;848;930;1600;`Simulates the NASDAQ;4300000002)]>> The insert fails in multiple places:> 1) `4300000002 \cannot place this into a long type, auto discovery fails,> symbol works but this is not a varchar type!!> 2) `NASDAQ National Association of Security Dealers and Quotations \guess> that symbol cannot have spaces> 3) `Simulates the NASDAQ \probably same reason as 2>> So, what would be a reasonable schema translation from SQL to q and> accordingly, what should the insert look like?>> Thanks,> Yuva>>>> On Thu, Jul 3, 2008 at 3:50 PM, Yuvaraj Athur Raghuvir> <yuvaraj.a.r> wrote:>>>> Hello,>>>> I want to persist a NUMERIC(11) data. I used long as the type for the q>> table.>>>> When I append a record with a value 4300000002, I get an error in the>> insert>> q)`4300000002>>>> What is the equivalent data type in q in this case?>>>> Thanks,>> Yuva>>>>>>>>> >></yuvaraj.a.r></yuvaraj.a.r>
One puzzling point is why upsert succeeds but insert fails. The situation is like follows:
q) EXCHANGE:([EX_ID:()]EX_NAME:() ;EX_NUM_SYMB:int$();EX_OPEN:int$() ;EX_CLOSE:int$() ;EX_DESC:() ;EX_AD_ID:long$() ) \ auto discovery of type based on first insert
q)meta EXCHANGE
c
t f a
EX_ID
EX_NAME
EX_NUM_SYMB
i
EX_OPEN
i
EX_CLOSE
i
EX_DESC
EX_AD_ID
j
q) insert [EXCHANGE;(NASDAQ;“NASDAQ (National Association of Security Dealers and Quotations)”;848;930;1600;“Simulates the NASDAQ”;4300000002j)]
'type
q) EXCHANGE insert (NASDAQ;“NASDAQ (National Association of Security Dealers and Quotations)”;848;930;1600;“Simulates the NASDAQ”;4300000002j)
`type
q) EXCHANGE upsert (NASDAQ;“NASDAQ (National Association of Security Dealers and Quotations)”;848;930;1600;“Simulates the NASDAQ”;4300000002j)
q) `EXCHANGE
So, would I be right in saying that upsert allows for type discovery but insert does not? Further, as a q idiom of working with tables, is upsert preferred over insert?