Oracle report conversion

[shannonlp]

Hi,

Using Oracle 9i/Crystal XI.

I’ve been asked to develop a report that currently exists in Oracle and as usual the SQL is ugly and does not translate well. I’m using ADD COMAND and although it returned results, it retuned more data that it should have.

My sql skills are average but I’m not familiar with localrate, getlocalcurrency, and getrate and whether Crystal knows what to do. Also, I’m not clear if Crystal can handle ‘and exists’. I’d be up for building the report in the standard way but I’m not clear on how to replicate localrate, getlocalcurrency, getrate and the ‘and exists’ clause.

If anyone could offer any advice it would be much appreciated.

Shannon

select region.rgname Region,
country.cnname Country,
substr(sub.suname,1,25) Name,
substr(sub.sucity,1,15) City,
sub.suacctno SUBS#,
sub.sucacode category,
sub.sucirculation,
category.caname,
contract.cobucode billing_bureau,
contract.cono contract_number,
contract.costartdt contract_start,
contract.coenddt contract_end,
contract.conoticedt contract_notice,
contract.cocontractdt contract_date,
cs.csbucode service_location,
sub.suname,
cs.csseid Service,
service.sedesc,
delivery_method.dmname,
world_prod.getlocalrate(csseq) LocalRate,
world_prod.getlocalcurrency(csseq) cu,
world_prod.getrate(csseq,'2006') US2006
from world_prod.subscriber sub,
world_prod.country,
world_prod.contract,
world_prod.contract_service cs,
world_prod.region,
world_prod.delivery_method,
world_prod.category,
world_prod.service,
world_prod.service_usage
WHERE region.rgcode = country.cnrgcode
and sub.sucountry = country.cncode
and sub.suacctno = contract.cosuacctno
and contract.cosuacctno = cs.cscoacctno
and contract.cono = cs.cscono
and sub.sucacode = category.cacode (+)
and cs.csdmcode = delivery_method.dmcode (+)
and cs.csseid = service.seid
and cs.cssucode = service_usage.sucode
and exists (
select 1 from world_prod.contract_service
where cscoacctno = sub.suacctno
and csseq = cs.csseq
and cscono = cs.cscono
and exists
(select 1 from world_prod.transaction
where txcsseq = cs.csseq
and txno =
(select max(txno)
from world_prod.transaction
where txcsseq = cs.csseq
and txeffdt <= sysdate)
and txtype <> 'STP'
and txrateloc > 0)
)
order by country.cnrgcode, country.cnname, sub.suname, sub.sucity

 

[elsenorjose]

Shannon, have you considered creating a view or stored procedure out of this SQL and using it as the data source in your report? If you're not sure how to create a view or SP, you can ask a DBA if that's an option.

Best of luck!

And, if it can be done in CR, one of the pros will definitely have an answer.

 

[IanWaterman]

Not totally sure but these look like functions.

world_prod.getlocalrate(csseq) LocalRate,
world_prod.getlocalcurrency(csseq) cu,
world_prod.getrate(csseq,'2006') US2006

YOu will need to go back to Oracle DB and see if these are functions and then try to replicate them in MySql.

Pretty sure Crystal commands can call functions.

Ian

 

[samiam2006]

I'll agree with you about the Oracle SQL. I'm in the process of converting a number of very complex Oracls reports to CR and it's been a painfull and slow process.

I've takent the Oracle SQL and added them to each of the CR reports as commands. After I figure out which ones are used for a main report and which for subreports (every CR report will have at least 2 subs), the dba and I will tweaked the SQL and use views as much as possible.

I'll look over your SQL and respond a little this afternoon.

 

[shannonlp]

Hi,

I was able to run this report using Add Command and get the expected data results by changing the joins (was outer, changed to inner). It looks like Crystal can’t handle Oracle’s syntax for an outer joins so I did a work around.

I have to say, converting reports from Oracle to Crystal is tedious and painful. I'm not thrilled with using Add Command because I like to release reports to end users and let them make their own modifications. At the same time, its the quickest and easiest way.

Thanks to all for the feedback.