select distinct in function

gezeala

This function is not working please help me...
it keeps on returning the same sodid...

i think its because of the distinct..

Main thing to consider is the min(delivery_date) for each row..

-- Function: fn_sopartcustmodel(int4, int4)
CREATE or replace FUNCTION "fn_sopartcustmodel"("int4", "int4") RETURNS "bool" AS '

declare
rloop record;
modelid alias for $1;
customerid alias for $2;

partcustid int4;
sonumber char(15);
soddeliverydate date;
sodquantity int4;
socustlot char(8);
partlongcode char(30);
partname char(30);
partqtyperbox int4;
partshortcode char(15);
sodid int4;
begin

delete from temp_sopartcustmodel;

for rloop in
select *
from vw_modelpartcust
where
model_id = modelid and customer_id = customerid

loop
select part_cust_id, part_longcode, part_name, part_qty_perbox, part_shortcode into partcustid, partlongcode, partname, partqtyperbox, partshortcode
from vw_modelpartcust
where vw_modelpartcust.part_cust_id = rloop.part_cust_id and
vw_modelpartcust.model_id = rloop.model_id and
vw_modelpartcust.customer_id = rloop.customer_id;

select distinct(so_number),sod_delivery_date, sod_quantity,so_cust_lot,sod_id
into sonumber,soddeliverydate,sodquantity,socustlot,sodid
from vw_so_detail
where
vw_so_detail.sod_delivery_date = (
select min(delivery_date)
from (

select sod_delivery_date as delivery_date

from vw_so_detail
where

vw_so_detail.part_cust_id = rloop.part_cust_id
and
vw_so_detail.so_status=''OPEN''
)
as b
)
and
vw_so_detail.part_cust_customer_id=rloop.customer_id

and
vw_so_detail.part_model_id=rloop.model_id
and
vw_so_detail.so_status=''OPEN'';

insert into temp_sopartcustmodel
values (partcustid , sonumber, soddeliverydate, sodquantity, socustlot, partlongcode, partname, partqtyperbox, partshortcode, sodid);

end loop;
return 1;
end;
' LANGUAGE 'plpgsql';

thanks in advance...

Anon

select min(delivery_date)
from (
select sod_delivery_date as delivery_date
from vw_so_detail
where
vw_so_detail.part_cust_id = rloop.part_cust_id
and
vw_so_detail.so_status=''OPEN''
)

I need to know the table structure to be real help, but the above is what distinct is hinging upon, particularly rloop.part_cust_id, as the other criteria are constant in the function.

As long as you are getting distinct part_cust_id in rloop, then you should not be ending up with duplicate sodid's. If not, then you will of course be getting the same row based on min date...

gezeala

Here are the lists of table and views involved:

-- Table: temp_sopartcustmodel
CREATE TABLE "temp_sopartcustmodel" (
"partid" int4,
"sonumber" char(15),
"soddeliverydate" date,
"sodquantity" int4,
"socustlot" char(8),
"partlongcode" char(30),
"partname" char(30),
"partqtyperbox" int4
)

-- View: vw_so_detail
CREATE VIEW "vw_so_detail" AS SELECT sod.sod_id, sod.sod_so_id, sod.sod_part_cust_id, sod.sod_quantity, sod.sod_unitprice, sod.sod_delivery_date, so.so_id, so.so_number, so.so_cust_lot, so.so_model_id, so.so_customer_id, so.so_issue_date, so.so_delivery_date, so.so_pezapermit_num, so.so_pezaform_num, so.so_si_num, so.so_payment_id, so.so_currency_id, so.so_mpt_date, so.so_remarks, so.so_status, pc.part_cust_id, pc.part_cust_part_id, pc.part_cust_longcode, pc.part_cust_shortcode, pc.part_cust_customer_id, pc.part_cust_unitprice, pc.part_cust_currency_id, p.part_id, p.part_name, p.part_longcode, p.part_shortcode, p.part_model_id, p.part_qty_perbox, cu.currency_id, cu.currency_code, cu.currency_desc, cu.currency_symbol, c.customer_id, c.customer_code, c.customer_desc, c.customer_currency_id, c.customer_office_add, c.customer_billing_add, c.customer_shipping_add, c.customer_fax_num, c.customer_contact_num, c.customer_contact, c.customer_email FROM (((((sales_order_detail sod LEFT JOIN sales_order so ON ((sod.sod_so_id = so.so_id))) LEFT JOIN part_cust pc ON ((sod.sod_part_cust_id = pc.part_cust_id))) LEFT JOIN part p ON ((p.part_id = pc.part_cust_part_id))) LEFT JOIN currency cu ON ((cu.currency_id = pc.part_cust_currency_id))) LEFT JOIN customer c ON ((c.customer_id = pc.part_cust_customer_id)));

