Can this be done with a Query Only either QBE or Local SQL ?

Can this be done with a Query Only either QBE or Local SQL ?

Post by Leslie Milbur » Fri, 27 Jun 2003 20:30:04



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.

 
 
 

Can this be done with a Query Only either QBE or Local SQL ?

Post by Dennis Santor » Fri, 27 Jun 2003 21:20:30


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

 
 
 

Can this be done with a Query Only either QBE or Local SQL ?

Post by Steven Gree » Fri, 27 Jun 2003 21:30:44



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

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

 
 
 

Can this be done with a Query Only either QBE or Local SQL ?

Post by Larry DiGiovann » Fri, 27 Jun 2003 22:12:41


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

 
 
 

Can this be done with a Query Only either QBE or Local SQL ?

Post by Leslie Milbur » Fri, 27 Jun 2003 22:15:15


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

 
 
 

Can this be done with a Query Only either QBE or Local SQL ?

Post by Mike Irwin [CTech » Fri, 27 Jun 2003 22:44:08


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

 
 
 

Can this be done with a Query Only either QBE or Local SQL ?

Post by Larry DiGiovann » Fri, 27 Jun 2003 22:52:55



> 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'.

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

 
 
 

Can this be done with a Query Only either QBE or Local SQL ?

Post by Leslie Milbur » Fri, 27 Jun 2003 22:41:40


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



> > 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'.

> 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

 
 
 

Can this be done with a Query Only either QBE or Local SQL ?

Post by Larry DiGiovann » Fri, 27 Jun 2003 23:11:19



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

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

 
 
 

Can this be done with a Query Only either QBE or Local SQL ?

Post by Larry DiGiovann » Fri, 27 Jun 2003 23:14:36



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

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

 
 
 

Can this be done with a Query Only either QBE or Local SQL ?

Post by Mike Irwin [CTech » Fri, 27 Jun 2003 23:51:20


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




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

> 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

 
 
 

Can this be done with a Query Only either QBE or Local SQL ?

Post by Leslie Milbur » Sat, 28 Jun 2003 21:18:20


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.

 
 
 

1. Query performance issue - What Am I doing wrong?

IN tblTrans i have a group of transactions that all come under the same
FileName.  I have created a query to extract all the distinct file names
into tblFiles to create FileID's for each file.  I then run a query to Join
tblFiles to tblTrans so that I will list a FileID and the Transaction
INformation.
----
SELECT tf.FileID, edi.* from tblTrans edi
JOIN tblFiles tf ON tf.FileName = edi.FileName
----

tblTrans will have 70K rows, tblFile will have about 1.7K rows after I run
the extract query.

That JOIN query Runs SLOW and it kills my client machine for some reason.
Unlike other queries, this particular query kinda spits out chunks of  data
and will not complete after 15K rows in the tblTrans.  What am I doing
wrong?

I have added an index to tblTrans.FileName  ? Right direction?  Thanks.

CREATE TABLE tblTrans(
TransactionID int NOT NULL,
something1 varchar(15) NULL,
something2 varchar(15) NULL,
something3 varchar(15) NULL,
FileName varchar(64) NOT NULL
)

CREATE TABLE tblFiles(
FileID IDENTITY Int NOT NULL,
FileName varchar(64) NOT NULL
)

--

[Shaun Farrugia] ## [Programmer] ##  [DTE Energy Operations]

o_O

2. synonym in SQL server?

3. What am I doing wrong (Queries Paradox 7)?

4. HELP SQL 6.5

5. SQL problem, what am I doing wong?

6. VFP 3.0 - Form Initialization Error

7. ADO and SQL: what am I doing wrong ?

8. BCPSENDROW on Sys 10 error??

9. SQL What am I doing wrong ?

10. What am I doing wrong (RTRIM, PL/SQL)

11. Return either a table either another table (different schema)

12. SQL Query in QBE Grid

13. Getting SQL from QBE queries similar to Database Desktop