3 Replies Latest reply on Apr 21, 2017 9:45 AM by klevitan

    Query Operator "Like" does not work like in SQL (wildcard %)

    FormerAltirisGuy Rookie

      Hi

       

      I'm using LANDesk 9.6 SP1 for 3 month now and I realize that the queries are "interpreted" like this:

      "Computer"."Software"."Add or Remove Programs"."Program"."Name"  Like  "programX"

      -equals in SQL

      "Computer"."Software"."Add or Remove Programs"."Program"."Name"  Like  "%programX%"

       

      BUT I often want to look for a software called "programX*"

      so in SQL I would use

      ...Like "programX%"

       

      Searching the community gives me this...

      Custom Database Queries: How they work, guidelines on creating them, and examples of them in action

      HOW TO: Hacking LANDesk Queries

       

      Is it really true that the LANDesk "query builder" should be "simple to use LIKE statements"?

      That means, it always uses  "%searchstring%" with the LIKE operator, making it impossible to use "searchstring%" or "%searchstring"?

       

      It's hard to believe. C'mon, it's LANDesk! One of the BIGGEST players in software distribution?

      Is there a way I'm not aware of? As I said, I'm a LANDesk beginner ;-)

       

      Thanks

        • 1. Re: Query Operator "Like" does not work like in SQL (wildcard %)
          phoffmann SupportEmployee

          The LIKE statement from the Query builder DOES work like a simple LIKE in SQL.

           

          You can find out as much by using INSPECT on any query (I recommend a simple one).

           

          The GOTCHA for you here (remember - this is "SQL kept SIMPLE for non-SQL aware people) is that the LIKE operator in queries will automatically slap a % on either side of the string that you're doing your search on (which you will again be able to see if you use INSPECT on your query) .

           

          It's one of those catch-22's ... you can't keep contained to be something simple enough for "Bob from Marketing" (as it were) to be able to draw up a query on stuff he cares about / can identify, and yet keep things complex enough so that folks like you can do advanced SQL operations.

           

          In your particular case, your query string should be just like this:

          (...) LIKE programX
          

           

          ... if you then INSPECT the query, the Query SQL will be displayed at the bottom, and you'll see that we'll have added a % on both sides of the 'programX' string.

           

          The (unfortunate) reality is that a good 80% of folks who use the query builder don't really know SQL (WebConsole users, non-admin console users who do it for just reports) in my experience -- so the query builder does what it's supposed to and keep things simple.

           

          Two ways around this would be ...

          • Suggesting / getting an "Advanced mode" query builder to allow for more advanced features such as you are after (with string-searching on only one side, for instance). That'd need to be an enhancement request here -- Enhancement Requests -- as the current limitation is intentional (and makes sense given that we're having to make querying data "easy" for folks who don't know anything about SQL) .
          • Write your query in raw SQL via either XTraction as a report or SQL Enterprise Manager ... or your personal preference.

           

          Hope this helps?

          • 2. Re: Query Operator "Like" does not work like in SQL (wildcard %)
            klevitan Specialist

            I am not sure but I think if you specify Like ProgramX* it will do what you want.

             

            - Kurt

            • 3. Re: Query Operator "Like" does not work like in SQL (wildcard %)
              klevitan Specialist

              Like GEO  Produces this SQL:

               

              SELECT DISTINCT A0.DISPLAYNAME, A1.SUITENAME 

              FROM Computer A0 (nolock) LEFT OUTER JOIN AppSoftwareSuites A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn 

              WHERE (A1.SUITENAME LIKE N'%GEO%')  

              ORDER BY  A0.DISPLAYNAME

               

               

              Like GEO* Produces this SQL:

               

              SELECT DISTINCT A0.DISPLAYNAME, A1.SUITENAME 

              FROM Computer A0 (nolock) LEFT OUTER JOIN AppSoftwareSuites A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn 

              WHERE (A1.SUITENAME LIKE N'GEO%')  

              ORDER BY  A0.DISPLAYNAME

               

              Like *GEO Produces this SQL:

               

              SELECT DISTINCT A0.DISPLAYNAME, A1.SUITENAME 

              FROM Computer A0 (nolock) LEFT OUTER JOIN AppSoftwareSuites A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn 

              WHERE (A1.SUITENAME LIKE N'%GEO')  

              ORDER BY  A0.DISPLAYNAME