Stored Procedure problem...

[rgb30b]

My procedure includes a datarange parameter. First, I need to delete the table. Then the daterange populates the table, finally the procedure loops through the table and updates it with additional info. Seems to work fine except, when I put in the new daterange in CR, the table info is not deleted and I have to verify database, refresh with the same parameters, then it works fine. I have to use CRs OLE DB, because of cursors in the proc, and CR 8.5. I realize this may not be enough info, but why do I need to verify database and refresh to see the results of the proc? Any help would be appreciated...

 

[bdreed35]

Can you post the proc? Also, can you explain what you mean by date range in CR? Are you using SQL Server paramters or CR parameters? You shouldn't have to be verifying the database every time. I suspect that maybe your parameters you are using maybe CR's, and they are having no affect on your SP.

~Brian

 

[rgb30b]

Thankx for the reply. In essence, Im attempting to select an SQL daterange (shown in CR), delete a table of data, select new data via the daterange and populate the table, then update it, with a cursor and a series of updates. Here goes the proc. Its long and probably poorly written, anyway..

CREATE PROCEDURE LEH_prod_file_upload @st_dt datetime,@en_dt datetime
AS
declare @site_id char(3), @from_shift char(4),@to_shift char(4) ,@s_class char(3),@s_rpt_or_file char(1)

delete [QualityPlastics]..allplastic_info

select @site_id = 'LEH'
select @from_shift = 7-3
select @to_shift - 11-7
select @s_class = 'PRS'
select @s_rpt_or_file = 'R'

SET NUMERIC_ROUNDABORT ON
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET ANSI_WARNINGS OFF
SET NOCOUNT ON



select
product_date,
prod_sku = ltrim(rtrim(C.prod_sku)) + case when right(ltrim(rtrim(M.case_machine)),2) = 'CF' then 'CF' else '' end,
prod_shift,prod_logs_cases = round(( prod_logs_cases * isnull(stat_factor,1)) * 10,0),
printing,C.machine_id
into #temp8
FROM [e-cpt]..FORMING_PROD_TRACK C, [e-cpt]..SKU_MASTER S, [e-cpt]..MACHINE M
WHERE ( C.site_id = @site_id and C.prod_sku = S.prod_sku )
and ( C.machine_id = M.machine_id and M.site_id = @site_id and M.class = @s_class)
and ( C.product_date >= @st_dt and C.product_date <= @en_dt)
and prod_logs_cases <> 0
and left(ltrim(rtrim(printing)),2) = '05'

delete from #temp8 where product_date = @st_dt and prod_shift > @from_shift
delete from #temp8 where product_date = @en_dt and prod_shift < @to_shift

insert allplastic_info
SELECT
prod_sku as &quot;Sku&quot; ,
sum(prod_logs_cases)/10.0 as &quot;Cases&quot;,
substring(convert(char(3),datepart(dd,product_date) + 100 ),2,2) as &quot;Production_date&quot;,
convert(char(4),datepart(yy,max(product_date))) + substring(convert(char(3),datepart(mm,max(product_date)) + 100 ),2,2 ) + substring(convert(char(3),datepart(dd,max(product_date)) + 100 ),2,2) as &quot;Production_date&quot;,
ltrim(rtrim(max(printing))) as &quot;Dept&quot; ,
ltrim(rtrim(max(machine_id))) AS &quot;MachineID&quot;,null,null,null,null,null,null, null,null,null,null,null,null,null,null, @st_dt, @en_dt, null
FROM #temp8
group by prod_sku
order by prod_sku

declare @skucode char(18)

declare @xyear smallint, @xvers tinyint, @planyear smallint, @location char(4)

select @planyear = (Select MAX(left(production_date , 4)) from allplastic_info)
select @location = '0358'

create table #temp1 (
skucode char(18) null,
desitem char(18) null,
stockitem char(18) null,
substrate char(10) null,
packcount int null,
singleunitwgt float null,
stkmastnum int null,
skuname char(50) null,
grosslbscase float null,
proctype char(7) null)


create table #temp2 (
stkmastnum int null,
machmastnum int null,
rate float null,
varprice float null,
varcost money null,
machitem char(18) null,
runratecode char(10) null,
grosslbscase float null,
proctype char(7) null,
mch_waste float null)

