SQL Query formula replacement for Crystal Formula.

[MarsianStar]

Hello,
I am using Oracle and CR XI. This is the command that I am using. The second code is for a formula in the report that I am also using, but because the field is not indexed, it takes about an hour to run. I would like to insert an equivilant formula in the SQl query to make run faster.

SELECT c.CALLID, 
       c.RECVDDATE, 
         c.RECVDTIME, 
         s.CUSTID, 
         a.ITEM_RES_ID, 
         c.CUSTTYPE, 
         s.REPORTED_BY_ORGANIZATION, 
         a.ASGNTEAM, 
         c.SUBITEM, 
         c.CALLDESC, 
         c.CLOSEDESC,
         s.CALLER_NAME
FROM  CALLOG c,
      ASIGNMENTS a,
      CUSTOMERS s

stringvar ClientName;
if {Command.CUSTTYPE} = "TYPE1" then (ClientName:= {Command.REPORTED_BY_ORGANIZATION})
else
(ClientName:= {Command.CUSTID});
ClientName

 

[lbass]

I think the issue is not the formula, but that you have not joined the tables in the from clause of the command.

-LB

 

[MarsianStar]

I have a another Crystal report created from template with joints and still takes the same amount of time. The people from DBA said it was an indexing issue, but too busy to help fixing.

If I remove that client formula and just indicate one type of customers, then it works like a charm. Thinking to make 2 sub reports as there are only 2 types (People and Organizations) calls, but would like a sample code/syntax on how to do in the SQL Query, if handy offcourse.

 

[lbass]

Why AREN'T you joining the tables--that is very odd. I still don't think it's the formula per se--it might just be that the formula is the only thing that is trying to make use of multiple tables that are NOT joined, so everything has to occur locally. Are there common fields you could be linking? What it the relationship between tables? One to one? One to many?

-LB

 

[lbass]

PS. If you don't link, then I think all rows in each table are checked for each row in every other table, returning a huge number of records and slowing the report.

-LB

 

[MarsianStar]

I meant that I am using 2 reports one with joins and one without. I created the command one thinking that it was my joins that was causing the hold up. But they are both the same in the result since there is only one record of subset per callog all it is really is the customer info. Callog is One to two, the subset and the assignments table. Common field is the call ID field.

Here is the older Code with the original report.

SELECT "CALLOG"."CALLID", "CALLOG"."RECVDDATE", "CALLOG"."RECVDTIME", 
"SUBSET"."CUSTID", "ASGNMNT"."ITEM_RES_ID", "CALLOG"."CUSTTYPE", 
"SUBSET"."REPORTED_BY_ORGANIZATION", "ASGNMNT"."ASGNTEAM", 
"CALLOG"."SUBITEM", "SUBSET"."CALLER_NAME", "SUBSET"."ALTERNATECALLER", 
"CALLOG"."CALLDESC", "CALLOG"."CLOSEDESC"

FROM   ("PRODUCTION"."CALLOG" "CALLOG" LEFT OUTER JOIN 
"PRODUCTION"."ASGNMNT" "ASGNMNT" ON "CALLOG"."CALLID"="ASGNMNT"."CALLID") 
INNER JOIN "PRODUCTION"."SUBSET" "SUBSET" ON 
(("CALLOG"."CALLID"="SUBSET"."CALLID") AND 
("CALLOG"."CUSTID"="SUBSET"."CUSTID")) AND 
("CALLOG"."CUSTTYPE"="SUBSET"."CUSTTYPE")