X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id F0F2330FED7
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Jun 2003 11:23:24 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 30592-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 26 Jun 2003 08:23:14 -0300 (ADT)
Received: from vadangyal.vnet.hu (mail.vnet.hu [213.163.59.4])
	by svr1.postgresql.org (Postfix) with ESMTP id C4D9530FEE8
	for <pgsql-sql@postgresql.org>; Thu, 26 Jun 2003 08:23:13 -0300 (ADT)
Received: from compaq (adsl213150.vnet.hu [62.77.213.150])
	by mail.vnet.hu (iPlanet Messaging Server 5.1 Patch 1 (built Jun 6
	2002)) with ESMTPA id <0HH300JEG68FYK@mail.vnet.hu> for
	pgsql-sql@postgresql.org; Thu, 26 Jun 2003 13:21:52 +0200 (CEST)
Date: Thu, 26 Jun 2003 13:21:27 +0200
From: =?iso-8859-1?Q?Egy=FCd_Csaba?= <csegyud@freemail.hu>
Subject: Join or Where?
To: pgsql-sql@postgresql.org
Reply-To: =?iso-8859-1?Q?Egy=FCd_Csaba?= <csegyud@freemail.hu>
Message-id: <003301c33bd5$198f7250$230a0a0a@compaq>
MIME-version: 1.0
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2727.1300
X-Mailer: Microsoft Outlook Express 6.00.2720.3000
Content-type: multipart/alternative;
	boundary="Boundary_(ID_cC/tIInhUME7ElTWVOsEKQ)"
X-Priority: 3
X-MSMail-priority: Normal
References: <FF851C7EEB75954F9BCFB5CA117AB1EC61A446@delta.nulinkinc.com>
X-Archive-Number: 200306/293
X-Sequence-Number: 14051

This is a multi-part message in MIME format.

--Boundary_(ID_cC/tIInhUME7ElTWVOsEKQ)
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7BIT

Urgent Help : Use of return from function/procedure.Hi,
could anybody tell me what can be the difference between these queries?
There are a big difference but I can't understand the reasons. For some
reasons the firs query calculates getup column in a wrong way. It allways
devides with 10. (???) While the getupquantity column comes well.

With the second query everything is ok.

Thank you,

-- Csaba

----------------------------------------------------------------------------
-------
select (select getupquantity from t_products where id=productid) as
getupquantity,
       quantity,
       (select quantity/getupquantity) as getup
from t_stockchanges, t_products
where (getupquantity<>0)
limit 30;

RESULT:
 getupquantity | quantity | getup
---------------+----------+-------
            10 |      100 |    10
            10 |    10600 |  1060
            15 |    15150 |  1515
            13 |    13650 |  1365
          17.5 |    17500 |  1750
          5.75 |     5750 |   575
           2.5 |     2500 |   250
           1.5 |     1500 |   150
            16 |    16000 |  1600
             2 |     2000 |   200
           5.5 |     5500 |   550
           4.5 |     4500 |   450
             2 |     2000 |   200
----------------------------------------------------------------------------
-------
select t_products.getupquantity as getupquantity,
       quantity,
       (select t_stockchanges.quantity/t_products.getupquantity) as getup
from t_stockchanges
join t_products on (t_products.id=productid)
where (getupquantity<>0)
limit 30;

RESULT:
getupquantity | quantity | getup
---------------+----------+-------
            10 |      100 |    10
            10 |    10600 |  1060
            15 |    15150 |  1010
            13 |    13650 |  1050
          17.5 |    17500 |  1000
          5.75 |     5750 |  1000
           2.5 |     2500 |  1000
           1.5 |     1500 |  1000
            16 |    16000 |  1000
             2 |     2000 |  1000
           5.5 |     5500 |  1000
           4.5 |     4500 |  1000
             2 |     2000 |  1000
--------------------------------------------------------------------

Hi,
could anybody tell me what can be the difference between these queries?
There are a big difference but I can't understand the reasons. For some
reasons the firs query calculates getup column in a wrong way. It allways
devides with 10. (???) While the getupquantity column comes well.

With the second query everything is ok.

Thank you,

-- Csaba

----------------------------------------------------------------------------
-------
select (select getupquantity from t_products where id=productid) as
getupquantity,
       quantity,
       (select quantity/getupquantity) as getup
from t_stockchanges, t_products
where (getupquantity<>0)
limit 30;

