| Author |
Message
|
| jrsetters |
 Posted: Tue Feb 26, 2013 12:01 pm Post subject: I need some help with the syntax for a CARDINALITY array |
 |
|
Acolyte
Joined: 24 Aug 2011
Posts: 72
Location: Cincinnati, OH
|
I keep getting a warning "unresolvable field reference" on my SET statements when using an array. I am trying to loop through the patient identifier list using the MRM HL7 message set. It finds the field references just fine if I take the array off.
| Code: |
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
DECLARE hl7 NAMESPACE 'urn:hl7-org:v2xml';
DECLARE i INTEGER 1;
DECLARE count, PID INTEGER;
DECLARE THCEPI CHAR;
SET count = CARDINALITY(InputRoot.MRM.hl7:PID.hl7:"PID.3.PatientIdentifierList"[]);
WHILE i <= count DO
SET THCEPI = InputRoot.MRM.hl7:PID.hl7:"PID.3.PatientIdentifierList"[i].hl7:"CX.5";
IF THCEPI = 'THC_EPI' THEN
SET PID = InputRoot.MRM.hl7:PID.hl7:"PID.3.PatientIdentifierList"[i].hl7:"CX.1";
END IF;
SET i = i+1;
END WHILE; |
The message set is configured like this:
What i want to do is loop through a list of patient identifiers and find the one indicated with 'THC_EPI' in the fifth component.
For example in the following segment ('|' is the field delimiter, '~' is the repetition delimiter, and '^' is the field component delimiter) I want to extract the value 000000000505878.
PID|1||E105921^^^EPIC^EPI~000000000505878^^^EPI^THC_EPI~505878^^^^THC_NZ||FOURTEEN^REG||19630108|M||AA| |
|
| Back to top |
|
 |
