Board index » off-topic » Paradox scalar subqueries problem
iwaskia
Delphi Developer |
iwaskia
Delphi Developer |
Paradox scalar subqueries problem2004-11-06 07:36:21 PM off-topic8 my project use BDE+Paradox when i try to run this SQL select p.itemno, (select sum(inv.qty) from inventory inv where inv.itemno=p.itemno) as tqty from productitems p i got nothing in tqty, it seems the subquery cant get the value of p.itemno, if i put other value in there for example, inv.itemno='12345' i have the sum of qty of item 12345 is it the limition of BDE+Paradox ? thx |
Pau
Delphi Developer |
2004-11-09 01:33:57 AM
Re:Paradox scalar subqueries problem
try
select p.itemno, sum(inv.qty) as tqty from productitems p, inventory inv where inv.itemno=p.itemno Group by p.itemno "iwaskia" < XXXX@XXXXX.COM >escribi?en el mensaje Quotemy project use BDE+Paradox |
iwaskia
Delphi Developer |
2004-11-09 07:04:23 AM
Re:Paradox scalar subqueries problem
thx for reply
but i have to get the sum of 2 fields in 2 different tables, they cannot be joined together, they are many-to-many with the same itemno so i try to use subqueries for example select p.itemno, (select sum(inv.qty) from inventory inv where inv.itemno=p.itemno) as tqty, (select sum(invi.qty) from invoiceitem invi where invi.itemno=p.itemno) as iqty from productitems p Pau wrote: Quotetry {smallsort} |
Steven Green
Delphi Developer |
2004-11-09 07:43:51 AM
Re:Paradox scalar subqueries problem
iwaskia wrote:
Quotebut i have to get the sum of 2 fields in 2 different tables, they and in this can, you're still trying to develop the "one query" angle.. so why not just do two queries and put the results together after?.. Diamond Software Group www.diamondsg.com/main.htm Paradox Support & Sales - Corel CTech Paradox --------------------------------------------------- Diamond Sports Gems www.diamondsg.com/gemsmain.htm Trading Cards and other Sports Memorabilia --------------------------------------------------- |
Pau
Delphi Developer |
2004-11-09 04:28:31 PM
Re:Paradox scalar subqueries problem
Hi.
I don´t know if Paradox support this type of querys, but you can get the same ussing calculated fields. Try something similar to this Procedure Tform1.Query1CalcFields(DataSet: TDataSet); begin if queryCalulatedfield1.isnull then //Calculate the field one time only begin queryAux.Close; queryAux.sql.text := "subquery1"; queryAux.Open; if not queryAuxResultfield1.isnull then queryCalulatedfield1.asFloat := queryAuxResultfield.asFloat; end; if queryCalulatedfield2.isnull then begin queryAux.Close; queryAux.sql.text := "subquery2"; queryAux.Open; if not queryAuxResultfield2.isnull then queryCalulatedfield2.asFloat := queryAuxResultfield.asFloat; end; end; Sorry by my english Pau.. "iwaskia" < XXXX@XXXXX.COM >escribi?en el mensaje Quotethx for reply |