?
Solved

SQL Server 2000 & Paradox BDE Delete query

Posted on 2004-04-26
12
Medium Priority
?
964 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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month13 days, 12 hours left to enroll

755 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