Help : Crystal Reports from Oracle Stored Proc

[shalet76]

Hi,

I am creating crystal reports out of oracle stored procedure. I have different queries in my stored procedure which is using IN statement.
Eg:
SELECT replace(shipper_name,'\',' ') as shipper,
sum(teus / cntSailings) as teus
FROM VW_CONTRIBUTION,
VW_TOTAL_SAILINGS
WHERE VW_CONTRIBUTION.dir = VW_TOTAL_SAILINGS.DIR
AND VW_CONTRIBUTION.Principal = arg_principal
AND VW_CONTRIBUTION.line in ( arg_service )

Here 'arg_service' is an argument passed to the procedure. When I call the report with multiple service lines, the query is not working properly and not returning any rows. I am passing the values as comma separated String(varchar2)
Eg : arg_service = Q1,P1,A1
But when there's only a single value for 'arg_service'( Q1 ), query is working perfectly. Can anybody suggest me a solution for this?

Thanks,
sv.

 

[Jurandi]

Hi Shalet76,

in a past a had the same problem.
Unfortunatelly the unique way to solve this is a dynamic query in a stored procedure case your oracle server version is equal or greater than 8i, because in a clausule IN
is not permitted a parameter.
I don't know why with a one key it work fine.

Sorry by my poor english.

[]s,

Jurandi

 

[Turkbear]

Hi,
Try concatenting the IN parameter with paraens:

Either:
AND VW_CONTRIBUTION.line in '(' ||( arg_service ) ||')'
in your SP's code or
create the passed values with like this
('Q1','P1','A1') and
pass that string as the parameter..

Still might not work, but worth a try or two( you may need to switch to dynamic Sql in the proc)..

 

[housesw]

Dear All
How can I call crystal report from oracle form 6i ?
Thanks,

House