Getting Expression instead of field list

[cmetz]

I did a stored procedure, when I try to create a report and I add the procedure, I get Expression in the drop down and not the list of fields.

Thanks

 

[rosemaryl]

Dear CMETZ,

Whaqt database and what version of Crystal.

The last statement in any stored procedure must be a select. What are you doing in your sp?

Have you read this kb article?

http://support.businessobjects.com/communityCS/TechnicalPapers/storproc.pdf.asp

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[cmetz]

I am using Crystal Reports 9.

Are you familiar with what I am talking about?

Getting Expression instead of the field list?

 

[rosemaryl]

Dear Cmetz,

I am very familiar with using stored procedures in reports but have never seen the behavior you are stating ... I am not quite sure what you mean by getting "Expression" instead of field list.

Perhaps, you could elaborate.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[cmetz]

When I go to the drop down of the stored procedure where it usually list the fields to add to my report, mine only says Expression

 

[rosemaryl]

Dear Cmetz,

Well, to me that indicates that something is wrong with the stored procedure .. if it is not overly long, why don't you post it here.

Did you adhere to the rules for stored procedures? And, what database are you using?

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[vidru]

Without seeing your procedure, I'd guess that you're either using variables or aggregates in your final SELECT statement, but aren't aliasing the columns. If my guess is close, your statement should be written like this:

SELECT MyVar = @MyVariable, MySum = SUM(Table.Field)
FROM Table

-dave

 

[cmetz]

ALTER procedure DBA.rep_contmissingOB2(in @Enter_date date) /* parameter, ... */
/* RESULT ( column_name, ... ) */
begin
declare p1 integer;
declare p2 integer;
declare p3 integer;
declare p4 integer;
declare p5 varchar(150);
//Find all outbound containers with missing contents
create table #cont(
ordno varchar(30) null,
hdate date null,
origin varchar(30) null,
orgdesc varchar(50) null default '',
dest varchar(30) null,
destdesc varchar(50) null default '',
htype varchar(10) null,
status varchar(5) null,
pkgid varchar(50) null,
serial varchar(50) null,
);
insert into #cont(ordno,hdate,origin,dest,htype,status,pkgid,serial)
select header.ordernumber,cast("date" as date),origin,destination,header.type,status,detail.packageid,serial from
header join detail on header.ordernumber = detail.ordernumber left outer join
shipmenttypes on header.type = shipmenttypes.typecode left outer join
content on header.ordernumber = content.ordernumber left outer join
shipmentnotes on content.serial = shipmentnotes.packageid where
cast("date" as date) = @Enter_date and shipmenttypes.hascontent = '1' and status = 'D' and shipmentnotes.note = 'Package is Missing from Shipment';
create table #tmp2(
usr varchar(10) null,
tstmp timestamp null,
note2 varchar(150) null,
);
for trloop as transcurs dynamic scroll cursor for
select serial from #cont do
insert into #tmp2(usr,tstmp,note2)
select top 1 userid,"timestamp",note from
audittrail where eventnumber = '28' and note like('%'+serial+'%') order by
"timestamp" desc end for;
commit work;
create table #tmp3(
pkgid2 varchar(30) null,
lstusr varchar(15) null,
lststmp timestamp null,
);
for tr2loop as trans2curs dynamic scroll cursor for
select * from #tmp2 do
set p1=patindex('%-%',note2);
set p3=(p1+1);
set p2=locate(note2,'-',p3);
set p4=(p2-p3);
set p5=note2;
//update #tmp2
//set serno = substr(p5,p3,p4) ;
insert into #tmp3(pkgid2,lstusr,lststmp) values(
substr(p5,p3,p4),usr,tstmp) end for;
commit work;
select * from
#cont left outer join #tmp3 on #cont.serial = #tmp3.pkgid2
end