i2b2 Developer's Forum
Space shortcuts
Space Tools

Versions Compared

Old Version 2

changes.mady.by.user Shawn Murphy

Saved on

compared with

New Version 3

changes.mady.by.user Shawn Murphy

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Wiki Markup
\\
<span style="color: #0000ff">select</span> concept_cd <span style="color: #0000ff">from</span> concept_dimension <span style="color: #0000ff">where</span> concept_path <span style="color: #808080">LIKE</span> <span style="color: #ff0000">'\i2b2\Diagnoses\Circulatory system (390-459)%'</span>\\
\\  !worddava449709682f22ca20463e0ba3e24c1ae.png|height=309,width=619!\\
\\
The result of this query is a list of concept_cds to query against the fact table.  The data repository cell uses this data to find a set of patients having a fact entry for this list of concept codes.  This patient set finder query is as follows:
\\
<span style="color: #0000ff">select</span> patient_num <span style="color: #0000ff">from</span> \[c_facttablename\] <span style="color: #0000ff">where</span> \[c_facttablecolumnname\] <span style="color: #808080">IN</span>
<span style="color: #808080">(</span><span style="color: #0000ff">select</span> \[c_facttablecolumnname\] <span style="color: #0000ff">from</span> \[c_tablename\] <span style="color: #0000ff">where</span> \[c_columnname\] \[c_operator\] \[c_dimcode\]<span style="color: #808080">)</span>\\

...

Operator

Dimcode Format

Sample Metadata XML

SQL

IN

('val1','val2','val3') or 'val1','val2','val3'

The open and close braces enclosing the list of date values are optonal.

For SQLServer:
<concept>
<facttablecolumn>patient_num </facttablecolumn>
<tablename>visit_dimension</tablename>
<columnname>start_date</columnname>
<columndatatype>D</columndatatype>
<operator>IN</operator>
<dimcode>'2010-10-10 13:30:00'
, '2009-09-10 13:30:00'
</dimcode>
..
</concept>

SELECT patient_num
FROM visit_dimension
WHERE start_date
IN ('2010-10-10 13:30:00', '2009-09-10 13:30:00')

All date operators (= , > , < , <> , BETWEEN)

Follows the database specific value format related to the date operator

For SQLServer:
<concept>
<facttablecolumn>patient_num </facttablecolumn>
<tablename>patient_dimension</tablename>
<columnname>birth_date</columnname>
<columndatatype>D</columndatatype>
<operator>=</operator>
<dimcode>'2010-10-10 13:30:00'</dimcode>
..
</concept>

For SQL Server:
SELECT patient_num
FROM patient_dimension
WHERE birth_date = ('2010-10-10 13:30:00')

 

 

For Oracle:
<concept>
<facttablecolumn>patient_num </facttablecolumn>
<tablename>patient_dimension</tablename>
<columnname>birth_date</columnname>
<columndatatype>D</columndatatype>
<operator>=</operator>
<dimcode>to_date('01-JAN-1998', 'DD-MON-YYYY')
</dimcode>
</concept>

For Oracle:
SELECT patient_num
FROM patient_dimension
WHERE birth_date = to_date ('01-JAN-1998, DD-MON-YYYY')

© Shawn Murphy 2010