Hi
By default, the UNION operator removes duplicate rows from the result set. If you use UNION ALL, all rows are included in the results and duplicates are not removed.
In your case, the first result without "Dsp_cart_num" field in the list would be producing duplicate rows and hence one row is being removed.
Regards
Kamlesh
-----Original Message-----
From: K.[Email Address Removed] [mailto:K.[Email Address Removed] Friday, January 30, 2004 12:08 PM
To: LazyDBA.com Discussion
Subject: Stored Proc
Hi All
I have Stored proc called Invoice_details in this
1. When I remove the "Dsp_cart_num" field from the selection list than it
shows only Two records
Dsp_Cart_num Inh_invoice_num Inh_pslip_num Inh_inv_date
dsp_date Inh_depot Inh_grs_wt Inh
8Q545336 8N0097 8N0243 2002-10-14 16:31:46.000
2002-10-14 16:31:46.000 N 52.900
8Q545408 8N0097 8N0243 2002-10-14 16:31:46.000
2002-10-14 16:31:46.000 N 52.900
2. When I put the "Dsp_cart_num" field in the selection list than it shows
Three records which is correct also.
Dsp_Cart_num Inh_invoice_num Inh_pslip_num Inh_inv_date
dsp_date Inh_depot Inh_grs_wt Inh
8Q545336 8N0097 8N0243 2002-10-14 16:31:46.000
2002-10-14 16:31:46.000 N 52.900
8Q545408 8N0097 8N0243 2002-10-14 16:31:46.000
2002-10-14 16:31:46.000 N 52.900
8Q545337 8N0097 8N0243 2002-10-14 16:31:46.000
2002-10-14 16:31:46.000 N 52.900
I would like to know ,Why it is happening because I am not changing any
"Where clause" condition.
Column Name
Dsp_Cart_num
Dsp_Srl_num is the composite primary key.
alter proc Invoice_details 'n','14/10/2002','14/10/2002'
@dp char(1),
@fdate varchar(12),
@tdate varchar(12)
as
Select Dsp_Cart_num,Inh_invoice_num,Inh_pslip_num
,Inh_inv_date,dsp_date,Inh_depot, Inh_grs_wt, Inh_net_wt,
Inh_total_pkgs, dsp_qty, Dsp_Pbx_Qty, Dsp_Price,((Dsp_Qty +
isnull(Dsp_Pbx_Qty,0)) * Dsp_Price) as Value,
dsp_article, dsp_ticket, dsp_wbc, dsp_shade, Cns_Name,
At_cotton_synthetic as Ind_cs,
Cns_Address1, Cns_Address2, Cns_Address3, Cns_State, Cns_Country,
Cns_Range,
Cns_Division, Cns_Regn,Gp_tax_prcntg as Inv_tax_prcntg
From Invoice_header,despatch, Gp_header, Consignee, Artkt
/*Yarn_thread*/
Where Cns_Code = Inh_cus_code
And Cns_depot_code = Inh_depot
--And Yt_Code = At_ytcode
And ltrim(rtrim(At_ticket)) = ltrim(rtrim(dsp_ticket))
And ltrim(rtrim(At_article)) = ltrim(rtrim(dsp_article))
And Gp_doc_num = Inh_pslip_num
and ltrim(rtrim(dsp_aslip_no)) = ltrim(rtrim(Inh_pslip_num))
And convert(datetime,Inh_inv_date,103) between
convert(datetime,@fdate,103) and
dateadd(day,1,convert(datetime,@tdate,103))
and ltrim(rtrim(Inh_depot)) like ltrim(rtrim(@dp)) +'%'
and month(dsp_date) = month(Inh_inv_date)
and day(dsp_date) = day(Inh_inv_date)
and year(dsp_date) = year(Inh_inv_date)
union
Select Dsp_Cart_num,Inh_invoice_num,Inh_pslip_num
,Inh_inv_date,dsp_date,Inh_depot, Inh_grs_wt, Inh_net_wt,
Inh_total_pkgs, dsp_qty, Dsp_Pbx_Qty, Dsp_Price,((Dsp_Qty +
isnull(Dsp_Pbx_Qty,0)) * Dsp_Price) as Value,
dsp_article, dsp_ticket, dsp_wbc, dsp_shade, Cns_Name,
At_cotton_synthetic as Ind_cs,
Cns_Address1, Cns_Address2, Cns_Address3, Cns_State, Cns_Country,
Cns_Range,
Cns_Division, Cns_Regn,Gp_tax_prcntg as Inv_tax_prcntg
From Invoice_header_archive, despatch_archive, Gp_header,
Consignee, Artkt /*Yarn_thread*/
Where Cns_Code = Inh_cus_code
And Cns_depot_code = Inh_depot
--And Yt_Code = At_ytcode
And ltrim(rtrim(At_ticket)) = ltrim(rtrim(dsp_ticket))
And ltrim(rtrim(At_article)) = ltrim(rtrim(dsp_article))
And Gp_doc_num = Inh_pslip_num
and ltrim(rtrim(dsp_aslip_no)) = ltrim(rtrim(Inh_pslip_num))
And convert(datetime,Inh_inv_date,103) between
convert(datetime,@fdate,103) and
dateadd(day,1,convert(datetime,@tdate,103))
and ltrim(rtrim(Inh_depot)) like ltrim(rtrim(@dp)) +'%'
and month(dsp_date) = month(Inh_inv_date)
and day(dsp_date) = day(Inh_inv_date)
and year(dsp_date) = year(Inh_inv_date)
order by Inh_invoice_num
*********************************************
This communication which may be confidential and privileged and the
views expressed herein may be personal and are not necessarily the
views of Coats Ltd. It is for the exclusive use of the intended
recipients(s). If you are not the intended recipient(s), please note that
any distribution, copying or use of this communication or the information
in it is strictly prohibited. If you have received this communication in
error, please notify us by email (Apps.[Email Address Removed] or telephone
our technical support helpdesk at Coats Ltd. +44 (0)20 8210 5100
(UK 0830h - 1800h, Mon-Fri, GMT) and then delete the e-mail and
any copies of it.
**********************************************
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Get today's cartoon: http://www.LazyDBA.com
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page