Query Question for a datacombo

[Neerav Verma]

I have a class ICD9 in one NS and another has 4 properties of it in a different NS

Class Charge  has ICD9One, ICDTwo, ICDThree, ICDFour

What I want is to pick up the code from ICD9 to display in my datacombo in order of ICD9One, ICDTwo, ICDThree, ICDFour

SELECT ID, Code, ShortDescription  FROM ICD9 

WHERE ID = ( select ICD9One  FROM Charge where ID = 3)

OR ID = (select ICD9Two FROM Charge where ID = 3)

OR ID IN (select ICD9Three FROM Charge where ID = 3)

OR ID IN (select ICD9Four FROM Charge where ID = 3)

This is the result I get

 ID     Code     ShortDescription          

 -----  -------  ------------------------- 

 10     002.9    PARATYPHOID FEVER UNSPEC  

 54     007.3    INTESTINAL TRICHOMONIASIS 

 6      002.0    TYPHOID FEVER             

 1      001      CHOLERA* 

But the properties in Charge actually are

54     007.3

6       002.0

1       001

10     002.9

Not sure how to get the correct order. 

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------
Samuel Goldwyn  - "I'm willing to admit that I may not always be right, but I am never wrong."

[Lawrence H. Harris]

I don't think you can assume the order in SQL without using a temporary table.

To be sure I would add sequence number fields to the CHARGE record with 1,2,3,4 then sort by that. You might try nesting in parenthesis to see if that will force the order of evaluation but I am not sure that is guaranteed to work for all time (if it even does).

If you have the ability to adjust the class definitions remove the ICD9 codes from the CHARGE class to their own and replace them with a one to many reference. Then you can both allow for more (or less) than 4 codes and control the order explicitly with a sequence field or implicitly by knowing that Cache always increments the ID when you insert a new record and sort by ID.

If you can't change the charge record then you may have to write a stored procedure that returns the needed result set. I have not done it but there are instructions in the documentation if you search 'Defining and Using Stored Procedures'. Then from SQL you can just CALL your stored procedure.

In any event here is some dummy classes I mocked up using a reference to a ICD9List Class.

s tSC=##class(ICD9).TestData()
k idCharge
s tSC=##class(Charge).AddCharge("007.3",1,.idCharge)
s tSC=##class(Charge).AddCharge("002.0",2,.idCharge)
s tSC=##class(Charge).AddCharge("001",3,.idCharge)
s tSC=##class(Charge).AddCharge("002.9",4,.idCharge)
k idCharge
s tSC=##class(Charge).AddCharge("002.0",1,.idCharge)
s tSC=##class(Charge).AddCharge("002.9",2,.idCharge)
s tSC=##class(Charge).AddCharge("007.3",3,.idCharge)
s tSC=##class(Charge).AddCharge("001",4,.idCharge)

Then the SQL is:

Select refICD9->ID,refICD9->Code,refICD9->ShortDescription From ICD9List
Where refCharge=8
Order By Sequence


Class User.Charge Extends (%Persistent, %XML.Adaptor)
{

Relationship refICD9List As User.ICD9List [ Cardinality = many, Inverse = refCharge ];

Property Comment As %String;

ClassMethod AddCharge(Code As %String, Sequence As %Integer, ByRef idCharge As Charge) As %Integer
{
If '$D(idCharge) {
Set objCharge=##class(Charge).%New()
Set objCharge.Comment="Auto Created"
Do objCharge.%Save()
Set idCharge=objCharge.%Id()
}
&sql(Select ID Into :idICD9 From ICD9 Where Code=:Code)
If SQLCODE=100 Quit $$$ERROR($$$GeneralError,"ICD9 Code "_Code_" missing")
If SQLCODE'=0 Quit $$$ERROR($$$GeneralError,"Unexpected SQL Error (select) "_SQLCODE)
Set objICD9List=##class(ICD9List).%New()
Set objICD9List.Code=Code
Set objICD9List.Sequence=Sequence
Set objICD9List.refCharge=##class(Charge).%OpenId(idCharge)
Set objICD9List.refICD9=##class(ICD9).%OpenId(idICD9)
Do objICD9List.%Save()
Quit $$$OK
}

}

