At 08:37 AM 7/05/2005 +0000, you wrote:

>Can someone help with this:
>
>I have a folowing tables:
>
>CREATE TABLE CENOVNIK (
> EAPARTNO VARCHAR(15),
> SP DOUBLE PRECISION
>);
>
>CREATE TABLE KURS_POREZ (
> ID INTEGER NOT NULL,
> KURS NUMERIC(15,2),
> POREZ NUMERIC(15,2)
>);
>
>CREATE TABLE WATER_PUMPS_PARTS (
> APPL_ID INTEGER,
> PARTNO VARCHAR(9),
> EAPARTNO VARCHAR(11),
> DESCRIPTION VARCHAR(50),
> PART_OE VARCHAR(16),
> SEQ DOUBLE PRECISION
>);
>
>I would like to make query which should like something like folowing:
>
>SELECT DISTINCT PARTNO
> , EAPARTNO
> , DESCRIPTION
> , PART_OE AS OE
> , (CAST ((CENOVNIK.SP*KURS_POREZ.KURS*KURS_POREZ.POREZ/1.00) AS
>DECIMAL(15,2))) AS MPC
>FROM WATER_PUMPS_PARTS, KURS_POREZ
>LEFT OUTER JOIN CENOVNIK ON
>(WATER_PUMPS_PARTS.EAPARTNO=CENOVNIK.EAPARTNO)
>
>I would like always to multiply column CENOVNIK.SP with
>KURS_POREZ.KURS*KURS_POREZ. Query is not designed in run time, but it
>should
>be fixed (something like I tried).
>
>Table KURS_POREZ always has only one row.
>
>The query I wrote doesn't return error, but also doesn't return any
>result.

Of course, when I prematurely hit the Send button, I'd just bumped into the
cross join, as you have done, too, and found the inevitable problem.

You could pull those values from your one-row table of calc factors via two
non-correlated subqueries:

SELECT
wpp.PARTNO
, wpp.EAPARTNO
, wpp.DESCRIPTION
, wpp.PART_OE AS OE /* why? */
, CAST ((c.SP * kp.KURS * kp.POREZ/1.00) AS DECIMAL(15,2)) AS MPC

CAST ((c.SP * (select kp1.KURS from KURS_POREZ kp1)
* (SELECT kp2.POREZ from KURS_POREZ kp2) /1.00) AS DECIMAL(15,2)) AS MPC
FROM WATER_PUMPS_PARTS wpp
LEFT JOIN CENOVNIK c
ON wpp.EAPARTNO=c.EAPARTNO

I would like always to multiply column CENOVNIK.SP with
KURS_POREZ.KURS*KURS_POREZ.

More elegant and speedy would be a selectable stored proc that reads the
KURS_POREZ record only once, into variables.

./hb