Solved

SQL Server 2000 & Paradox BDE Delete query

Posted on 2004-04-26
12
944 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10925713
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
ID: 10926249

   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
ID: 10928197
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Waterstone
ID: 10928576

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
ID: 10928606
update  TimeCard
set TimeCard.Status='Active'
where (TimeCard.JobRSN in (select ActiveJobs.JobRSN from ActiveJobs))


meikl ;-)
0
 

Author Comment

by:Waterstone
ID: 10928714
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
 
LVL 27

Expert Comment

by:kretzschmar
ID: 10928770
>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
ID: 10928910

> 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
ID: 10928970
>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
ID: 10929200
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
ID: 10929777
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
ID: 10930233
glad you got it work :-))

good luck again

meikl ;-)
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
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…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

691 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