Re: Select inside select

sqlexpress

Thread Starter: Osvaldo Ramirez Started: 1/20/2007 8:35 PM UTC

Replies: 3

1/20/2007 8:35 PM UTC

Re: Select inside select

Reply Quote

Osvaldo Ramirez

Dear Boris Borzic

The statement dint work, because my client has a MySQL 4.0 server
and this version dont accept subquerys, So he said, that will try to
update the MySQL.

Anyway I make this code, and I would like to know your point of view.

Thanks a lot
Osvaldo Ramirez
P.S. Please, tell me what news are in the SQLExpress++

Code:

cSQL := "select tra_id,tra_fecha,tra_status,lab_id,tra_rep from transfe where tra_fecha >= '"+dtos(dFecha1)+"' and tra_fecha <= '"+dtos(dFecha2)+"' order by tra_id"

oTransfer := SQLDataSet():new(cSQL,oConnection)

if oTransfer:reccount() > 0

do while ! oTransfer:eof()
cIn += alltrim(str(oTransfer:fieldget("tra_id")))
oTransfer:skip()
cIn += iif( ! oTransfer:eof() , "," , "" )
enddo

cSQL := "select * from sub_tra where tra_id in ("+cIn+") order by tra_id,art_barcod"
oDetails := SQLDataSet():new(cSQL,oConnection)
if ( nRecnos := oDetails:reccount() ) >= 1
oDetails:SetRelation( oTransfer )

.... my report

2/17/2007 12:32 AM UTC

Re: Select inside select

Reply Quote

Osvaldo Ramirez

Thanks Boris Borzic

Tell me, inner Join is more fast that have two cursor with a relation ?

This is my new cursor only one :

cSQL := "select transfe.tra_id,transfe.tra_fecha,transfe.tra_status,transfe.lab_id,"+;
"transfe.tra_rep,sub_tra.art_barcod,sub_tra.st_cant,sub_tra.suc_id,"+;
"sub_tra.st_entrada,sub_tra.st_oferta from transfe inner join sub_tra on transfe.tra_id = "+;
"sub_tra.tra_id where tra_fecha >= '"+dtos(dFecha1)+"' and tra_fecha <= '"+dtos(dFecha2)+"' "

It look ok ?

Best Regards
Osvaldo Ramirez

1/21/2007 6:00 PM UTC

Re: Select inside select

Reply Quote

Boris Borzic

Perhaps you can reformulate the subquery as an inner join. Here is an example taken from SQL Server help file:

SELECT *
FROM HumanResources.Employee AS e
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
ORDER BY c.LastName

Best regards,
Boris Borzic
-- news://news.Xb2.NET
http://www.Xb2.NET
http://www.SQLExpress.net
industrial strength Xbase++ development tools

Osvaldo Ramirez <ramirezosvaldo@prodigy.net.mx> wrote in news:e50sub5ogpjr4w0.200120072136@ANDREA:

Dear Boris Borzic
The statement dint work, because my client has a MySQL 4.0 server
and this version dont accept subquerys, So he said, that will try to
update the MySQL.
Anyway I make this code, and I would like to know your point of view.
Thanks a lot
Osvaldo Ramirez
P.S. Please, tell me what news are in the SQLExpress++
Code:
cSQL := "select tra_id,tra_fecha,tra_status,lab_id,tra_rep from transfe where tra_fecha >= '"+dtos(dFecha1)+"' and tra_fecha <= '"+dtos(dFecha2)+"' order by tra_id"

oTransfer := SQLDataSet():new(cSQL,oConnection)
if oTransfer:reccount() > 0
do while ! oTransfer:eof()
cIn += alltrim(str(oTransfer:fieldget("tra_id")))
oTransfer:skip()
cIn += iif( ! oTransfer:eof() , "," , "" )
enddo
cSQL := "select * from sub_tra where tra_id in ("+cIn+") order by tra_id,art_barcod"
oDetails := SQLDataSet():new(cSQL,oConnection)
if ( nRecnos := oDetails:reccount() ) >= 1
oDetails:SetRelation( oTransfer )
.... my report

2/17/2007 1:44 PM UTC

Re: Select inside select

Reply Quote

Boris Borzic

Osvaldo Ramirez <ramirezosvaldo@prodigy.net.mx> wrote in news:kegyuvbyfgjsmha.170220070130@ANDREA:

Tell me, inner Join is more fast that have two cursor with a relation ?

inner join will be faster

Best regards,
Boris Borzic
-- news://news.Xb2.NET
http://www.Xb2.NET
http://www.SQLExpress.net
industrial strength Xbase++ development tools