dBASE SQL Servers - More sql advise

Author

More sql advise

Bruce Mercer

2006-10-25, 7:23 am

Hi all,

I keep getting a "Invalid Parameter" error when trying to run this.
I have also tried using "EXISTS" with the same error. This matches
the local sql OLH, so what am I missing, an undocumented feature?

SILSERVERDATA = Advantage database
SILHPO = dBASE level 7 database
///////////////////////////////////////////////////////////////////////////////////
open database SILSERVERDATA
open database SILHPO

q = new query()
q.sql = [select o.rec_numb, o.tray_numb ;
from :SILSERVERDATA:ordma
st o ;
where (o.tray_numb IN (select tray_numb from :SILHPO:po_table))]
q.active = true

u1 = new updateSet()
u1.source = q.rowset
u1.destination = "BruceINTest.dbf"
u1.copy()
u1:= null
q1.active = false
q1:= null

close database SILSERVERDATA
close database SILHPO
/////////////////////////////////////////////////////////////////////////////////////
I hate ADS with dBASE !

TIA
Bruce

*Lysander*

2006-10-25, 7:23 am

Bruce Mercer schrieb:
[color=darkred]
> q.sql = [select o.rec_numb, o.tray_numb ;
> from :SILSERVERDATA:ordma
st o ;
> where (o.tray_numb IN (select tray_numb from :SILHPO:po_table))][
/color]

I don't know what your problem might be, since fortunately I do not have
to use ADS.

But you could try to restructure the sql-command, so that it does not
depend on a subselect. Maybe just the BDE is choking on cross-database
subselects...

For this you could try to go with an (INNER) join.
If I understand correctly what you want, this should in any case be
faster then using sub-selects.

select
o.rec_numb,
o.tray_numb
from
:silserverdata:ordma
st o
join
:silhpo:po_table p
on o.tray_numb = p.tray_numb

ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~
dB-D-A-CH // KOSTENLOSE Jahreskonferenz 2006
10. und 11. November 2006 in Marienheide
Info: www.dbdach.org/cgi-bin/dbdach.exe?thema=10004
Themen: www.dbdach.org/cgi-bin/themen.exe
Hitparade: www.dbdach.org/cgi-bin/hitparade.exe

Geoff Wass [dBVIPS]

2006-10-25, 7:23 am

In article <5vvJd1i6GHA.1772@news-server>, brucewm53remove@yaho
o.com
says...
> Hi all,
>
> I keep getting a "Invalid Parameter" error when trying to run this.
> I have also tried using "EXISTS" with the same error. This matches
> the local sql OLH, so what am I missing, an undocumented feature?
>
> SILSERVERDATA = Advantage database
> SILHPO = dBASE level 7 database
> ///////////////////////////////////////////////////////////////////////////////////
> open database SILSERVERDATA
> open database SILHPO
>
> q = new query()
> q.sql = [select o.rec_numb, o.tray_numb ;
> from :SILSERVERDATA:ordma
st o ;
> where (o.tray_numb IN (select tray_numb from :SILHPO:po_table))]
> q.active = true
>
> u1 = new updateSet()
> u1.source = q.rowset
> u1.destination = "BruceINTest.dbf"
> u1.copy()
> u1:= null
> q1.active = false
> q1:= null
>
> close database SILSERVERDATA
> close database SILHPO
> /////////////////////////////////////////////////////////////////////////////////////
> I hate ADS with dBASE !
>
> TIA
> Bruce
>
>
>
Bruce,

The BDE, and thus dBASE, supports a subset of SQL. When it comes to the
IN() clause, the contents can only be a value set (ie. a list of
values). It does not support the SELECT statement that you have used.

I am not experienced with ADS. You could try rearranging your query so
that it links your po_table to the ordmast table. You could also try
creating a temporary table by passing an SQL statement directly to ADS
to execute for you using the DATABASE.executeSQL() method.

The SQL Help included with dBASE PLUS can help you to see which types of
SQL commands are supported.

--
Geoff Wass [dBVIPS]

*Lysander*

2006-10-25, 7:23 am

Geoff Wass [dBVIPS] schrieb:
> You could also try
> creating a temporary table by passing an SQL statement directly to ADS
> to execute for you using the DATABASE.executeSQL() method.

