using variable in a SQLITE IN clause

[Paul Bishop]

I’ve been banging my head on this one.  I’m trying to use a variable in a SQLite IN condition with no luck.  If I do a straight select using this:

db eval {SELECT * FROM myTable where ord_code in (123,456,789)}</pre>

I get results back.  But if I try to put the order codes in a variable, I don’t get any results back:

db eval {SELECT * FROM myTable where ord_code in ($ord_code_list)}</pre>

I’ve tried setting ord_code_list the following ways:

set ord_code_list “123,456,789”

set ord_code_list “123 456 789”

set ord_code_list “123, 456, 789”

set ord_code list {“123″,”456″,”789”}

Has anybody successfully done this, and if so, how?

Thank you!

Paul Bishop

Carle Foundation Hosptial

Urbana, IL

Paul Bishop
Carle Foundation Hospital
Urbana, IL

Topic

[Charlie Bursell]

I always use the subst command here.

# As a global or namespace variable

set select {SELECT * FROM myTable where ord_code in $ord_code_list}

 

Then AFTER you set the value of ord_code_list:

db eval [subst -nocommands -nobackslashes $select]

The problem with your command is the $ord_code_list is inside brackets {} and will not be evaluated.

[Paul Bishop]

Thanks Charlie!  I wasn’t thinking about the brackets and I had never worked with the subst command.  This worked perfectly.

Paul Bishop
Carle Foundation Hospital
Urbana, IL

[Jeff Dinsmore]

I use this simple proc to format an “in” list for my SQLite queries.
<p style=”padding-left: 30px;”>proc inList { inTermsList } {</p>
<p style=”padding-left: 30px;”>  set inStr “”

foreach t $inTermsList {

if { $inStr ne “” } {
append inStr ,
}
append inStr \’$t\’

}

return $inStr</p>
<p style=”padding-left: 30px;”>}</p>
Then you can use it directly in your query something like this:
<p style=”padding-left: 30px;”>set mdIdList

</p>
<p style=”padding-left: 30px;”>set cmpColumn crmcMdId</p>
<p style=”padding-left: 30px;”>set mdInfo [$dbHandle eval “select crmcMdId,npi,groupName,namePrefix,nameFirst,nameMid,nameLast,nameSuffix from providers where $cmpColumn in ([inList $mdIdList])”]</p>

Jeff Dinsmore
Chesapeake Regional Healthcare

[Jeff Dinsmore]

Well, that didn’t format very nicely…  Looks like I need some more practice.

Let’s try again:

[code]

proc inList { inTermsList } {

set inStr “”

foreach t $inTermsList {

if { $inStr ne “” } {
append inStr ,
}
append inStr \’$t\’

}

return $inStr

}

[/code]

[code]

set mdIdList

set cmpColumn crmcMdId

set mdInfo [$dbHandle eval “select crmcMdId,npi,groupName,namePrefix,nameFirst,nameMid,nameLast,nameSuffix from providers where $cmpColumn in ([inList $mdIdList])”]

[/code]

Jeff Dinsmore
Chesapeake Regional Healthcare

[Jeff Dinsmore]

Dang… swing number three…

The proc:
<p style=”padding-left: 30px;”>proc inList { inTermsList } {</p>
<p style=”padding-left: 30px;”>  set inStr “”</p>
<p style=”padding-left: 30px;”>  foreach t $inTermsList {</p>
<p style=”padding-left: 30px;”>    if { $inStr ne “” } {
append inStr ,
}
append inStr \’$t\’</p>
<p style=”padding-left: 30px;”>  }</p>
<p style=”padding-left: 30px;”>  return $inStr</p>
<p style=”padding-left: 30px;”>}</p>
Using the proc – something like this:
<p style=”padding-left: 30px;”>set mdIdList

</p>
<p style=”padding-left: 30px;”>set cmpColumn crmcMdId</p>
<p style=”padding-left: 30px;”>set mdInfo [$dbHandle eval “select crmcMdId,npi,groupName,namePrefix,nameFirst,nameMid,nameLast,nameSuffix from providers where $cmpColumn in ([inList $mdIdList])”]</p>

Jeff Dinsmore
Chesapeake Regional Healthcare

[Jeff Dinsmore]

OK, I’m done.  That’s really unpleasant.

How should I be formatting code snippets?

Is there a preview option like the old forum had?

Jeff Dinsmore
Chesapeake Regional Healthcare

[Author]

Replies