Board index » off-topic » Paradox scalar subqueries problem

Paradox scalar subqueries problem


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

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

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:
Quote
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
news:418cb61c$ XXXX@XXXXX.COM ...

>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



 

{smallsort}

Re:Paradox scalar subqueries problem

iwaskia wrote:
Quote
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
there's an old saying in the Paradox world.. "just because it *can* be
done with one query, doesn't mean it *should* be done with one query"..
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
---------------------------------------------------
 

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
Quote
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:
>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
>news:418cb61c$ XXXX@XXXXX.COM ...
>
>>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
>
>
>