Reinsert records only if condition meet

I thought I had this beat but it is still happening.  When a user closes out a record manually I only want it to reinsert if bitOverridden is not checked.  It still seems to be reinerting no matter.  How do i fix this. The attached file has a record sample to show what is happening.


INSERT INTO tblPermTask (
		intTaskID, intAssignedId,
		intFilterId, intPositionId, 
		strTaskName, dtFound, 
		dtCompleted, strStatus, 
		dtStatus, strSSN, 
		strName, strPositionData, 
		strSoldierData, strNotes,
		strProponent, dtNotification, 
		dtCompleteTask)
 SELECT t.intTaskID, t.intAssignedId,
		t.intFilterId, t.intPositionId, 
		t.strTaskName, t.dtFound, 
		t.dtCompleted, t.strStatus, 
		t.dtStatus, t.strSSN, 
		t.strName, t.strPositionData, 
		t.strSoldierData, t.strNotes,
		t.strProponent, t.dtNotification, 
		t.dtCompleteTask
   FROM tblTask as t INNER JOIN
		tblPermTask as p on p.intassignedId = t.intAssignedId AND p.intFilterID = t.intFilterId AND p.intPositionId = t.intPositionId and p.strSSN = t.strSSN
  WHERE p.dtCompleted is not null and p.bitOverRide is NULL or p.bitOverride = 0
EXCEPT
 SELECT p.intTaskID, p.intAssignedId,
		p.intFilterId, p.intPositionId, 
		p.strTaskName, p.dtFound, 
		p.dtCompleted, p.strStatus, 
		p.dtStatus, p.strSSN, 
		p.strName, p.strPositionData, 
		p.strSoldierData, p.strNotes,
		p.strProponent, p.dtNotification, 
		p.dtCompleteTask
   FROM tblPermTask p

Open in new window

Help.txt
kdeutschAsked:
Who is Participating?
 
kdeutschConnect With a Mentor Author Commented:
Ok got this to wrok last week with the following code.
INSERT INTO tblPermTask (
            intTaskID, intAssignedId,
            intFilterId, intPositionId,
            strTaskName, dtFound,
            dtCompleted, strStatus,
            dtStatus, strSSN,
            strName, strPositionData,
            strSoldierData, strNotes,
            strProponent, dtNotification,
            dtCompleteTask)
 SELECT t.intTaskID, t.intAssignedId,
            t.intFilterId, t.intPositionId,
            t.strTaskName, t.dtFound,
            t.dtCompleted, t.strStatus,
            t.dtStatus, t.strSSN,
            t.strName, t.strPositionData,
            t.strSoldierData, t.strNotes,
            t.strProponent, t.dtNotification,
            t.dtCompleteTask
   FROM tblTask as t INNER JOIN
            tblPermTask as p on p.intassignedId = t.intAssignedId AND p.intFilterID = t.intFilterId AND p.intPositionId = t.intPositionId and p.strSSN = t.strSSN
  WHERE             p.dtCompleted is not null and p.bitOverRide is null
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
INSERT INTO tblPermTask (
            intTaskID, intAssignedId,
            intFilterId, intPositionId,
            strTaskName, dtFound,
            dtCompleted, strStatus,
            dtStatus, strSSN,
            strName, strPositionData,
            strSoldierData, strNotes,
            strProponent, dtNotification,
            dtCompleteTask)
 SELECT t.intTaskID, t.intAssignedId,
            t.intFilterId, t.intPositionId,
            t.strTaskName, t.dtFound,
            t.dtCompleted, t.strStatus,
            t.dtStatus, t.strSSN,
            t.strName, t.strPositionData,
            t.strSoldierData, t.strNotes,
            t.strProponent, t.dtNotification,
            t.dtCompleteTask
   FROM tblTask as t INNER JOIN
            tblPermTask as p on p.intassignedId = t.intAssignedId AND p.intFilterID = t.intFilterId AND p.intPositionId = t.intPositionId and p.strSSN = t.strSSN
  WHERE p.dtCompleted is not null and ( p.bitOverRide is NULL or p.bitOverride = 0 )
EXCEPT
 SELECT p.intTaskID, p.intAssignedId,
            p.intFilterId, p.intPositionId,
            p.strTaskName, p.dtFound,
            p.dtCompleted, p.strStatus,
            p.dtStatus, p.strSSN,
            p.strName, p.strPositionData,
            p.strSoldierData, p.strNotes,
            p.strProponent, p.dtNotification,
            p.dtCompleteTask
   FROM tblPermTask p

0
 
igni7eConnect With a Mentor Commented:
 WHERE p.dtCompleted is not null and p.bitOverRide<>1
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
igni7eCommented:
Just to let you know, if you don't need to, you shouldn't put OR's in your WHERE clause.
They decrease performance.
0
 
kdeutschAuthor Commented:
All,
Ok I tried the first by aneeshattingal, but the record still reinserted,
then I tried the example by igni7e and the record did not reinsert but if I just close the task and don't click on override, it does not reinsert the record like it should.

( p.bitOverRide is NULL or p.bitOverride = 0 )   Reinserts the records no matter

p.bitOverRide is NULL and p.bitOverride <>1   Does not reinsert records if the record is  wasnot marked as overridden.
0
 
igni7eCommented:
Well, is the value changed to 1 when its marked as overridden?

Change X to whatever the overridden value is:
p.bitOverRide is NULL and p.bitOverride <>X
0
 
kdeutschAuthor Commented:
HI,
Correct the value is changed to 1 when overridden.
I have since tried this but now the records don't reinsert id bitOverridden is not checked

 p.dtCompleted is not null and p.bitOverride <> 1
0
 
igni7eConnect With a Mentor Commented:
If tblPermTask contains those records it won't insert as you use EXCEPT
0
 
kdeutschAuthor Commented:
OK,
I took out the except statement but when I closed records but don't override them, they are not reinserting agian like they should., the good news is if I mark them as overridden they are not showing up either.

INSERT INTO tblPermTask (
            intTaskID, intAssignedId,
            intFilterId, intPositionId,
            strTaskName, dtFound,
            dtCompleted, strStatus,
            dtStatus, strSSN,
            strName, strPositionData,
            strSoldierData, strNotes,
            strProponent, dtNotification,
            dtCompleteTask)
 SELECT t.intTaskID, t.intAssignedId,
            t.intFilterId, t.intPositionId,
            t.strTaskName, t.dtFound,
            t.dtCompleted, t.strStatus,
            t.dtStatus, t.strSSN,
            t.strName, t.strPositionData,
            t.strSoldierData, t.strNotes,
            t.strProponent, t.dtNotification,
            t.dtCompleteTask
   FROM tblTask as t INNER JOIN
            tblPermTask as p on p.intassignedId = t.intAssignedId AND p.intFilterID = t.intFilterId AND p.intPositionId = t.intPositionId and p.strSSN = t.strSSN
  WHERE             p.dtCompleted is not null and p.bitOverRide <> 1


0
 
igni7eCommented:
Only insert if it's closed and not overridden right?
WHERE p.dtCompleted is not null and p.bitOverRide <> 1

You may need to change p.dtCompleted:
p.dtCompleted = 'YES'
Change YES to whatever value it is when closed.
0
 
igni7eCommented:
If my above comment doesn't work, just make sure the values in the DB are updated correctly before the insert statement is run.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.