New post
Question
· Dec 3, 2018

Using %INLIST in a SQL query using cursors

#Code Snippet #SQL #Caché

I am trying to use %INLIST in  SQL query using a cursor and the query fails to return results. It appears that the variable I use against %INLIST returns an empty string. All the examples I have seen use result sets and build the query as a string. Is it possible to use %INLIST in a cursor-based query? Below is a a snippet of the code I am using:

Property mylist as %String (MAXLEN="") [InitialExpression = "route1, route2, route3"];

set routeList = $LISTBUILD(mylist)

&sql(DECLARE MyReport CURSOR FOR

SELECT

      ProcessStartDate,

      ProcessCompleteDate,

      ProcessName,

     RouteName

FROM

       ProcessRouteTbl

WHERE

       ProcessCompleteDate between :pStartDate and :pEndDate

       AND RouteName %INLIST routeList)

Discussion (3)2
Log in or sign up to continue
Eduard Lebedyuk · Dec 3, 2018

%INLIST assumes a list structure and you're passing a string.

There are two ways so solve it:

1. Store list structure right from beginning. To do that define your property as:

Property mylist as %List(MAXLEN="") [InitialExpression = {$lb("route1", "route2", "route3"}];

and in your method you don't need $listbuild as you already have a list structure in your property, so replace:

set routeList = $LISTBUILD(mylist)

with

set routeList = yourObj.mylist

2. If you already have a lot of comma-delimited strings, you can convert them to list. In this case property stays the same, but replace:

set routeList = $LISTBUILD(mylist)

with

set routeList = $ListFromString(mylist)

I recommend first approach.

Also read the docs about $list functions. It is one of the core Cache concepts.

0 0
Kyle Wagner · Dec 3, 2018

The %INLIST predicate is meant to be used against properties of type %List.  Your property is defined as a %String that happens to contain a delimited string of values.

SQLCODE should have contained  an error code when you attempt to fetch the cursor (-400).

To do what your trying to do you could either use the %CONTAINS predicate IE: "...AND routeList %CONTAINS (RouteName)"

Or you could convert the string to a list.  This would convert your delimited string property to a %List, allowing %INLIST to function correctly.

IE: RouteName %INLIST($LISTFROMSTRING(routeList))

Best solution would probably be to convert the property to %List if possible or even another table.

Hope that helps!

0 0