This content has been marked as final. Show 5 replies
sounds like you should be able to handle it at the database level with a subquery.
something like this should get you started:
WHERE (your existing conditions, if any, here)
AND UNIC_ID NOT IN (SELECT NAVPOST.UNIC_ID FROM NAVPOST)
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?
can you paste the query you're using that's generating the errors? perhaps paste the errors themselves?
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
then after submiting ture a form: (an update) the get the latest info
<cfquery name="NAVPOST" datasource="#Application.DSN#">
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">
WHERE UNIC_ID NOT IN (SELECT NAVPOST.UNIC_ID FROM NAVPOST)
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, ...
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.