-- View: vw_modelpartcust
CREATE VIEW "vw_modelpartcust" AS SELECT model.model_id, model.model_code, model.model_desc, part.part_id, part.part_name, part.part_longcode, part.part_shortcode, part.part_model_id, part.part_qty_perbox, part_cust.part_cust_id, part_cust.part_cust_part_id, part_cust.part_cust_longcode, part_cust.part_cust_shortcode, part_cust.part_cust_customer_id, part_cust.part_cust_unitprice, part_cust.part_cust_currency_id, customer.customer_id, customer.customer_code, customer.customer_desc, customer.customer_currency_id, customer.customer_office_add, customer.customer_billing_add, customer.customer_shipping_add, customer.customer_fax_num, customer.customer_contact_num, customer.customer_contact, customer.customer_email FROM model, part, part_cust, customer WHERE (((model.model_id = part.part_model_id) AND (part.part_id = part_cust.part_cust_part_id)) AND (customer.customer_id = part_cust.part_cust_customer_id));

I'd like to show you the output of the function..

select fn_sopartcustmodel(2,3);
select * from temp_sopartcustmodel order by partname,partshortcode ;

this would give..

partcustid | sonumber | soddeliverydate | sodquantity |socustlot | partlongcode | partname | partqtyperbox | partshortcode | sodid
------------+--------------- +----------------- +------------- +----------+----------------------+-----------------+-----------------+-------------------+-------
49 | so003CDI | 2002-06-01 | 1000 | 3-4 | 321861-0820C700 | Main Chassis | 34 | FAS-6500C | 201
56 | so003CDI | 2002-06-01 | 1000 | 3-4 | 321882-0560A700 | Screw | 34 | FSC-6578 | 201
104 | so003CDI | 2002-06-01 | 1000 | 3-4 | 321871-17400700 | Spacer | 34 | SE-6544 | 201
96 | so003CDI | 2002-06-01 | 1000 | 3-4 | 321852-2300A700 | Holder | 44 | HE-6525A | 201
73 | so003CDI | 2002-06-01 | 1000 | 3-4 | 321821-28600700 | Lever R | 34 | FUL-6555 | 201
67 | so003CDI | 2002-06-01 | 1000 | 3-4 | 321821-28500700 | Shaft | 23 | G-645 | 201
46 | so003CDI | 2002-06-01 | 1000 | 3-4 | 321811-1480B700 | Shaft | 1504 | G-650A | 201
28 | so003CDI | 2002-06-01 | 1000 | 3-4 | 321821-24900700 | Lever | 2500 | UL-6524 | 201
(9 rows)

notice that the same sodid appears..

here is the table sales_order

so_id | so_number | so_cust_lot | so_model_id | so_customer_id | so_issue_date | so_delivery_date | so_pezapermit_num | so_pezaform_num | so_si_num | so_payment_id | so_currency_id | so_mpt_date | so_remarks | so_status
-------+----------------+---------------+--------------+--------------------+-----------------+---------------------+------------------------+----------------------+--------------+-------------------+------------------+----------------+----------------+------------
3 | so003CDI | 3-4 | 2 | 3 | 2002-06-05 | 2002-06-04 | 3333 | 3333 | 3333 | 2 | 3 | 2002-02-20 | testing | OPEN

11 | so011FTCP | 2-1 | 3 | 8 | 2002-06-05 | 2002-01-14 | 312-111 | 41-1334 | 323-1333 | 4 | 3 | 2002-06-19 | test | OPEN
2 | so002ALE | 2-3 | 2 | 2 | 2002-06-06 | 2002-06-07 | 2222 | 2222 | 2222 | 1 | 3 | 2002-01-08 | test | OPEN 16 | so015CDI | 3-3 | 2 | 3 | 2002-06-06 | 2002-01-01 | 32303-4 | 42-4 | 4003-3 | 3 | 4 | 2002-06-05 | TEST | OPEN

1 | so001ALE | 1-2 | 2 | 2 | 2002-06-01 | 2002-06-07 | 11111 | 11111 | 11111 | 7 | 1 | 2001-12-11 | test | OPEN

(5 rows)
I'm supposed to get so003 or so015 sales orders as the output of the function. In the sales_order_detail table i've set the delivery_date of so003 and so015 so that the output would give so003 for some parts and then so015 for the other parts. I do this by changing the delivery_dates of the two sales_orders...

thanks again in advance..