11 Replies Latest reply on Dec 14, 2006 11:38 AM by ArmchairDeity

    Problem with a query

    hevok
      Hi,

      I am having a bit of trouble with a query.
      See the code below. There are at least two records that match one or both words in the list.
      What am i doing wrong?

        • 1. Re: Problem with a query
          insuractive Level 3
          I'm not familiar with the CFQueryParam List attribute, but you might try qualifying the list using ListQualify(list, "'") as I'm pretty sure the SQL syntax requires all strings to be surrounded by apostrophes.

          That way you get
          jobTitle IN ('Care','Manager')
          instead of
          jobTitle IN ('Care,Manager')

          When in doubt, output your SQL statement as text and see what comes out.

          <cfset list = "Care,Manager">
          <cfquery name="qGetInfo" datasource="job">
          SELECT *
          FROM tblJob1
          WHERE 0 = 0
          AND jobTitle IN (<cfqueryparam value="#ListQualify(list, "'")#" cfsqltype="cf_sql_varchar" list="yes"/>)
          </cfquery>

          Hope that helps.
          • 2. Re: Problem with a query
            MikerRoo Level 1
            No, your syntax is correct and you do not use ListQualify in this case.

            Something weird is going on, or you have spaces in the table data, or your table is using a case-sensitive collation.

            First, what DB are you using?

            Next, run this code and attach the unedited results here:

            <cfquery name="qGetInfo" datasource="job" result="zQ_Stat">
            SELECT DISTINCT jobTitle
            FROM tblJob1
            </cfquery>

            <CFDUMP var="#zQ_Stat#">
            <CFDUMP var="#qGetInfo#">


            • 3. Re: Problem with a query
              hevok Level 1
              DB: I use MsSQL 2k

              Here are the results of the cfdump:
              struct
              CACHED false
              COLUMNLIST JOBTITLE
              EXECUTIONTIME 0
              RECORDCOUNT 42
              SQL SELECT DISTINCT jobTitle FROM tblJob1
              query - Rows: 42
              JOBTITLE
              1 Another Test
              2 Assessment Officer - Special Needs
              3 Barking Park HLF Project Manager
              4 Business Development Manager
              5 Care Manager
              6 CCTV Monitoring Officer
              7 Childminding Development Officer
              8 Communications Careline Officer
              9 Community Educational Psychology Service (1 Perm 1 Temp)
              10 Day Centre Officer (Level One)
              11 Deputy Head of Children's Centre x5 (3 year fixed term contract)
              12 Document Imaging and Filing Clerk x2
              13 Fitness Instructors - Various Hours
              14 Group Manager - Business Transformation
              15 Group Manager - Community Housing
              16 Group Manager - Housing Services (3 year contract)
              17 Group Manager - Systems Development
              18 Group Manager- Drug and Alcohol Action Team
              19 Group Manager Finance - Resources and Technical
              20 Investigations Officer
              21 Junior IT Support Analyst
              22 Leisure Attendant (Casual)
              23 Leisure Attendant (Part time)
              24 Lunchtime Support Worker
              25 Operations Manager-SENART
              26 Park Development Officer (2 year Fixed Term Contract)
              27 Play Projects and Development Officer (2 Year Fixed Term Contract)
              28 Receptionist/Cashier - Part-Time
              29 Receptionist/Cashiers - Casual (various hours)
              30 Senior Cashier X2
              31 Senior Project Engineer x2
              32 Substance Misuse Care Managers x 5 (Social Worker qualified desirable or a relevant other)
              33 test
              34 Test final
              35 Test from web team
              36 Test HR
              37 Test my final
              38 test my test
              39 Test upload
              40 This is job 1
              41 Workplace Skills for Life Tutor
              42 Youth Worker - Buildings
              • 4. Re: Problem with a query
                Level 7
                Right.

                And there are no job titles that are either "care" or "manager".

                where x in (y1,y2,y3)

                Requires an EXACT match between x and one of the items in the list

                "care manager" <> "care"; "Group Manager - Business Transformation" <>
                "manager".

                --
                Adam
                • 5. Re: Problem with a query
                  hevok Level 1
                  I see where I was going wrong.

                  the 'IN' requires an exact match.

                  IS there a way of using LIKE instead?

                  Basically, I want users to search for a job where the keywords would either be Care or Manager - and would return both 'Care Manager' and 'Group Manger'.
                  • 6. Re: Problem with a query
                    hevok Level 1
                    Actually that is not what I meant. It is a freeform entry - 'Care' 'Manager' are just examples.
                    Users can type whatever they like, but I want to search for:
                    'Care' AND 'Manager'
                    'Care' OR 'Manager'
                    'Care Manger'.
                    • 7. Problem with a query
                      insuractive Level 3
                      What about
                      <cfset list = "Care,Manager">
                      <cfquery name="qGetInfo" datasource="job">
                      SELECT *
                      FROM tblJob1
                      WHERE 0 = 0
                      AND (
                      <cfloop from="1" to="#ListLen(list)#" index="i">
                      <cfif i neq 1> OR </cfif> jobTitle LIKE '%#ListGetAt(list,i)#%'
                      </cfloop>
                      )
                      • 8. Re: Problem with a query
                        Dan Bracuk Level 5
                        Storing keywords in a separate table would be more efficient.
                        • 9. Re: Problem with a query
                          MikerRoo Level 1
                          Insuractive's approach will work but will be extremely slow and inefficient (because the DB will have to do multiple full table scans!).

                          You do not need to futz around with a keyword table either.

                          Since you are using MS SQL, you can make good use of SQL's full-text search.

                          Look up sp_fulltext_catalog, in MS SQL "Books Online", to start.

                          Post another thread if you want help with that.
                          • 10. Re: Problem with a query
                            insuractive Level 3
                            Wow, I wasn't familiar with the SQL server full-text search functionality. That's pretty powerful stuff. Thanks for bringing that up, MikerRoo.
                            • 11. Re: Problem with a query
                              ArmchairDeity
                              I know I may be a bit late with this, but you might try:

                              <cfset list = "Care,Manager">

                              <cfqueryparam value="#listQualify(list,'%')#" list="true" ... />

                              But (someone may have already said this, I don't have time to read the thread in heavy detail) you need to keep in mind that the way you have this query written jobTitle HAS TO BE Care, or Manager, and I'm not sure what the results of adding the wildcard to the IN clause would be, I'm not sure I've ever tried that.

                              You have the option of querying for any data you want and using a Verity collection to store the query data in a Collection for free-text searchign as well.