Options for Converting a Subquery to CR 8.5 1

[antonx]

Hi and Happy New Year to all !

I was given this DB2 SQL subquery to convert to Crystal Reports 8.5. My company won't allow me to create a SP or View and want to know what other options I have. Here is the SQL subquery:

select A.CONTRACT_NUMBER, A.AGENT_ID, B.DIST_GRP_NAME
from NEW_BUS A
inner join ADVISOR B on A.AGENT_ID = B.AGENT_ID
where A.TRANS_TYPE = 'APPLIED'
and B.DIST_GRP_NAME <> 'ICA'
and A.CONTRACT_NUMBER not in
(select C.CONTRACT_NUMBER
from NEW_BUS C
where C.TRANS_TYPE in ('NPW', 'NEW ISSUE));

The CONTRACT_NUMBER can have many different TRANS_TYPE values (i.e. Applied, NPW, New Issue, etc...) in the same table NEW_BUS. I want Crystal Reports to print only the CONTRACT_NUMBERs that have Applied only and that the same CONTRACT_NUMBER does not have a NPW or New Issue.

I was thinking of using a subreport. My main report will contain the first select statement and my subreport will contain the subselect. But the linking may not work because it will give me all CONTRACT_NUMBERs with TRANS_TYPE of Applied, NPW, or NEW Issue. Right?

What are the other options I have and would appreciate if you gave me an example?

Thank you very much!

 

[MrBillSC]

The only approach I know of would be to use variables within a formula. You won't need table C. In your select formula select table A if A.Trans_Type in ['Applied','NPW','New Issue'].

You will need to group by Contract_Number and sort by Trans_Type decending.

Create a formula VarNoPrint:
WhilePrintingRecords;
NumberVar NoPrint;

If A.TransType in ['NPW','New Issue'] then
NoPrint:= NoPrint + 1 else
NoPrint

Place formula VarNoPrint in your detail section (You can suppress it).

Create a section a and b for group footer Contract_Number.
In Group Footer a place your report items for Contract_Number and conditionally suppress group footer a section if NoPrint > 0.

Create a formula VarNoPrintReset:

WhilePrintingRecords;
NumberVar NoPrint:= 0

Place formula VarNoPrintReset in group footer b. You can suppress group footer b.

 

[antonx]

Your suggestion worked !! Thank you very much.

The only problem is the time it takes for the report to go to page 2. There are 700,000 records and running on a Pentium II (RAM 256M). In the Preview tab, when I click the right arrow to go to page 2, the Status Bar displays Formatting Page 2 and it takes a couple of minutes to display page 2. It's not a big deal but is there any options to speed up the formatting?

Thanks

 

[MrBillSC]

Since you can't use the subquery or a view, I know of no other way. If someone else know how to speed this up I'd like to know as well.

Sorry.
MrBill

 

[rana77]

Hi

Create a stored procedure for query
select A.CONTRACT_NUMBER, A.AGENT_ID, B.DIST_GRP_NAME
from NEW_BUS A
inner join ADVISOR B on A.AGENT_ID = B.AGENT_ID
where A.TRANS_TYPE = 'APPLIED'
and B.DIST_GRP_NAME <> 'ICA'
and A.CONTRACT_NUMBER not in
(select C.CONTRACT_NUMBER
from NEW_BUS C
where C.TRANS_TYPE in ('NPW', 'NEW ISSUE));
and design report using stored procedure.
In this case u dont have to worry abt linking and using subreport and it will be faster too.

Lalit Rana