NUMERIC(11) data type equivalent?

Community Support
1

Content-Disposition: inlineHello,

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

2

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:

  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@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?

Thanks,
Yuva

3

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>

4

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>

5

Thanks.

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$()&nbsp;&nbsp; ;EX_DESC:()&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ;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?

Regards,
Yuva