August 25, 2005 at 9:57 pm
Not sure if it went through. Please ignore if posted again..
------------
Hi,
I have a Dataset filled with data from a stored procedure.I am using string builder and iterating through each row in data set to get the output. I am getting few duplicate records in the output data.Actually the Original tables from which this data is retrived does not have duplicate records. The code is correct in getting the output. Only a few columns are selected from the original tables. I want to retrive the data this way. But I want to delete these duplicate records. Is there any way to do this in dataset directly?. Can we call a stored procedure from any other stored procedure? I want to count the rows with Invoice number (which is unique) and delete the extra rows if the count is more than once. But how to use the Stored procedure and the query to check the count and then delete the rows. Please give some code example.
Thanks for your time and suggestions!
Alice
August 26, 2005 at 9:43 am
Alice - your question makes me worry :-).
If you are not getting the right information in the dataset in the first place, then you really need to adjust that so that you do. I would not remove rows afterward, because --
a. It's slow (why collect rows only to discard them?)
and
b. The whole idea of a dataset is that the changes you make can be posted back to the server. If you delete rows, then you are implying that you want to delete them at the server, which doesn't sound like what you really want.
If the data is read-only and you will not be posting changes back to the database, then I would not use a dataset in the first place, but a datareader instead.
August 26, 2005 at 12:21 pm
Thanks For your suggestion.Yes you are right I would use data as read only to generate the Excel report using string builder looping for each row.Yes I want to use data reader.But how to comapre between two rows using a data reader.I tried setting values in each row to string variables and then comparing.But it some how did not work.It would be great if you could give me few lines of code imaginning that your stored procedure will bring duplicate records.Actual table has 28 rows and the columns that we want for this report makes us feel that it is a duplicate reacord(that is true for this report),but there are other columns which make it unique in the actual Table.We are selecting Product_code,Location(USA,Asia),Invoice_Number, and few other columns.So I want a way to delete these extra records if the invoice_Number is same in this particular report.Hope Now I am clear.I have a stored Procedure which brings the data for this.So if you can suggest anything else that would be great.All that I need is an excel report generated using that stored procedure and having unique values.
Thanks for your Time,
Alice.
August 30, 2005 at 4:28 pm
Alice -
I thing the best method is to change (or add?) the SQL query to use SELECT DISTINCT, which will filter out duplicates.
HTH,
Merrill
August 31, 2005 at 12:38 pm
August 31, 2005 at 12:41 pm
My Stored Procedure is given below
----------------------------------------------------------------
CREATE PROCEDURE financeReport
(
@StartDate smalldatetime=null,
@EndDate smalldatetime=null
)
AS
BEGIN
SET NOCOUNT ON
---------------------------------------------------
-- Declare and Set local variables
---------------------------------------------------
DECLARE @ComCode varchar(5)
DECLARE @CustID varchar(15)
DECLARE @CustName varchar(255)
DECLARE @ShipCity varchar(150)
DECLARE @Company varchar(150)
DECLARE @Company1 varchar(150)
DECLARE @Company2 varchar(150)
DECLARE @CommissionRate float
DECLARE @PercOfCommission varchar(10)
DECLARE @PercOfCommission1 float
DECLARE @PercOfCommission2 float
DECLARE @LineTotal float
DECLARE @Commission varchar(255)
DECLARE @IVNO nvarchar(6)
DECLARE @IVSTATE nvarchar(10)
DECLARE @IVDATE smalldatetime
DECLARE @IDLINE smallint
DECLARE @IVCO nvarchar(15)
DECLARE @ICCUSREF varchar(15)
DECLARE @IDITEM varchar(15)
DECLARE @IDQTYSHP float
DECLARE @IDUNTPRC float
DECLARE @IDACCTNO varchar(20)
DECLARE @TotalCommission float
DECLARE @point int
DECLARE @IVTYPE varchar(5)
DECLARE @IVSLCAT2 varchar(3)
DECLARE @IVSLCATName varchar(255)
DECLARE @PIF_DATE smalldatetime
CREATE TABLE #MyTempTable (
[ComCode] [varchar] (5) NULL ,
[CustID] [varchar] (15) NULL ,
[CustName] [varchar] (255) NULL ,
[ShipCity] [varchar] (150) NULL ,
[Company] [varchar] (150) NULL ,
[PercOfCommission] [float] NULL ,
[Commission] [float] NULL ,
[LineTotal] [float] NULL ,
[VendorCode] [varchar] (10) NULL,
[IVNO] [nvarchar] (6) NULL,
[IVSTATE] [nvarchar] (10) NULL ,
[IVDATE] [smalldatetime] NULL ,
[IDLINE] [smallint] NULL ,
[IVCO] [nvarchar] (15) NULL ,
[ICCUSREF] [varchar] (15) NULL ,
[IDITEM] [varchar] (15) NULL ,
[IDQTYSHP] [float] NULL ,
[IDUNTPRC] [float] NULL ,
[IDACCTNO] [varchar] (20) NULL,
[TotalCommission] [float] NULL,
[IVSLCATName] [varchar] (255) NULL,
[PIF_DATE] [smalldatetime] NULL
)
DECLARE Records CURSOR FOR
SELECT CUST_ID,COM_CODE,TOTALSALES,SHIP_CITY
,AR_IVC_NO,SHIP_STATE,IVC_DATE,LN_NO,CO_NUMBER,CUST_PO_NO,ITEM,SHIP_QTY,
UNIT_PRICE,SAL_ACCTNO,IVC_TYPE,SALES_CAT2,PIF_DATE
FROM CUSTOMER_POSTPRINT where (IVC_DATE >= @StartDate or @StartDate IS NULL) and (IVC_DATE <= @EndDate or @EndDate IS NULL)
and COM_CODE !='N/A' and SAL_ACCTNO LIKE '%31__'
--COM_CODE ='AR3' and CUST_ID='1862'--(IVC_DATE >= @StartDate or @StartDate IS NULL) and (IVC_DATE <= @EndDate or @EndDate IS NULL)
OPEN Records
FETCH NEXT FROM Records INTO @CustID,@ComCode,@LineTotal,@ShipCity
,@IVNO,@IVSTATE,@IVDATE,@IDLINE,@IVCO,
@ICCUSREF,@IDITEM,@IDQTYSHP,@IDUNTPRC,@IDACCTNO,@IVTYPE,@IVSLCAT2,@PIF_DATE
WHILE @@FETCH_STATUS = 0
BEGIN
Select @CustName = rtrim(CustomerName) from FS_Customer where CustomerID = rtrim(@CustID)
Set @IVSLCATName = null
if(@IVSLCAT2 is not NULL)
begin
Select @IVSLCATName = rtrim(CustName) from OEMCustomerCodes where Code = rtrim(@IVSLCAT2)
end
select @Company = rtrim(COMM_DESC),@CommissionRate=rtrim(COMM_RATE),@PercOfCommission=rtrim(SALES_ID) from COMMISSION_CODE where COMM_CODE = @ComCode and COMM_CODE !='N/A'
Set @point =CHARINDEX('/', @Company)
if(@point > 0)
Begin
Set @Company1 =UPPER(LTRIM(convert(char(20),SUBSTRING(@Company,1,@point - 1))))
Set @Company2 =UPPER(Ltrim(Rtrim(convert(char(20),SUBSTRING(@Company,@point + 1,20 )))))
Set @point =CHARINDEX('/', @PercOfCommission)
if(@point > 0)
begin
Set @PercOfCommission1 =LTRIM(convert(float,SUBSTRING(@PercOfCommission,1,@point - 1)))
Set @PercOfCommission2 =LTRIM(convert(float,SUBSTRING(@PercOfCommission,@point + 1,5)))
end
end
if(@IVTYPE = 'R')
Begin
select @LineTotal=(0-CashReceiptLocalAmount) from FS_ARCreditMemo where CashReceiptReferenceNumber = @IVNO
End
SET @CommissionRate = @CommissionRate * 0.01
if(@Company1 = @Company2)
begin
Set @PercOfCommission1 = @PercOfCommission1 + @PercOfCommission2
if(@PercOfCommission1 > 0)
Begin
Set @PercOfCommission1 = @PercOfCommission1 * 0.1
end
SET @TotalCommission = @PercOfCommission1 * @CommissionRate * @LineTotal
Insert into #MyTempTable(ComCode,CustID,CustName,ShipCity,Company,PercOfCommission,Commission,LineTotal,VendorCode,IVNO,IVSTATE,IVDATE,IDLINE,IVCO,ICCUSREF,IDITEM,IDQTYSHP,IDUNTPRC,IDACCTNO,TotalCommission,IVSLCATName,PIF_DATE)
values(@ComCode,@CustID,@CustName,@ShipCity,@Company1,@PercOfCommission1,@CommissionRate,@LineTotal,NULL,@IVNO,@IVSTATE,@IVDATE,@IDLINE,@IVCO,@ICCUSREF,@IDITEM,@IDQTYSHP,@IDUNTPRC,@IDACCTNO,@TotalCommission,@IVSLCATName,@PIF_DATE)
end
else
begin
if(@PercOfCommission1 > 0)
Begin
Set @PercOfCommission1 = @PercOfCommission1 * 0.1
end
if(@PercOfCommission2 > 0)
Begin
Set @PercOfCommission2 = @PercOfCommission2 * 0.1
end
SET @TotalCommission = @PercOfCommission1 * @CommissionRate * @LineTotal
Insert into #MyTempTable(ComCode,CustID,CustName,ShipCity,Company,PercOfCommission,Commission,LineTotal,VendorCode,IVNO,IVSTATE,IVDATE,IDLINE,IVCO,ICCUSREF,IDITEM,IDQTYSHP,IDUNTPRC,IDACCTNO,TotalCommission,IVSLCATName,PIF_DATE)
values(@ComCode,@CustID,@CustName,@ShipCity,@Company1,@PercOfCommission1,@CommissionRate,@LineTotal,NULL,@IVNO,@IVSTATE,@IVDATE,@IDLINE,@IVCO,@ICCUSREF,@IDITEM,@IDQTYSHP,@IDUNTPRC,@IDACCTNO,@TotalCommission,@IVSLCATName,@PIF_DATE)
SET @TotalCommission = @PercOfCommission2 * @CommissionRate * @LineTotal
Insert into #MyTempTable(ComCode,CustID,CustName,ShipCity,Company,PercOfCommission,Commission,LineTotal,VendorCode,IVNO,IVSTATE,IVDATE,IDLINE,IVCO,ICCUSREF,IDITEM,IDQTYSHP,IDUNTPRC,IDACCTNO,TotalCommission,IVSLCATName,PIF_DATE)
values(@ComCode,@CustID,@CustName,@ShipCity,@Company2,@PercOfCommission2,@CommissionRate,@LineTotal,NULL,@IVNO,@IVSTATE,@IVDATE,@IDLINE,@IVCO,@ICCUSREF,@IDITEM,@IDQTYSHP,@IDUNTPRC,@IDACCTNO,@TotalCommission,@IVSLCATName,@PIF_DATE)
end
FETCH NEXT FROM Records INTO @CustID,@ComCode,@LineTotal,@ShipCity
,@IVNO,@IVSTATE,@IVDATE,@IDLINE,@IVCO,@ICCUSREF,@IDITEM,@IDQTYSHP,@IDUNTPRC,@IDACCTNO,@IVTYPE,@IVSLCAT2,@PIF_DATE
END
CLOSE Records
DEALLOCATE Records
select rtrim(ComCode) as ComCode,rtrim(CustID) as CustID,rtrim(CustName) as CustName,rtrim(ShipCity) as ShipCity,rtrim(Company) as Company,PercOfCommission,Commission,LineTotal,VendorCode,rtrim(IVNO) as IVNO,rtrim(IVSTATE) as IVSTATE,IVDATE,IDLINE,rtrim(IVCO) as IVCO,rtrim(ICCUSREF) as ICCUSREF,rtrim(IDITEM) as IDITEM,IDQTYSHP,IDUNTPRC,rtrim(IDACCTNO) as IDACCTNO,TotalCommission, rtrim(IVSLCATName) as IVSLCAT2, rtrim(PIF_DATE) as PIF_DATE from #MyTempTable
order by company
END
GO
August 31, 2005 at 2:19 pm
I got the solution.But I want small modification.I want to create temporary table.When I am trying to exec the Stored Procedure for the 1st time it gave me the correct result.Next time it complains the table name already exists.So I want to modify the code to create temporary table everytime this Stored Procedure is called.How to do that?
CREATE proc CommissionReport as
CREATE TABLE Commission_Table1 (
[ComCode] [varchar] (5) NULL ,
[CustID] [varchar] (15) NULL ,
[CustName] [varchar] (255) NULL ,
[ShipCity] [varchar] (150) NULL ,
[Company] [varchar] (150) NULL ,
[PercOfCommission] [float] NULL ,
[Commission] [float] NULL ,
[LineTotal] [float] NULL ,
[VendorCode] [varchar] (10) NULL,
[IVNO] [nvarchar] (6) NULL,
[IVSTATE] [nvarchar] (10) NULL ,
[IVDATE] [smalldatetime] NULL ,
[IDLINE] [smallint] NULL ,
[IVCO] [nvarchar] (15) NULL ,
[ICCUSREF] [varchar] (15) NULL ,
[IDITEM] [varchar] (15) NULL ,
[IDQTYSHP] [float] NULL ,
[IDUNTPRC] [float] NULL ,
[IDACCTNO] [varchar] (20) NULL,
[TotalCommission] [float] NULL,
[IVSLCATName] [varchar] (255) NULL,
[PIF_DATE] [smalldatetime] NULL
)
insert into Commission_Table1
exec financeReport '@StartDate','@EndDate'
select distinct * into Commission_Table2 from Commission_Table1
select * from Commission_Table2
GO
Thanks,
Alice
September 1, 2005 at 10:52 am
It looks like your procedure creates a regular (not a temp) table and never removes it, so it remains afterward. Temp tables start with the Pound sign (#); put a Drop command at the end of the procedure to remove your temp table.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply