Helen,

Thank you very much, this is it.

Regards,

Zoran Zivkovic

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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