quote:

---

Originally posted by yaman

Sir , I have 30000 records in Invoice table and
master table have 30 - 40 records .
i want distinct records .These query take too much time to fetch data pls help me out sir .


SELECT distinct
ISNULL(invoice_header.invoice_id,'') as invoice_id,
ISNULL(invoice_header.invoice_date,'') as invoice_date ,
ISNULL(invoice_header.customer_code,'') as customer_code,
ISNULL(invoice_header.cust_po,'') as cust_po,
ISNULL(invoice_header.dept,'') as dept,
ISNULL(convert(varchar,invoice_header.date_shipped,6),'') as date_shipped,
ISNULL(invoice_header.shipvia_code,'') as shipvia_code,
ISNULL(invoice_header.fob_code,'') as fob_code,
ISNULL(payment_terms.payment_name,'') As payment_name ,
--invoice_header.payment_code,
ISNULL(invoice_header.season_code,'') as season_code,
ISNULL(invoice_header.category_code,'') as category_code,
ISNULL(invoice_header.no_cartons,'') as no_cartons,
ISNULL(invoice_header.shipment_no,'') as shipment_no,
ISNULL(invoice_header.tli_po_no,'') As tli_po_no,
ISNULL(invoice_header.dc_id,'')as dc_id,
ISNULL(invoice_header.freight,'') as freight,
ISNULL(invoice_header.discount,'') as discount,
ISNULL(invoice_header.factory_code,'') as factory_code,
ISNULL(invoice_detail.seq_num,'') as seq_num,
ISNULL(invoice_detail.sku_code,'') as sku_code,
ISNULL(invoice_detail.design_code,'') as design_code,
ISNULL(invoice_detail.color_code,'') as color_code,
ISNULL(invoice_detail.item_code,'') as item_code,
ISNULL(invoice_detail.size_code,'') as size_code,
ISNULL(invoice_detail.quantity,'') as quantity,
ISNULL(invoice_detail.price,'') as price,
ISNULL(invoice_detail.price*invoice_detail.quantity,'') as amt,
ISNULL(invoice_detail.unit_code,'') as unit_code,
ISNULL(customer_master.user_customer_code,'') as user_customer_code,
ISNULL(customer_master.customer_name,'') as customer_name,
ISNULL(customer_master.address,'') as address,
ISNULL(invoice_header.salesman,'') as salesman,
ISNULL(invoice_header.commission,'') as commission,
ISNULL(distribution_center.dc_num,'') as dc_num,
ISNULL(distribution_center.address,'') as address,
ISNULL(customer_master.ship_via,'') as ship_via ,
'''' as column_7,
'''' as column_8,
'''' as column_12,
'''' as column_16
( SELECT Max(Unit_Name) FROM UNIT_MASTER where UNIT_MASTER.unit_code = invoice_detail.unit_code )
as Unit_Desc,
( SELECT Max(Item_Name) FROM item_master where item_master.item_code = invoice_detail.item_code )
as Item_Desc

FROM invoice_header LEFT JOIN invoice_detail
ON invoice_header.invoice_id=invoice_detail.invoice_id
LEFT JOIN distribution_center
ON distribution_center.dc_seq=invoice_header.dc_id
LEFT JOIN customer_master
ON customer_master.customer_code=distribution_center.customer_code
and customer_master.customer_code=invoice_header.customer_code
LEFT JOIN payment_terms
ON payment_terms.payment_code =invoice_header.payment_code

Yaman

---