Very Computer

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.

Looks like a job for a tcursor to me. In a scan or a while loop. And if you are
in a multiuser environment you may have to watch out for how you do the
increment of the number. I Don't see any way to do it in a single query. You
could code a series of queries in a loop as well but it would be way slower than
a tc.

Ask if you need more details.

Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and non-profits since
1982

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.

Quote:> 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

How come you get to the neat answers before I read the NG ? It's
just not fair !  :)

Mike

Quote:> 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

Is 1234 is to be treated as numeric

xyz1235
xyz1236
...
xyz11234

or as an alphanumeric seed:

xyz12341
xyz12342
...
xyz123410000

Either way, I'd use SQL.  Not sure if QBE will do the implicit typecasting
you'd need.

SELECT 'xyz' || CAST(1234 + unique_id as CHAR(6)) as unique_id,
       client_id, campaign_id, Status
  FROM answer

Will give you an answer table with 1234 treated as a numeric seed.  Move
1234 into the prefix string xyz if you want it the other way.

I forget if that'll leave trailing spaces (char(6)) or not.  If it does
and you don't want them:

SELECT 'xyz' || TRIM(TRAILING ' ' FROM CAST(1234 + unique_id as CHAR(6)))
as unique_id,
       client_id, campaign_id, Status
  FROM answer

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

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

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

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

Out by 7 ... have a 6-year-old to rouse (got any spare nukes ?
He sleeps like the dead !) and get out of the door, aside from
getting out and into work by 9 or so. Today we had a small fight

email, opened it, and double-clicked on the Zip  file. Strange
things are happening ... Yes, I did read your email, but this
couldn't have been a virus, could it ? ... what ? .. Yes, of
course I know the person who sent it ! They'd never get a
virus...") and so on and so on.

Mike

Hi All,

As promised here is what I ended up doing.

Firstly I restructured the answer table to add an Autoincrement column.
Two reasons for this:
    1. It seemed to be slightly faster than doing a query into another
table.
    2. The autoinc field always started from 1, whereas if the temp table
        was not deleted for any reason it remembered the autoinc starting
        value even if the table was emptied (I would have thought an
        empty operation would reset it back to 1).

So now the Answer table is in the format
CLIENT_ID                A14
RECORD_COUNT    +

I then did the sql query as suggested by Larry.

Note: I have a generic Query engine which uses text substitution rather than
parameter passing as this allows me to use a variety of development tools,
eg C.

Anyway my SQL query ended up as follows:

insert into :ALIAS:TARGET.DB
(UNIQUE_ID, CLIENT_ID, CAMPAIGN_ID, STATUS_CODE)
select cast ('##3' + ANSWER.RECORD_COUNT as char (##2)) || '##1' as
UNIQUE_ID,
CLIENT_ID, '##0' as CAMPAIGN_ID, 'Send Mail' as STATUS_CODE
from ANSWER

where
##0 is the CampaignID
##1 is the DatabaseID
##2 is the maximum width of the cast result
##3 is the starting number.

##2 is needed because (under Local SQL at least) if the total size of the
resultant UNIQUE_ID is larger than TARGET.UNIQUE_ID you get a field mismatch
error message. This could be hardcoded if the length of ##1 is fixed which
in my case may not be across installations.

Anyway, at the end of the day it works perfectly and is about as fast as I
think it could possibly be.
Once again thanks to Larry for the great idea :-)

Leslie.