DB Lookup – less-than comparison operator

[Peter Heggie]

in Cloverleaf 6.2.0.2, I have created an advanced database lookup to SQL Server. Part of the WHERE clause specifies a ‘less than’ operator (<). Cloverleaf seems to not recognize it as a comparison operator, and possibly thinks it is part of a variable substitution (like MRN = ).

I have tried < and also tried <= and also tried < but none work. what Cloverleaf returns: Error: Failed to query data: Incorrect syntax near ‘<'. I have not actually tried it in Cloverleaf – I am testing at the command line using HCITCL. How can I incorporate a less-than comparison operator in my query?

Peter Heggie
PeterHeggie@crouse.org

Topic

[Robert Milfajt]

Looking at my SQL documention, if the will be the same.  If not, you could not the >= for less than.  If > does hang you up, I’d try BETWEEN and set your lower bound to lowest possible value and upper bound to your less than value.

Good luck…

Robert Milfajt
Northwestern Medicine
Chicago, IL

[Peter Heggie]

Thank you! BETWEEN is probably not going to work in this case. I have an incoming DFT P03 charge message with an MRN but no visit number. The FT1 has a Date of service. I need to get the visit number that is in effect at the time of service.

I’m using the MRN and date of service to do a lookup in a table of patient visits, one row per encounter, and I want to first find the admit date closest to but not greater than the date of service – the incoming charge transaction may be for a previous visit, not the current visit. So once I find the admit date, then I can use that to find the visit number and encounter number that corresponds to that date of service.

Given this data:

mrn              ecd            encounter admitdate

1000111055 200222201 3000301 2018-01-01 08:53:00.000

1000111055 200222202 3000302 2018-02-05 16:03:00.000

1000111055 200222203 3000303 2017-12-01 15:53:00.000

1000111055 200222204 3000304 2017-11-15 00:01:00.000

1000111055 200222205 3000305 2017-12-11 15:06:00.000

and a charge message having mrn 1000111055 and DOS of 2018-02-01 12:24:56, I expect to get an admit date of 2018-01-01 08:53:00.000, and then I can query to get the ecd of 200222201.

If I use a BETWEEN with the lower bound being the lowest possible date, then I’ll pick up the first admit date of 2017-11-15 00:01:00.

Peter Heggie
PeterHeggie@crouse.org

[Peter Heggie]

OK – thank you Michael and Robert, and David (Infor) – after multiple variations, found that simply reversing the values of the WHERE clause resolved the problem. It gets Cloverleaf to resolve the variable name first before handing off the interpreted query to SQL Server, and does not attempt to resolve the < character.

Before:

SELECT ecd FROM [CLOVERSQL].[dbo].[patient_summary] where mrn = and status = ‘TS’ and admitdate =  

(select max(admitdate) from [CLOVERSQL].[dbo].[patient_summary] where mrn = and admitdate < );

After:

SELECT ecd FROM [CLOVERSQL].[dbo].[patient_summary] where mrn = and status = ‘TS’ and admitdate =  

(select max(admitdate) from [CLOVERSQL].[dbo].[patient_summary] where mrn = and > admitdate);

This works correctly.

Peter Heggie
PeterHeggie@crouse.org

[Author]

Replies