Re: [MarkLogic Dev General] Need help with SQL

[Ankur Patwa]

All,
I found the answer in SQL Modelling guide in Fragment Roots in Database section.
One caveat with it is that it breaks other cts queries and free text searches.
Our fragments are defined at document level right now and would like to keep it 
that way.
I've tried creating a Fragment Root at document's root element but it looks
 like the fragments do not jive well with one another.
Should I be creating a Fragment Parent at the root level but to no avail.
What should I try next?
Any help/insight is much appreciated.

Thanks in advance.

Best,
Ankur


From: general-boun...@developer.marklogic.com 
[mailto:general-boun...@developer.marklogic.com] On Behalf Of Ankur Patwa
Sent: Monday, November 26, 2012 5:18 PM
To: MarkLogic Developer Discussion (general@developer.marklogic.com)
Subject: [MarkLogic Dev General] Need help with SQL


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<mailto:i...@icainformatics.com> and destroy all copies 
of the original message. Thank you.




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