Class User.ICD9 Extends (%Persistent, %XML.Adaptor)
{

Property Code As %String [ Required ];

Property ShortDescription As %String [ Required ];

Index CodeIndex On Code [ Unique ];

Relationship refICD9List As User.ICD9List [ Cardinality = many, Inverse = refICD9 ];

ClassMethod AddCode(Code As %String, ShortDescription As %String) As %Status
{
Set objICD9=##class(ICD9).%New()
Set objICD9.Code=Code
Set objICD9.ShortDescription=ShortDescription
Do objICD9.%Save()
Quit $$$OK
}

ClassMethod TestData() As %Status
{
Set tSC=##class(ICD9).AddCode("002.9","PARATYPHOID FEVER UNSPEC")
Set tSC=##class(ICD9).AddCode("007.3","INTESTINAL TRICHOMONIASIS")
Set tSC=##class(ICD9).AddCode("002.0","TYPHOID FEVER")
Set tSC=##class(ICD9).AddCode("001","CHOLERA*")
Quit $$$OK
}

}

Class User.ICD9List Extends (%Persistent, %XML.Adaptor)
{

Property Code As %String;

Property Sequence As %Integer;

Relationship refCharge As User.Charge [ Cardinality = one, Inverse = refICD9List ];

Index refChargeIndex On refCharge;

Relationship refICD9 As User.ICD9 [ Cardinality = one, Inverse = refICD9List ];

}

Lawrence

________________________________

http://www.linkedin.com/in/vneerav <http://www.linkedin.com/in/vneerav>
------------------------------------------------------
Samuel Goldwyn <http://www.brainyquote.com/quotes/authors/s/samuel_goldwyn.html> - "I'm willing to admit that I may not always be right, but I am never wrong."

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en <http://groups.google.com/group/Ensemble-in-Healthcare?hl=en>

[Neerav Verma]

We can't have relationships with Icd9 because it is sitting in a different ns. Technichally we can but we don't want to because standard codes are referenced by hundreds of classes and relationships won't work

I started going the path of using a List or Array and then order it by its Sequence (which could be the element key or index) but the problem with that they are infinite., I had to limit them to 4.

That is why I made 4 different properties

Tell me more about the stored procedure

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav

------------------------------------------------------
Stephen Leacock  - "I detest life-insurance agents: they always argue that I shall some day die, which is not so."

[Lawrence H. Harris]

Here is a version of the Charge class with a custom query. It was built with the 'Class->Add-Query' wizard and then edited according to the Cache documentation.


It works from MS Excel but not from the SMC's Execute SQL function. When you try from the SMC it says "ERROR #5540: SQLCODE: -370 Message: SQL CALL Statement cannot be used for Query Procedure 'SQLUSER.CHARGE_GETICD9CODES'" but I can't find any documentation on what this means. So whether you can call the stored procedure or not from Zen I don't know.

From MS Excel you use {CALL Charge_GetICD9Codes(i)} where i is the ID of the charge you want to look at. The output looks like this:

ID Code ShortDescription
11 007.3 INTESTINAL TRICHOMONIASIS
12 002.0 TYPHOID FEVER
13 001 CHOLERA*

10 002.9 PARATYPHOID FEVER UNSPEC

Note that I manually populated the ICD9[1..4] fields with the ID's from the ICD9 table.

If any one knows why SMC won't let me call the procedure to test it I would love to know.

Lawrence
======================

