Solved

SQL Server 2000 & Paradox BDE Delete query

Posted on 2004-04-26
12
928 Views
Last Modified: 2013-11-23

Hello Experts,

I have a Delphi 5 app that passes data between a local paradox bde databse and a remote SQL Server 2000 database.   I use ADO components to access the SQL data and Infopower components to access the BDE data. There is a table on each server, we'll call them BDEJobs and SQLJobs.  Each has a unique two-field key of RSN & Date.

I would like to create a delete query that would delete all records on SQLJobs that do not have a corresponding record in BDEJobs.

Any ideas on the best way to accomplish this?

TIA,
WS
0
Comment
Question by:Waterstone
12 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
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 ;-)
0
 
LVL 12

Expert Comment

by:Ivanov_G
Comment Utility

   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
0
 
LVL 19

Expert Comment

by:Melih SARICA
Comment Utility
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
 


0
 

Author Comment

by:Waterstone
Comment Utility

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)
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
update  TimeCard
set TimeCard.Status='Active'
where (TimeCard.JobRSN in (select ActiveJobs.JobRSN from ActiveJobs))


meikl ;-)
0
 

Author Comment

by:Waterstone
Comment Utility
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"

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
>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 ;-)

0
 

Author Comment

by:Waterstone
Comment Utility

> 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.
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 500 total points
Comment Utility
>Gets: Line 1: Incorrect syntax near 't'.

instead of
TimeCard t
use
TimeCard as t

also

ActiveJobs a
should be then
ActiveJobs as a

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

this depends on the relation

guessing one ActiveJobs can have many TimeCards

meikl ;-)
0
 

Author Comment

by:Waterstone
Comment Utility
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!
0
 

Author Comment

by:Waterstone
Comment Utility
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.
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
glad you got it work :-))

good luck again

meikl ;-)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now