create table #temp3 (
stkmastnum int null,
resincost money null)

create table #temp4 (
skucode char(18) null,
stockitem char(18) null,
skuname char(50) null,
packcount int null,
substrate char(10) null,
singleunitwgt float null,
resincost float null,
laborcost float null)

create table #temp5(
skucode char(18) null)


create table #temp6(
avg_count int null,
skucode char(18)null)


select @xyear = year, @xvers = vers
from [dixiecst]..planfreq
where location = @location
and year = @planyear
and currflag = 'X'


declare @sku as char(18)
DECLARE Sku_Cursor CURSOR FOR
SELECT Sku FROM AllPlastic_info
OPEN Sku_Cursor
FETCH NEXT FROM Sku_Cursor into @sku
WHILE @@FETCH_STATUS = 0

BEGIN

delete #temp1
delete #temp2
delete #temp3
delete #temp4
delete #temp5
delete #temp6
------Begin

insert #temp1
select a.item, b.item, c.des_stockitem, a.sku_substratecode, b.packagecount, 0, c.des_stockmastnum,a.name,0,''
from [dixiecst]..master a, [dixiecst]..skurecipes b, [dixiecst]..master c
where a.location = @location
and a.year = @xyear
and a.vers = @xvers
and a.class = 'SKU'
and a.item = @sku
and b.location = a.location
and b.skumastnum = a.mastnum
and b.class = 'DES'
and c.location = a.location
and c.mastnum = b.usedmastnum

update #temp1
set singleunitwgt = a.stk_singleunitwgt,
grosslbscase = a.stk_grosslbscase,
proctype = a.stk_proctype

from [dixiecst]..master a
where a.location = @location
and a.mastnum = stkmastnum


insert #temp2
select a.stkmastnum, b.machmastnum, c.rate, 0, 0,b.machitem,b.runratecode,a.grosslbscase,a.proctype,0
from #temp1 a, [dixiecst]..stkmch b, [dixiecst]..runrates c
where b.location = @location
and b.stkmastnum = a.stkmastnum
and c.location = @location
and c.year = @xyear
and c.vers = @xvers
and c.runratecode = b.runratecode

update #temp2
set varprice = varprice + c.wavgvar
from [dixiecst]..machrecipes a, #temp2 b, [dixiecst]..master c
where a.location = @location
and a.machmastnum = b.machmastnum
and a.class = 'LBR'
and a.item like 'VLAB%'
and c.location = a.location
and c.mastnum = a.matmastnum

---print 'vlab'
----select * from #temp2

update #temp2
set varprice = varprice + c.wavgvar
from [dixiecst]..machrecipes a, #temp2 b, [dixiecst]..master c
where a.location = @location
and a.machmastnum = b.machmastnum
and a.class = 'LBR'
and a.item like 'BENE%'
and c.location = a.location
and c.mastnum = a.matmastnum

---print 'bene'

update #temp2
set varprice = varprice + c.wavgvar
from [dixiecst]..machrecipes a, #temp2 b, [dixiecst]..master c
where a.location = @location
and a.machmastnum = b.machmastnum
and a.class = 'LBR'
and a.item like 'FASS%'
and c.location = a.location
and c.mastnum = a.matmastnum
-----print 'fass'

update #temp2
set varprice = varprice + c.wavgvar
from [dixiecst]..machrecipes a, #temp2 b, [dixiecst]..master c
where a.location = @location
and a.machmastnum = b.machmastnum
and a.class = 'LBR'
and a.item like 'OVRT%'
and c.location = a.location
and c.mastnum = a.matmastnum

update #temp2
set mch_waste = a.mch_waste/100
from [dixiecst]..master a
where machmastnum = a.mastnum and
a.class = 'MCH'
and a.year = @xyear
and a.vers = @xvers


update #temp2 set grosslbscase = 1 where runratecode not like 'EXT%'

update #temp2 set varcost = (grosslbscase / rate) * (1+mch_waste) * varprice

