Hi All,

Target Database is Paradox.
As a result of a query I end up with an Answer Table with the Column
"CLIENT_ID".
What I need to do is to add new records to another Table which has the
following format:

UNIQUE_ID A14
CLIENT_ID A14
CAMPAIGN_ID A14
Status A20

CLIENT_ID is to be populated from the Answer Table.
CAMPAIGN_ID is constant for all the records that will be inserted, say
'XYS12345'.
Status is contant and must be set to 'Send Mail'.

The problem is the UNIQUE_ID field. This is in the format of a dbid with the
record number appended. So for example the first record could be xywr1, the
second xywr2, the third xywr3 and so on. What I need to do is to somehow
generate this ID as part of the query. I know the next available value at
the time of executing the query but I have no idea how to use this as part
of the query when I insert the records.

The number of records to insert could range from 1 to 10000, so doing this
via a query leaves direct calls to the BDE for dead, we would probably be
measuring seconds against minutes.

So my question is can I do this by a query or even a series of queries, if
so how ? I prefer QBE but Local SQL is fine as well.
Thanks for any help
Leslie.

Leslie Milburn wrote:

same answer as Dennis gave.. you can't do it in a query.. from that angle, you
have to do it by scanning the table.. unless Larry has an angle for SQL (which I
really don't expect), that's the only way to do it..


--

Steve Green - Diamond Software Group, Inc - Waldorf Maryland USA
Corel CTech Paradox - http://www.diamondsg.com - Support/Downloads/Links
---------------------------------------------------------------------------------

Do you need a Sanity Check? http://www.diamondsg.com/sanity.htm
Upgrade/Downgrade versions? http://www.diamondsg.com/upgrade.htm
-------------------------------------------------------------------------

I can think of one way. Create an intermediate table with the other 3
columns, and a UNIQUE_ID (or whatever you want to call it) as an autoinc.
Insert the other values via a query. Then in a second query to your
actual table, you can do scalar calcs on the autoinc value for each row,
which you can treat like a record number.

At the end of query 1 you have

UNIQUE_ID CLIENT_ID CAMPAIGN_ID Status
1 AAAAA XYS12345 Send Mail
2 AAAAB XYS12345 Send Mail
3 AAAAC XYS12345 Send Mail
....
100000 ZZZZZ XYS12345 Send Mail

In a second insert query to your destination, you can perform whatever
fixed calc you want on UNIQUE_ID to give it the formatting you want - add
a minimum for it, append text, whatever.

--
Larry DiGiovanni
Digico, Inc
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources

Hi Larry,

This is a very smart solution indeed. However, my ignorance of QBE syntax
means I do not have a clue how to force the formatting I require on the
second query.

Keeping it simple for me, lets say I have done the first query and generated
the intermediate table as per your example. How would I then transfer the
data over and say add the intermediate UNIQUE_ID (1....10000) to the
starting value of 'xyz1234'.

Thanks
Leslie.


"Larry DiGiovanni" <larryd (AT) digicoinc (DOT) com> wrote

Thanks Larry,

FYI: 1234 is a numeric which is incremented.

I'll give this a go, hopefully Local SQL can do the job - I've always found
it difficult to use compared to other SQL dialects.

Thanks again and also to the others who replied. I'll let you know how it
went tomorrow (its midnight here in Australia).
Leslie

"Larry DiGiovanni" <larryd (AT) digicoinc (DOT) com> wrote

Leslie Milburn wrote:

Midnight is prime programming time. Goodnight, slacker <g>.

--
Larry DiGiovanni
Digico, Inc
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources

Mike Irwin [CTech] wrote:

Maybe if you roll outta bed earlier than 10am. Hmmm?



--
Larry DiGiovanni
Digico, Inc
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources