PC SOFT

FOROS PROFESIONALES
WINDEV, WEBDEV y WINDEV Mobile

|
|
|
FR
Inicio → WINDEV 2024 → DataSource with multiselect
DataSource with multiselect
Iniciado por veiudox, 16,jun. 2020 23:28 - 3 respuestas
#1
Publicado el 16,junio 2020 - 23:28
Hi


Is there a way to use a data source with a select that returns more than one table?



ds is data source


sql is string = [

select * from order where id = 1;

select * from details where orderid = 1;

]


IF HExecuteSQLQuery(ds,hQueryWithoutCorrection,sql) then


end



however it always returns only the first table;


there is something along these lines:

ds.table [1] (accessing result of the first select)
ds.table [2] (accessing result of the second select)

?
#2
Publicado el 17,junio 2020 - 12:17
Hi David,
I think it is not possible to do that...

What you can do is:

sql is string = [
select *
from order a
LEFT OUTER JOIN details b ON a.id=b.orderid
where a.id = 1
]

It is not the same but it works...

Bye
Andrea
#3
Miembro registrado
124 mensajes
Publicado el 17,junio 2020 - 12:57
Hi David,

I think you'll need to take a speed course on SQL first.
SQL-wise this simply can't work.
This however may work:
sql is string = [
select * from order
inner join orderdetail on order.orderid=orderdetail.orderid
where order.orderid = 1;
]
IF HExecuteSQLQuery(ds,hQueryWithoutCorrection,sql) THEN
...
END


This will return a combination of the order header data (repeating for as many order detail lines it has) and the order detail data.
If you also want to show order headers that don't have order detail lines simply replace "inner join" by "left outer join".

--
Best Regards - Met vriendelijke groeten - Cordialement
Peter Holemans
http://www.mcs2.eu
http://www.pixontri.eu
#4
Miembro registrado
124 mensajes
Publicado el 17,junio 2020 - 13:07
Hi David,

I think you'll need to take a speed course on SQL first.
SQL-wise this simply can't work.
This however may work:
sql is string = [
select * from order
inner join orderdetail on order.orderid=orderdetail.orderid
where order.orderid = 1;
]
IF HExecuteSQLQuery(ds,hQueryWithoutCorrection,sql) THEN
...
END


This will return a combination of the order header data (repeating for as many order detail lines it has) and the order detail data.
If you also want to show order headers that don't have order detail lines simply replace "inner join" by "left outer join".

It all depends how you want to make use of the data retrieved...

If you don't want a combined result set, alternatively you'll need to execute two different SQL's into two different data source variables on each of the tables. Next you can browse each of them in a nested fashion for example:
dsHeader, dsDetail are datasource
SQL is string = "Select * from order where orderid=x"
IF HExecuteQuery(dsHeader...) THEN
FOR EACH dsHeader
SQL = "Select * from orderdetail where orderid = "+dsHeader.orderid
IF HExecuteQuery(dsDetail...) THEN
FOR EACH dsDetail
...
END
END
END
END


--
Best Regards - Met vriendelijke groeten - Cordialement
Peter Holemans
http://www.mcs2.eu
http://www.pixontri.eu
→ Volver a WINDEV 2024