RESULT:
 getupquantity | quantity | getup
---------------+----------+-------
            10 |      100 |    10
            10 |    10600 |  1060
            15 |    15150 |  1515
            13 |    13650 |  1365
          17.5 |    17500 |  1750
          5.75 |     5750 |   575
           2.5 |     2500 |   250
           1.5 |     1500 |   150
            16 |    16000 |  1600
             2 |     2000 |   200
           5.5 |     5500 |   550
           4.5 |     4500 |   450
             2 |     2000 |   200
----------------------------------------------------------------------------
-------
select t_products.getupquantity as getupquantity,
       quantity,
       (select t_stockchanges.quantity/t_products.getupquantity) as getup
from t_stockchanges
join t_products on (t_products.id=productid)
where (getupquantity<>0)
limit 30;

RESULT:
getupquantity | quantity | getup
---------------+----------+-------
            10 |      100 |    10
            10 |    10600 |  1060
            15 |    15150 |  1010
            13 |    13650 |  1050
          17.5 |    17500 |  1000
          5.75 |     5750 |  1000
           2.5 |     2500 |  1000
           1.5 |     1500 |  1000
            16 |    16000 |  1000
             2 |     2000 |  1000
           5.5 |     5500 |  1000
           4.5 |     4500 |  1000
             2 |     2000 |  1000
--------------------------------------------------------------------


