Board index » delphi » simple paradox question

simple paradox question

Consider this problem:

Lets say I have a customer table with general customer info in it. Then lets
also say that I have some supporting tables with misc customer info in them.
Now as I iterate through my customer table I need to check and make sure
that there is an entry in the supporting tables. If there is not then I need
to add a record with some default (constant) values.

What is the best way to handle this scenario? Right now I have 1400
customers and use TQuery Components for the sub tables that have a parameter
like this 'select -fields- from subtable where -keyfield- = :key' and I make
the datasource of that query the master customer table. However with only 2
subtables this make the process take about 5-6 minues. If I take out all the
code that checks to make sure the subtables have an entry then it runs in
<30 seconds.

Any suggestions?

Richard Carruthers

 

Re:simple paradox question


Quote
>What is the best way to handle this scenario? Right now I have 1400
>customers and use TQuery Components for the sub tables that have a parameter
>like this 'select -fields- from subtable where -keyfield- = :key' and I make
>the datasource of that query the master customer table. However with only 2
>subtables this make the process take about 5-6 minues. If I take out all the
>code that checks to make sure the subtables have an entry then it runs in
><30 seconds.

You can do an insert query like this

Insert into Detail2   (code)
Select Code from Master D1
where (d1.Code Not in (Select d2.Code from Detail2 d2 ))
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:simple paradox question


This is the SQL statement I came up with for my case.

Insert into budget (B_Account, B_Description, B_Branch)
 Select I.Account, I.Description, I.Branch from Import I where (I.Account
not in (select B.b_account from budget B))

Budget is the detail table (1 to 1) of Import.
Account and B_Account are the keys.
All of the fields are of the same type.
Running just the select returns the correct data-

running the whole insert from a TQuery.ExecSQL returns "Field Values
Required"

It occured to me that the syntax for this operation may not be supported by
the bde for paradox tables...

Any suggestions?

Richard

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:398e179b.30645995@forums.Inprise.com...

Quote

> >What is the best way to handle this scenario? Right now I have 1400
> >customers and use TQuery Components for the sub tables that have a
parameter
> >like this 'select -fields- from subtable where -keyfield- = :key' and I
make
> >the datasource of that query the master customer table. However with only
2
> >subtables this make the process take about 5-6 minues. If I take out all
the
> >code that checks to make sure the subtables have an entry then it runs in
> ><30 seconds.

> You can do an insert query like this

> Insert into Detail2   (code)
> Select Code from Master D1
> where (d1.Code Not in (Select d2.Code from Detail2 d2 ))
> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:simple paradox question


Quote
>running the whole insert from a TQuery.ExecSQL returns "Field Values
>Required"

>It occured to me that the syntax for this operation may not be supported by
>the bde for paradox tables...

>Any suggestions?

I tested the query I sent you using a Paradox table.
I just retested it adding an additional field in the insert and that worked too.
I am using BDE 5.11
The error you quote doesn't sound like a query error maybe your code has some
other problems

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:simple paradox question


After reading several posts on www.mers.com I came up with this answer.

You cannot perform an Insert on a paradox table if _any_ field in the table
is marked required, even if it has a Default Value set in the DB Field Info.

The post on mers had to do with the specific fields in the query, the key
itself, or autoinc fields. However, it seems that even if you are not
inserting the specific field, it is not handled properly with the default
value.

Thanks for the help guys.
Richard

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:398c62bb.5544029@forums.Inprise.com...

Quote

> >running the whole insert from a TQuery.ExecSQL returns "Field Values
> >Required"

> >It occured to me that the syntax for this operation may not be supported
by
> >the bde for paradox tables...

> >Any suggestions?

> I tested the query I sent you using a Paradox table.
> I just retested it adding an additional field in the insert and that
worked too.
> I am using BDE 5.11
> The error you quote doesn't sound like a query error maybe your code has
some
> other problems

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Other Threads