Class User.Charge Extends (%Persistent, %XML.Adaptor)
{

Relationship refICD9List As User.ICD9List [ Cardinality = many, Inverse = refCharge ];

Property Comment As %String;

// Object ID in ICD9 for first code
Property ICD91 As %String;

// Object ID in ICD9 for second code
Property ICD92 As %String;

// Object ID in ICD9 for third code
Property ICD93 As %String;

// Object ID in ICD9 for fourth code
Property ICD94 As %String;

ClassMethod AddCharge(Code As %String, Sequence As %Integer, ByRef idCharge As Charge) As %Integer
{
If '$D(idCharge) {
Set objCharge=##class(Charge).%New()
Set objCharge.Comment="Auto Created"
Do objCharge.%Save()
Set idCharge=objCharge.%Id()
}
&sql(Select ID Into :idICD9 From ICD9 Where Code=:Code)
If SQLCODE=100 Quit $$$ERROR($$$GeneralError,"ICD9 Code "_Code_" missing")
If SQLCODE'=0 Quit $$$ERROR($$$GeneralError,"Unexpected SQL Error (select) "_SQLCODE)
Set objICD9List=##class(ICD9List).%New()
Set objICD9List.Code=Code
Set objICD9List.Sequence=Sequence
Set objICD9List.refCharge=##class(Charge).%OpenId(idCharge)
Set objICD9List.refICD9=##class(ICD9).%OpenId(idICD9)
Do objICD9List.%Save()
Quit $$$OK
}

/// Return the ICD9 codes associated with this charge in the order they are defined ICD9[i]
Query GetICD9Codes(ChargeId As %Integer) As %Query(CONTAINID = 1, ROWSPEC = "ID:%String,Code:%String,ShortDescription:%String") [ SqlProc ]
{
}

ClassMethod GetICD9CodesExecute(ByRef qHandle As %Binary, ChargeId As %Integer) As %Status
{
Set qHandle(1)=##class(Charge).%OpenId(ChargeId)
Set qHandle(2)=0
Quit $$$OK
}

ClassMethod GetICD9CodesClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = GetICD9CodesExecute ]
{
Kill qHandle(1),qHandle(2)
Quit $$$OK
}

ClassMethod GetICD9CodesFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = GetICD9CodesExecute ]
{
Set idICD9=""
If $IsObject(qHandle(1)) {
While (idICD9="") {
Set qHandle(2)=$Get(qHandle(2))+1
Quit:qHandle(2)>4
Set idICD9=$Case(qHandle(2),1:qHandle(1).ICD91,2:qHandle(1).ICD92,
3:qHandle(1).ICD93,4:qHandle(1).ICD94,
:"")
Quit:idICD9'=""
}
}
If idICD9="" {
Set Row=""
Set AtEnd=1
Quit $$$OK
}
Set objICD9=##class(ICD9).%OpenId(idICD9)
If '$IsObject(objICD9) {
Set Row=""
Set AtEnd=1
Quit $$$OK
}
Set Row=$ListBuild(objICD9.%Id(),objICD9.Code,objICD9.ShortDescription)
Set AtEnd=0
Kill objICD9
Quit $$$OK

}

}



________________________________

From: n...@weconnect.us on behalf of Neerav Verma
Sent: Wed 18/11/2009 8:09 AM
To: ensemble-in...@googlegroups.com
Subject: Re: [InterSystems-EnsHlth] Query Question for a datacombo


We can't have relationships with Icd9 because it is sitting in a different ns. Technichally we can but we don't want to because standard codes are referenced by hundreds of classes and relationships won't work

I started going the path of using a List or Array and then order it by its Sequence (which could be the element key or index) but the problem with that they are infinite., I had to limit them to 4.
That is why I made 4 different properties

Tell me more about the stored procedure

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------

Stephen Leacock <http://www.brainyquote.com/quotes/authors/s/stephen_leacock.html> - "I detest life-insurance agents: they always argue that I shall some day die, which is not so."

[Neerav Verma]

Great

I will give it a shot. Even though I do have what I needed working but will surely give this a shot as well

Appreciate your help

Thank You,

Neerav Verma
http://www.linkedin.com/in/vneerav
------------------------------------------------------

Jonathan Swift  - "May you live every day of your life."