insert #temp3
select a.stkmastnum, sum((b.perc / 100) * (c.wavgvar + c.wavgfix))
from #temp1 a, [dixiecst]..stkmat b, [dixiecst]..master c
where b.location = @location
and b.stkmastnum = a.stkmastnum
and b.class = 'RES'
and c.location = @location
and c.mastnum = b.matmastnum
group by a.stkmastnum
order by a.stkmastnum

insert #temp4
select a.skucode,a.stockitem, a.skuname, a.packcount, a.substrate, a.singleunitwgt, c.resincost, sum(b.varcost) laborcost
from #temp1 a, #temp2 b, #temp3 c
where b.stkmastnum = a.stkmastnum
and c.stkmastnum = a.stkmastnum
group by a.skucode, a.stockitem, a.packcount, a.substrate, a.singleunitwgt, c.resincost,a.skuname
order by a.skucode,a.stockitem, a.packcount, a.substrate, a.singleunitwgt, c.resincost,a.skuname


update allplastic_info
set stockitem = a.stockitem,
skuname = a.skuname,
packcount = a.packcount,
substrate = a.substrate,
singleunitwgt = (a.singleunitwgt * 1000 / 453.5924),
resincost = a.resincost ,
laborcost = a.laborcost
from #temp4 a
where sku = a.skucode

update allplastic_info
set avg_count = (Select sum(a.qa_count)/ count(qa_count))
---- avg_weight = (Select avg(a.weight))
from PlasticsEntry a, AllPlastic_info b, #temp4 c
where a.sku = b.sku and b.sku = c.skucode
and a.production_date >= b.start_date
and a.production_date <= b.end_date
-----and a.weight_check = '1'


update allplastic_info
set avg_weight = (Select sum(a.weight) / count(a.weight)) * 1000 / 453.5924, weight_count = (Select sum(weight_check))
from PlasticsEntry a, AllPlastic_info b, #temp4 c
where a.sku = b.sku and b.sku = c.skucode
and a.production_date >= b.start_date
and a.production_date <= b.end_date
and a.weight_check = '1'
-------group by sku

FETCH NEXT FROM Sku_Cursor into @sku
END
close sku_cursor
deallocate sku_cursor

update allplastic_info
set var_count = (b.avg_count - b.packcount)
from allplastic_info b

-----SET ARITHIGNORE OFF
update allplastic_info
set over_count = (b.var_count * b.cases) / (b.packcount)
from allplastic_info b

update allplastic_info
set resin_over = (b.over_count * b.avg_weight)
from allplastic_info b

update allplastic_info
set resin_total = (b.resin_over * b.resincost)
from allplastic_info b

update allplastic_info
set labor_total = (b.over_count * b.laborcost)
from allplastic_info b
GO

Select * from AllPlastic_Info

 

[vidru]

Looks to me like the problem is the GO right before your final select statement.

Get rid of it and try again.

-dave

 

[rgb30b]

Interesting observation, vidru. I didnt notice the GO. In fact, I didnt even put it in the proc. I deleted it, ran the report again, and it appears again! I can't get rid of it. At any rate, the sequence of events in the report has not changed.
1. Run Report - Select Daterange -nothing
2. Refresh Report again, use same range -nothing
3. Verify Database - popup - Database up to date
4. Refresh Report again - use same range - Results appear

I assumed that since I am using a Select statement as the final procedure, that I would see the fields of the proc. Is this an incorrect assumption, because Im not? The stored proc shows no underlying fields in Crystal. I had to add the AllPlastic_Info table to the report, just to see the fields and add them to the report. The proc with a daterange works flawless from Query Analyzer or ISQL. Thankx again for your help. Do you have any other suggestions?

 

[synapsevampire]

Open the report, do a Verify Database, and then save the report.

It should work from that point on.

-k

 

[synapsevampire]

If that fails, change the final select to an explicit field select rather than select *

I chastise my SQL developers for using select * because if the underlying structures add a field, the SP will fail. explicitly naming fields avoids this.

-k

 

[rgb30b]

Thankx for the assistance, synapsevampire. Just saving the report after verifing database, does not work, Ive tried that many times before. Although the underlying structures do not add a field, I have explicitly named the select statement, it makes no difference. The SP works, it just does not work the way I think it should in Crystal. Any more ideas will be appreciated.

 

[synapsevampire]

