[MarkLogic Dev General] Need help with SQL

[Ankur Patwa]

All,

I am trying to represent the following cts query in SQL



<cts:element-query qtextconst="diagnostics-result:HbA1c GE 9.0" 
xmlns:cts="http://marklogic.com/cts";>

  <cts:element 
xmlns:p="http://foo.com/cm/doc/blah.xsd";>p:discreteResult</cts:element>

  <cts:and-query>

    <cts:word-query>

      <cts:text xml:lang="en">HbA1c</cts:text>

    </cts:word-query>

    <cts:element-range-query operator="&gt;=">

      <cts:element 
xmlns:p="http://foo.com/cm/doc/blah.xsd";>p:discreteResultValue</cts:element>

      <cts:value xsi:type="xs:decimal" 
xmlns:xs="http://www.w3.org/2001/XMLSchema"; 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>9</cts:value>

    </cts:element-range-query>

  </cts:and-query>

</cts:element-query>





Where documents look like:

<patientRecord>

...

   <medicalData>

      ...

        <diagnostics>

         ...

          <diagnostic status="active" ageAtEvent="24951" 
dateTimeLow="2012-07-26T03:23:20-05:00"

                dateTimeHigh="2012-07-26T03:23:20-05:00" nid="n5aez">

                <origins>

                    <origin displayName="sampleccd"

                        documentId="764021c5-a6ef-4dcd-b786-2ab457f80f31"/>

                </origins>

                <displayName>CBC</displayName>

                <discreteResults>

                    <discreteResult unit="PQ" 
dateTimeLow="2012-07-26T03:23:20-05:00"

                        dateTimeHigh="2012-07-26T03:23:20-05:00" nid="nr7il">

                        <discreteResultValue>7.8</discreteResultValue>

                        <discreteResultDisplayName>HbA1c 
test</discreteResultDisplayName>

                    </discreteResult>

                </discreteResults>

            </diagnostic>

        </diagnostics>

     </medicalData>

</patientRecord>



On the SQL side I have a view (labs) with following columns:

1.       uri (document uri),

2.       path range index on discreteResultValue called lab_name

3.       path range index on discreteResultDisplayName called lab_value



I tried running a very simple SQL query:

SELECT distinct(uri) from labs where lab_name ='HbA1c test' and lab_value
 > 9.0



Here's where the fun begins:



With the CTS query above, there are 15 results given that we had to add a 
"filtered" as a flag in MarkLogic's search:search implementation.



With the SQL query above, I get back uris (total=48) with either lab_name
 = HbA1c test OR lab_value > 9. I wish to get uri of documents with both 
conditions.

I've tried a self-join query for labs table on uri but cannot get to documents 
that fulfill both conditions





Thanks,

Ankur










NOTICE OF CONFIDENTIALITY: This electronic message, including attachments, is 
for the sole use of the named recipient and may contain confidential or 
privileged information protected by State of Tennessee and Federal regulations. 
 Any unauthorized review, use, disclosure, copying or distribution is strictly 
prohibited.  If you are not the intended recipient or have received this 
communication in error please contact the sender or email 
i...@icainformatics.com and destroy all copies of the original message. Thank 
you.

_______________________________________________
General mailing list
General@developer.marklogic.com
http://developer.marklogic.com/mailman/listinfo/general