| Vitor |
| Posted: Tue Feb 26, 2013 12:05 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
Why not use a SELECT?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| jrsetters |
| Posted: Tue Feb 26, 2013 12:13 pm Post subject: |
|
|
Acolyte
Joined: 24 Aug 2011
Posts: 72
Location: Cincinnati, OH
|
| Vitor wrote: |
| Why not use a SELECT? |
Something like this?
| Code: |
SET PID = SELECT InputRoot.MRM.hl7:PID.hl7:"PID.3.PatientIdentifierList".hl7:"CX.1"
FROM InputRoot.MRM.hl7:PID.hl7:"PID.3.PatientIdentifierList"
WHERE InputRoot.MRM.hl7:PID.hl7:"PID.3.PatientIdentifierList".hl7:"CX.5" = 'THC_EPI'; |
|
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Feb 26, 2013 12:15 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| jrsetters wrote: |
| Vitor wrote: |
| Why not use a SELECT? |
Something like this?
| Code: |
SET PID = SELECT InputRoot.MRM.hl7:PID.hl7:"PID.3.PatientIdentifierList".hl7:"CX.1"
FROM InputRoot.MRM.hl7:PID.hl7:"PID.3.PatientIdentifierList"
WHERE InputRoot.MRM.hl7:PID.hl7:"PID.3.PatientIdentifierList".hl7:"CX.5" = 'THC_EPI'; |
|
Well I'd use an AS clause so you only ave to type the whole thing out once, and you need [] in the FROM to indicate a list (as you've supplied to the CARDINALITY function) but something of that nature yes.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| jrsetters |
| Posted: Tue Feb 26, 2013 12:41 pm Post subject: |
|
|
Acolyte
Joined: 24 Aug 2011
Posts: 72
Location: Cincinnati, OH
|
Well my problem is that none of the SET or SELECT statements will take an array indicator. I feel like it has something to do with the message element being in quotes. But if I remove the quotes, I get a syntax error.
For instance on a repeatable segment this would work and we have used similar code manyt imes:
SET count CARDINALITY(InputRoot.MRM.hl7:PID[]);
But once I try to dig down to the field level it stops recognizing it. I have no idea why the quotes are necessary though. We do similar a similar function in a mapping node and if I export that to eSQL it looks like this:
| Code: |
DECLARE A1 INTEGER 1;
DECLARE A2 INTEGER CARDINALITY(InputRoot.MRM.tns1:PID.tns1:PID.3.PatientIdentifierList[]);
DECLARE A1_REF REFERENCE TO InputRoot.MRM.tns1:PID.tns1:PID.3.PatientIdentifierList[1];
WHILE A1 <= A2 DO
MOVE A1_REF TO InputRoot.MRM.tns1:PID.tns1:PID.3.PatientIdentifierList[A1];
IF InputRoot.MRM.tns1:PID.tns1:PID.3.PatientIdentifierList[A1].tns1:CX.5='THC_EPI' THEN
SET OutBodyRef.tns1:PID.tns1:PID.3.PatientIdentifierList.tns1:CX.1 = A1_REF.tns1:CX.1;
SET OutBodyRef.tns1:PID.tns1:PID.3.PatientIdentifierList.tns1:CX.2 = A1_REF.tns1:CX.1;
END IF;
SET A1 = A1 + 1;
END WHILE; |
But again, even this, which works fine when deployed from the map will show syntax errors on each of the field references in the eSQL edit window. |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Feb 26, 2013 12:47 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| jrsetters wrote: |
| I have no idea why the quotes are necessary though. |
Double quotes in ESQL indicate that the name needs to be evaluated at run time, typically because the element is a reserved word. I don't see why they would be necessary in the example you've given unless 3 isn't allowed because it's all numeric.
I'm sure @kimbert will be along in a moment to explain it.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| adubya |
| Posted: Tue Feb 26, 2013 12:54 pm Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
| Because the field names have dots in them and if they're not in quotes then the dot will be taken as being an element separator rather than part of a field name. |
|
| Back to top |
|
|
| jrsetters |
| Posted: Tue Feb 26, 2013 12:55 pm Post subject: |
|
|
Acolyte
Joined: 24 Aug 2011
Posts: 72
Location: Cincinnati, OH
|
This has to do with a need to discard any messages where that particular patient ID is below a certain number.
There are a couple of sloppier ways I could do it such as using a mapping node first to get the ID and then a filter, or by using a series of SUBSTRING commands to get the value because I know the ID will always be the exact same length (so SUBSTRING BEFORE '^^^THC_EPI' and then SUBSTRING again to the LEFT 15 digits.)
But I'd much rather do it in a cleaner way that actually parses out the fields themselves. |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Feb 26, 2013 1:37 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| adubya wrote: |
| Because the field names have dots in them and if they're not in quotes then the dot will be taken as being an element separator rather than part of a field name. |
Good catch..... 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Feb 27, 2013 4:42 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You should never write your own code that uses CARDINALITY unless you actually need to know the literal count and use that as a specific individual value.
You should always write any kind of loop in ESQL using a reference variable and MOVE and LASTMOVE to confirm you should continue iterating.
It's an immense improvement in performance, and is the recommended practice.
Anyone who says they have reasonable experience with ESQL and writes a loop using cardinality has lied. |
|
| Back to top |
|
|
| jrsetters |
| Posted: Wed Feb 27, 2013 5:54 am Post subject: |
|
|
Acolyte
Joined: 24 Aug 2011
Posts: 72
Location: Cincinnati, OH
|
| Now that we have established how incompetent I am, does anyone have any ideas about why I can't build an array off of the MRM set using the individual field in quotes? |
|
| Back to top |
|
|
| adubya |
| Posted: Wed Feb 27, 2013 6:03 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
Does the flow deploy and run with the warnings but just not perform the desired action ?
Or do you get a deploy error ? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Feb 27, 2013 6:06 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| jrsetters wrote: |
| Now that we have established how incompetent I am, does anyone have any ideas about why I can't build an array off of the MRM set using the individual field in quotes? |
I didn't say incompetent. I said inexperienced.
To summarize what's been mentioned - you need to use any names that include special characters in them, like a dot, in quotes in ESQL.
So if your field name is PID.3.PatientIdentifierList, then you need to use "PID.3.PatientIdentifierList".
You also appear to be using namespace qualifiers on fields under an MRM defintion that does not necessarily appear to be using namespaces. But that's likely my own misunderstanding of what's going on.
The best advice I can suggest at this point, other than changing your loops to use references rather than [n] values is to use a Trace node (with pattern ${Root}) to examine the message you are given.
This will show you how the parser has structured the data, including whether any of the elements you're looking at actually have namespace qualifiers.
From that data, it's relatively straight forward to create the necessary ESQL paths to extract the value you're looking for. |
|
| Back to top |
|
|
| jrsetters |
| Posted: Wed Feb 27, 2013 10:18 am Post subject: |
|
|
Acolyte
Joined: 24 Aug 2011
Posts: 72
Location: Cincinnati, OH
|
| adubya wrote: |
Does the flow deploy and run with the warnings but just not perform the desired action ?
Or do you get a deploy error ? |
It worked fine deployed with the warnings, go figure. |
|
| Back to top |
|
|
| adubya |
| Posted: Wed Feb 27, 2013 10:52 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
Cool.
Lots of our production flows have "unresolvable field reference" warnings  |
|
| Back to top |
|
|
|
|