Since you're apparently using SQL Server, do a Trace on the database side to log what Crystal is passing, and perhaps get a better understanding of the error.

I used ODBC with the following test SP which has a cursor and it worked fine:

CREATE PROCEDURE MyTempProc @st_dt datetime,@en_dt datetime
AS
SET NOCOUNT ON
create table #temp1
(
ID int null,
somevar char(100) null,
someint int null,
somedate datetime null
)
insert #temp1
select top 1 * from testtable
declare @sku as char(18)
DECLARE Sku_Cursor CURSOR FOR
SELECT somevar FROM testtable
OPEN Sku_Cursor
FETCH NEXT FROM Sku_Cursor into @sku
WHILE @@FETCH_STATUS = 0
BEGIN
insert #temp1
select 2,@sku,200,getdate()
FETCH NEXT FROM Sku_Cursor into @sku
end
close sku_cursor
deallocate sku_cursor
select ID,somevar, someint, somedate
from #temp1
drop table #temp1

One obvious difference is the use of a drop table in the end.

In reviewing your SP I'm not certain that a cursor is even required, and as you probably know, cursors are inefficient.

Anyway, hopefully this will allow you to correct your error, and remember, I used ODBC.

-k

 

[FVTrainer]

Just a short comment that may or may not provide some assistance: I've used stored proc's similar to SV's. The pattern is the same as SV showed, namely:

1) The first thing the sp does is create a temp table from which you will eventually return records.

2) The remaining business logic of the sp (inserts, other temp tables, cursors, etc.)

3) Select field list from temp table.

So, the difference between this approach and rgb30b's proc is that rgb30b's is using an existing table and the first create table statement isn't related to the table used to return the resultset to crsytal (whereas SV's proc's first create table is related to the resultset). I'm wondering if maybe that's an issue. I haven't tested it out, but since SV's pattern is one I've used successfully as well, maybe it's worth a look.

 

[synapsevampire]

FV: I don't think so.

I altered it to:

CREATE PROCEDURE MyTempProc @st_dt datetime,@en_dt datetime
AS
SET NOCOUNT ON
create table #temp2
(
ID int null,
somevar char(100) null,
someint int null,
somedate datetime null
)
create table #temp1
(
ID int null,
somevar char(100) null,
someint int null,
somedate datetime null
)
insert #temp2
select * from testtable
insert #temp1
select * from #temp2
declare @sku as char(18)
DECLARE Sku_Cursor CURSOR FOR
SELECT somevar FROM testtable
OPEN Sku_Cursor
FETCH NEXT FROM Sku_Cursor into @sku
WHILE @@FETCH_STATUS = 0
BEGIN
insert #temp1
select 2,@sku,200,getdate()
FETCH NEXT FROM Sku_Cursor into @sku
end
close sku_cursor
deallocate sku_cursor
select ID,somevar, someint, somedate from #temp2
drop table #temp1
drop table #temp2

Did a verify database and it worked fine.

I create the temp2 table first, and select from it into temp1, and select from temp1 at the end.

-k

 

[FVTrainer]

SV,

Ok. Thanks for testing it out. At least that rules out the instruction order as a problem.

rgb30b:

One question: Is the following line a typo in your post or is it also like this in the sp:

select @to_shift - 11-7

That would seem to be an error; it returns NULL instead of assigning the value '11-7'

Also, this line:

select @from_shift = 7-3

returns &quot;4&quot; rather than a string &quot;7-3&quot;

 

[rgb30b]

Gentlemen, I really appreciate your persistence in helping resolve my problem. It is hard for me to believe that the – instead of an = sign in select @to_shift - 11-7 was the root cause of the whole thing, but Ill take it. Obviously, an oversight on my part. I have no way of knowing the shift that the records were produced in, I only have 3 records per month for 2003 (bogus data) to create this report. Since I’m not returning the shift value, maybe this is why, not sure yet, but Ill find out. Thankx again, synapsevampire and FVTrainer. I wish I could buy you guys a cold one!

 

[synapsevampire]

I prefer mine luke warm and hoppy

I'm suspect of the entire SP, bare in mind that cursors deliver poor performance.

Rewrite it to use temp tables and selects.

-k