--Boundary_(ID_cC/tIInhUME7ElTWVOsEKQ)
Content-type: text/html; charset=iso-8859-1
Content-transfer-encoding: 7BIT

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Urgent Help : Use of return from function/procedure.</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2726.2500" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#fffbf0>
<DIV>Hi,<BR>could anybody tell me what can be the difference between these 
queries?<BR>There are a big difference but I can't understand the reasons. For 
some<BR>reasons the firs query calculates getup column in a wrong way. It 
allways<BR>devides with 10. (???) While the getupquantity column comes 
well.<BR><BR>With the second query everything is ok.<BR><BR>Thank you,<BR><BR>-- 
Csaba<BR><BR>----------------------------------------------------------------------------<BR>-------<BR>select 
(select getupquantity from t_products where id=productid) 
as<BR>getupquantity,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
quantity,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (select 
quantity/getupquantity) as getup<BR>from t_stockchanges, t_products<BR>where 
(getupquantity&lt;&gt;0)<BR>limit 30;<BR><BR>RESULT:<BR>&nbsp;getupquantity | 
quantity | 
getup<BR>---------------+----------+-------<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
10 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100 |&nbsp;&nbsp;&nbsp; 
10<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 
|&nbsp;&nbsp;&nbsp; 10600 |&nbsp; 
1060<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15 
|&nbsp;&nbsp;&nbsp; 15150 |&nbsp; 
1515<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13 
|&nbsp;&nbsp;&nbsp; 13650 |&nbsp; 
1365<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17.5 
|&nbsp;&nbsp;&nbsp; 17500 |&nbsp; 
1750<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.75 
|&nbsp;&nbsp;&nbsp;&nbsp; 5750 |&nbsp;&nbsp; 
575<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 2500 |&nbsp;&nbsp; 
250<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 1500 |&nbsp;&nbsp; 
150<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16 
|&nbsp;&nbsp;&nbsp; 16000 |&nbsp; 
1600<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
2 |&nbsp;&nbsp;&nbsp;&nbsp; 2000 |&nbsp;&nbsp; 
200<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 5500 |&nbsp;&nbsp; 
550<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 4500 |&nbsp;&nbsp; 
450<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
2 |&nbsp;&nbsp;&nbsp;&nbsp; 2000 |&nbsp;&nbsp; 
200<BR>----------------------------------------------------------------------------<BR>-------<BR>select 
t_products.getupquantity as 
getupquantity,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
quantity,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (select 
t_stockchanges.quantity/t_products.getupquantity) as getup<BR>from 
t_stockchanges<BR>join t_products on (t_products.id=productid)<BR>where 
(getupquantity&lt;&gt;0)<BR>limit 30;<BR><BR>RESULT:<BR>getupquantity | quantity 
| 
getup<BR>---------------+----------+-------<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
10 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100 |&nbsp;&nbsp;&nbsp; 
10<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 
|&nbsp;&nbsp;&nbsp; 10600 |&nbsp; 
1060<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15 
|&nbsp;&nbsp;&nbsp; 15150 |&nbsp; 
1010<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13 
|&nbsp;&nbsp;&nbsp; 13650 |&nbsp; 
1050<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17.5 
|&nbsp;&nbsp;&nbsp; 17500 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.75 
|&nbsp;&nbsp;&nbsp;&nbsp; 5750 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 2500 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 1500 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16 
|&nbsp;&nbsp;&nbsp; 16000 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
2 |&nbsp;&nbsp;&nbsp;&nbsp; 2000 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 5500 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 4500 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
2 |&nbsp;&nbsp;&nbsp;&nbsp; 2000 |&nbsp; 
1000<BR>--------------------------------------------------------------------<BR>
<DIV>Hi,<BR>could anybody tell me what can be the difference between these 
queries?<BR>There are a big difference but I can't understand the reasons. For 
some<BR>reasons the firs query calculates getup column in a wrong way. It 
allways<BR>devides with 10. (???) While the getupquantity column comes 
well.<BR><BR>With the second query everything is ok.<BR><BR>Thank you,<BR><BR>-- 
Csaba<BR><BR>----------------------------------------------------------------------------<BR>-------<BR>select 
(select getupquantity from t_products where id=productid) 
as<BR>getupquantity,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
quantity,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (select 
quantity/getupquantity) as getup<BR>from t_stockchanges, t_products<BR>where 
(getupquantity&lt;&gt;0)<BR>limit 30;<BR><BR>RESULT:<BR>&nbsp;getupquantity | 
quantity | 
getup<BR>---------------+----------+-------<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
10 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100 |&nbsp;&nbsp;&nbsp; 
10<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 
|&nbsp;&nbsp;&nbsp; 10600 |&nbsp; 
1060<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15 
|&nbsp;&nbsp;&nbsp; 15150 |&nbsp; 
1515<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13 
|&nbsp;&nbsp;&nbsp; 13650 |&nbsp; 
1365<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17.5 
|&nbsp;&nbsp;&nbsp; 17500 |&nbsp; 
1750<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.75 
|&nbsp;&nbsp;&nbsp;&nbsp; 5750 |&nbsp;&nbsp; 
575<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 2500 |&nbsp;&nbsp; 
250<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 1500 |&nbsp;&nbsp; 
150<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16 
|&nbsp;&nbsp;&nbsp; 16000 |&nbsp; 
1600<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
2 |&nbsp;&nbsp;&nbsp;&nbsp; 2000 |&nbsp;&nbsp; 
200<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 5500 |&nbsp;&nbsp; 
550<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 4500 |&nbsp;&nbsp; 
450<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
2 |&nbsp;&nbsp;&nbsp;&nbsp; 2000 |&nbsp;&nbsp; 
200<BR>----------------------------------------------------------------------------<BR>-------<BR>select 
t_products.getupquantity as 
getupquantity,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
quantity,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (select 
t_stockchanges.quantity/t_products.getupquantity) as getup<BR>from 
t_stockchanges<BR>join t_products on (t_products.id=productid)<BR>where 
(getupquantity&lt;&gt;0)<BR>limit 30;<BR><BR>RESULT:<BR>getupquantity | quantity 
| 
getup<BR>---------------+----------+-------<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
10 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100 |&nbsp;&nbsp;&nbsp; 
10<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 
|&nbsp;&nbsp;&nbsp; 10600 |&nbsp; 
1060<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15 
|&nbsp;&nbsp;&nbsp; 15150 |&nbsp; 
1010<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13 
|&nbsp;&nbsp;&nbsp; 13650 |&nbsp; 
1050<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17.5 
|&nbsp;&nbsp;&nbsp; 17500 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.75 
|&nbsp;&nbsp;&nbsp;&nbsp; 5750 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 2500 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 1500 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16 
|&nbsp;&nbsp;&nbsp; 16000 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
2 |&nbsp;&nbsp;&nbsp;&nbsp; 2000 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 5500 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.5 
|&nbsp;&nbsp;&nbsp;&nbsp; 4500 |&nbsp; 
1000<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
2 |&nbsp;&nbsp;&nbsp;&nbsp; 2000 |&nbsp; 
1000<BR>--------------------------------------------------------------------<BR></DIV></DIV></BODY></HTML>

--Boundary_(ID_cC/tIInhUME7ElTWVOsEKQ)--