I have some schema which came from a relational model that I’m playing
around with in q. Something like the following table definitions:
instrument:([id:`int$()] name:`symbol$(); … more fields here …);
symbology:([id:`int$()] name:`symbol$(); code:`symbol$());
instrumentsymbol:([id:`int$()] symbology:`symbology$();
instrument:`instrument$(); symbol:`symbol$());
I am then trying to build a list of instruments with 2 extra columns,
representing the symbol in two symbologies identified by their codes.
In SQL I would do something like this:
select i.name, is1.symbol, is2.symbol
from instrument i
inner join instrumentsymbol as is1 on (is1.instrumentfk = i.id)
inner join instrumentsymbol as is2 on (is2.instrumentfk = i.id)
inner join symbology as s1 on (is1.symbologyfk = s1.id)
inner join symbology as s2 on (is2.symbologyfk = s2.id)
where s1.code = ‘RIC’
and s2.code = ‘BBG’
I’m struggling to see how to do this in q. This is what I have so far:
select symbol by instrument.name from instrumentsymbol where
symbology.code in BBGRIC
but this gives me a name and then a list of symbols in the second
column, whereas I would like name, ric, bbg columns in the results.
> instrument:([id:`int$()] name:`symbol$(); … more fields here …);
> symbology:([id:`int$()] name:`symbol$(); code:`symbol$());
> instrumentsymbol:([id:`int$()]
symbology:`symbology$();instrument:`instrument$(); symbol:`symbol$());
>
> select symbol by instrument.name from instrumentsymbol where
symbology.code in `BBG`RIC
>
> but this gives me a name and then a list of symbols in the second
column, whereas I would like name, ric, bbg columns in the results.
Sorry it wasn’t very clear. Here is a working example:instrument:([id:int$()] name:symbol$(); exchange:symbol$());symbology:([id:int$()] name:symbol$(); code:symbol$());instrumentsymbol:([id:int$()] symbology:symbology$();instrument:instrument$(); symbol:symbol$());insert[symbology;(1; $“Bloomberg”; $"BBG")];insert[symbology;(2; $"International Securities IdentificationNumber"; $“ISIN”)];insert[symbology;(3; $“Reuters Instrument Code”; $"RIC")];insert[instrument;(1; $"Heating Oil #2 / Fuel Oil Aug 10"; $“XNYM”)];insert[instrument;(2; $“Heating Oil #2 / Fuel Oil Sep 10”; $"XNYM")];insert[instrumentsymbol;(1; 1; 1; $"HOQ0 Comdty")];insert[instrumentsymbol;(2; 1; 2; $"HOU0 Comdty")];insert[instrumentsymbol;(3; 3; 1; $"HOQ0")];insert[instrumentsymbol;(4; 3; 2; $"HOU0")];I then want to query and get a row back per instrument, with the bbgsymbol and ric symbol. So in SQL I would join instrument toinstrumentsymbol/symbology twice restricting on the appropriatesymbology code.The desired result set is:name bbg ricHeating Oil #2 / Fuel Oil Aug 10 HOQ0 Comdty HOQ0Heating Oil #2 / Fuel Oil Sep 10 HOU0 Comdty HOU0I appreciate the advice.Thanks,JonOn Oct 27, 3:35?am, Aaron Davies <aaron.dav...> wrote:> On Oct 26, 2011, at 10:35 AM, Jon wrote:>> > instrument:([id:int$()] name:symbol$(); ... more fields here ...);> > symbology:([id:int$()] name:symbol$(); code:symbol$());> > instrumentsymbol:([id:int$()] symbology:symbology$();instrument:instrument$(); symbol:symbol$());>> > select symbol by instrument.name from instrumentsymbol where symbology.code in BBGRIC>> > but this gives me a name and then a list of symbols in the second column, whereas I would like name, ric, bbg columns in the results.>> i take it ric & bbg are in instrument?>> instrument:([id:int$()] name:symbol$(); ric:symbol$(); bbg:symbol$())>> maybe>> select symbol,instrument.name,instrument.ric,instrument.bbg from instrumentsymbol where symbology.code inBBGRIC>> ?>> it would help if you gave more code and some sample data and desired result</aaron.dav…>