5 Replies Latest reply on Dec 7, 2006 11:45 AM by Charlie Griefer


      I am trying to find difference between 2 dynamic list created like this:

      <cfset PreList = ValueList(NAV.Unic_ID, " ")> = ABBA, AABA, ACCC
      <cfset PostList = ValueList(NAVPOST.Unic_ID, " ")> ABBA, ACCC, BBAC

      and then display the information that is different only. In this example I need to display AABA and BBAC

      any idea on how do do something like that? Am I better to write some infomation on my DB or can I just do it dynamicaly?

      many thanks

        • 1. Re: Compare
          Charlie Griefer Level 1
          sounds like you should be able to handle it at the database level with a subquery.

          something like this should get you started:

          SELECT UNIC_ID
          FROM NAV
          WHERE (your existing conditions, if any, here)
          • 2. Re: Compare
            TommyMTL Level 1
            Unless I am not getting it, this cannot work for the current situation. I am running a query from only one Table (NAV)

            First I run the query name="NAV" from NAV(a table)
            then after submit, I need to find if their is any difference between the pre and the post Submit from NAV table.

            The NOT IN is generating several errors in my case.

            any direction to look at?

            • 3. Re: Compare
              Charlie Griefer Level 1
              can you paste the query you're using that's generating the errors? perhaps paste the errors themselves?
              • 4. Re: Compare
                TommyMTL Level 1
                Since I have only ONE table that handle everything in this regards, here is the query I am usign:

                Very first query: (First query to get the original info)
                <cfquery name="NAV" datasource="#Application.DSN#">
                SELECT Unic_ID, ID_Classement, Categorie, MFRLink, C_FR_Name, C_EN_Name, MENLink, Actif
                FROM NAV

                then after submiting ture a form: (an update) the get the latest info
                <cfquery name="NAVPOST" datasource="#Application.DSN#">
                SELECT Unic_ID
                FROM NAV

                Then your recommendation in order to evaluate the difference before and after the update
                (This is the reason why I was creating a ValueList of the original FRIST query)
                <cfquery name="NAVPOSTeval" dbtype="query">
                SELECT Unic_ID
                FROM NAV

                <cfdump var="#NAVPOSTeval#">

                Here is the error:
                Query Of Queries syntax error.
                Encountered "UNIC_ID NOT IN ( SELECT. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, ...
                • 5. Re: Compare
                  Charlie Griefer Level 1
                  my recommendation was that my query using the subquery would replace your initial 2 queries...not be run in addition to.

                  altho now that it's clearer that both queries are not being run at the same time, it doesn't look like that approach would work.

                  you can use a hidden form field with the valueList() value from query #1. then after the form submit do a query using the WHERE UNIC_ID NOT IN (form.theHiddenField)

                  the goal here is to make as few distinct trips to the db as possible. each <cfquery> has a cost associated with it. i'm trying to help you minimize those costs.