Clovertech

using variable in a SQLITE IN clause

› Clovertech Forums › Cloverleaf › using variable in a SQLITE IN clause

Tagged: 

  • Creator
    Topic
  • June 26, 2019 at 5:28 pm #110116
    Paul Bishop
    Participant

      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

    • Creator
      Topic
    Viewing 4 reply threads
    • Author
      Replies
      • June 28, 2019 at 12:05 am #110189
        Charlie Bursell
        Participant

          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.

        • June 28, 2019 at 10:50 am #110212
          Paul Bishop
          Participant

            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

          • July 16, 2019 at 12:03 pm #110703
            Jeff Dinsmore
            Participant

              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

                • July 16, 2019 at 12:09 pm #110704
                  Jeff Dinsmore
                  Participant

                    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

                  • July 16, 2019 at 12:15 pm #110706
                    Jeff Dinsmore
                    Participant

                      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

                      1. July 16, 2019 at 12:22 pm #110708
                        Jeff Dinsmore
                        Participant

                          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

                      2. Author
                        Replies
                      Viewing 4 reply threads
                      • You must be logged in to reply to this topic.