A very good advice, as long as only one database-type is affected.
In Bruce's case he needs to combine 2 database-types in one
sql-statement, so bypassing the BDE via db.executeSQL() would not work.

Also, he wants to save the results to a dBase-table, so he would need
db.executeSQL() to return rows, which also is not possible.

ciao,
André
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~
dB-D-A-CH // KOSTENLOSE Jahreskonferenz 2006
10. und 11. November 2006 in Marienheide
Info: www.dbdach.org/cgi-bin/dbdach.exe?thema=10004
Themen: www.dbdach.org/cgi-bin/themen.exe
Hitparade: www.dbdach.org/cgi-bin/hitparade.exe

Ivar B. Jessen

2006-10-25, 7:23 am

On Sun, 8 Oct 2006 03:03:23 -0400, in dbase.sql-servers,
Subject: Re: More sql advise,
Message-ID: <MPG. 1f926cc8135be0d59896
94@news.dbase.com>,
Geoff Wass [dBVIPS] < gswassREMOVE_ME@attg
lobal.net> wrote:

>The BDE, and thus dBASE, supports a subset of SQL. When it comes to the
>IN() clause, the contents can only be a value set (ie. a list of
>values). It does not support the SELECT statement that you have used.

Hmm, look at the explanation of the IN predicate in localSQL.hlp or run the code below my signature
to see that IN supports a subquery <g>

*****
The comparison set can also be the result set from a subquery. The subquery may return multiple
rows, but must only return a single column for comparison.

SELECT C.Company, C.State

FROM Customer C
WHERE (C.State IN
(SELECT R.State
FROM Regions R
WHERE (R.Region = "Pacific")))
*****

>I am not experienced with ADS. You could try rearranging your query so
>that it links your po_table to the ordmast table. You could also try
>creating a temporary table by passing an SQL statement directly to ADS
>to execute for you using the DATABASE.executeSQL() method.
>
>The SQL Help included with dBASE PLUS can help you to see which types of
>SQL commands are supported.

It is likely that the IN predicate does not work across different databases, but it does not say so
in the help file :-)

Ivar B. Jessen

//-----
close all

open database dBASESamples
open database dBASEContax

q = new query()
q.sql = 'select f.ID, f.Name ;
from :dBASESamples:Fish f ;
where ( f.ID IN ;
( select a."Key" ;
from :dBASEContax:appoint
s a ;
where a."Key" < 4) )'
q.active = true

u1 = new updateSet()
u1.source = q.rowset
u1.destination = "BruceINTest.dbf"
u1.copy()
u1:= null
q.active = false
q:= null

close database dBASESamples
close database dBASEContax

use BruceINTest
browse
//-----

Bruce Mercer

2006-10-25, 7:23 am

Thanks to all !

I'm using Lysander's ( I hope this is correct name) suggestion, because
it does make more sense. The problem with ADS is that you can't use
their indexes. So you have to either create a temp dBASE table and create
a index and then requery the temp table, or loop through the native ADS
query to get the data. Either way is pretty slow, to me. Thanks again for
everyone's help.

Hoping for better connectivity,
Bruce

Geoff Wass [dBVIPS]

2006-10-25, 7:23 am

In article <cofAmJr6GHA.416@news-server>, nobody@nowhere.com says...
> Andr=E9
>=20
Andr=E9,

In order to work with tables all in one database, I guess some data may=20
have to be duplicated in order to get the idea to work. Depending on the=20
amount of data and its volatility, it may be feasible.

Otherwise, he could look at trying to use separate DATABASE, QUERY and=20
ROWSET objects (one for each table) and linking the tables by=20
..masterRowset rather than relying on doing everything in one shot with=20
SQL.

--
Geoff Wass [dBVIPS]

Eric Logan

2006-10-25, 7:23 am

> "Geoff Wass wrote ...
> The BDE, and thus dBASE, supports a subset of SQL. When it comes to the
> IN() clause, the contents can only be a value set (ie. a list of
> values). It does not support the SELECT statement that you have used.

I routinely run SQL commands such as the following:
select * from dlyharv2 where sat_end in (select distinct sat_end from
chincpue)
E.L.

Sponsored Links