SQL Server 2000 & Paradox BDE Delete query

as you have two different connectionm-typs
you can't do this with a single query

so you have to do following

-read sqljob
-lookup bdejob
-if bdejob not exists delete sqljob
-next sqljob

meikl ;-)

   Not possible with one query....

   Let's say you have 2 datasets - SQLDataSet and BDE dataset. In both of them you have SQL statement

   SELECT * FROM YOUR_TABLE
   ORDER BY RSN, DATE

   in while not SQLDataSet.Eof do statement you can check if you have a record in BDEDataSet

There is no way to join both SQL's and get one table...

here is a step by step workflow  for ur operation

10 - Open SQLJobs..
20 - Open BDEJobs
30 - Read RSN & date Combination
40 - Search in BDEJobs
50 - If not Found Delete in SQLJobs
60 - If Eof(SQLJobs)  goto 80
70 - goto 30
80 - Close BDEJobs
90 - Close SQLJobs
 

Thanks for al your input.  I was pretty sure it wasn't possible, but wanted to be sure I wasn't missing something. But perhaps someone can help with the correct syntax to this step in the process.  If I need to post it as a new question someone please let me know.

I've worked out the best logic and am stuck on something simple.  I've got a table of ActiveJobs.  I want to set the status of the table Timecard to Active for each Timecard that has a corresponding record in the ActiveJobs table, such as is shown below.  But of course this is not the correct way to join the two tables

update  TimeCard
set TimeCard.Status='Active'
where (TimeCard.JobRSN = ActiveJobs.JobRSN)

update  TimeCard
set TimeCard.Status='Active'
where (TimeCard.JobRSN in (select ActiveJobs.JobRSN from ActiveJobs))


meikl ;-)

Thanks meikl.

The query changes the Status in All rows, not just the ones that match.  Don't know why.  As I always said when I was teaching COBOL in the early 80's and did Code Reviews, "Looks good, should work"

>The query changes the Status in All rows, not just the ones that match

there should only rows affected in the timecard-table,
where is a reference to the ActiveJobs-table.

>The query changes the Status in All rows, not just the ones that match

if it is so, then all row of the timecard-table have a reference in  theActiveJobs-table.

another statement could be

update  TimeCard t
set t.TimeCard.Status='Active'
where exists (select 1 from ActiveJobs a where t.JobRSN =a.JobRSN)


meikl ;-)

> if it is so, then all row of the timecard-table have a reference in  theActiveJobs-table.

Nope, ActiveJoObs has 738 records, TimeCard 3,372.  All 3,372 get changed.

update  TimeCard t
set t.TimeCard.Status='Active'
where exists (select 1 from ActiveJobs a where t.JobRSN =a.JobRSN)

 Gets: Line 1: Incorrect syntax near 't'.

This should not be so hard.

Well DUUH!  Yep, you're right. Needed to qualify on Date also.

update  TimeCard
set TimeCard.Status='Active'
where exists (select 1 from ActiveJobs where TimeCard.JobRSN = ActiveJobs.JobRSN)
AND TimeCard.WeekEndIngDate='05/01/2004'

Works great.  Thanks!

FYI: My Final Solution.
 Problem:  We upload records from BDE Jobs to SQL Timecards multiple times each week.  During the week records that have already been uploaded can become inactive.  These need to be removed frmo the SWQ table.

Steps:

1)  ActiveJobs is a work table on SQL Server with just JobRSN field.
     Delete all from ActiveJobs

2)  Load Jobs from BDE db into Timecard on SQL Server
     At same time add a record for each JobRSN to ActiveJobs.

3) Query to set all Timecard.Status to DELETE for week.

4)  Query to set all Timecard.Status to Active for week that have a matching record in ActiveJobs.

5)  Delete query to delete all TimeCards with Status of Delete.


Thanks for all the help.

glad you got it work :-))

good luck again

meikl ;-)