Crystal Evaluate Syntax

[SundancerKid]

Hi All,

I need to evaluate the following and I think my syntax is wrong.

Please help!!

IF ({Q.SAMPLENO} = {Q.MASTERSAMPLENO} OR 
   ISNULL({Q.MASTERSAMPLENO})) OR 
   {Q.ISTIMEWEIGHTED} <> -1) AND 
   {Q.APPROVED} = -1  AND 

***** This replaced the Oracle Function *****
   (IF {@TechConsultant} = -1 AND 
      NOT ISNULL({Q.MONITORBY}) AND 
      UPPERCASE({Q.MONITORBY}) = UPPERCASE(CurrentCEUserName) AND 
      (ISNULL({Q.SAMPLESTATUSTYPE}) OR 
      {Q.SAMPLESTATUSTYPE}='I' OR 
      {Q.SAMPLESTATUSTYPE}='A' OR
      {Q.SAMPLESTATUSTYPE}='Q') then
      0
    Else 1) = 1 then
   0 
Else
   1

Here is the original code from an Oracle View since I can not pass the Current User ID to Oracle to determine the user

WHERE ((((IH.SampleNo = IH.MasterSampleNo) OR  IH.MasterSampleNo IS NULL))
OR  ((IHLT.IsTimeWeighted <> -1)))  AND IH.Approved=-1  AND  Med_SelectByTechnicianNew (IH.MonitorBy,(SELECT IHSB.SampleStatusType  FROM IHSampleStatus  IHSB  WHERE IHSB.SampleStatusID= IH.SampleStatusID))=-1

Oracle Function

 Med_SelectByTechnicianNew
(monitorby varchar2,v_type varchar2)
return number
is
    technician number(1);
    v_val number(1);
    cursor cr is
    select IHSB.TechnicianConsultant
    from IHSampledBy IHSB
    where upper(IHSB.MonitorBy)=upper(USER);
begin
    v_val := -1;
    open cr;
    fetch cr into technician;
    if not cr%NOTFOUND then
        if technician is not null and technician=-1 then
            if monitorby is not null and upper(monitorby)=upper(USER) then
                    if not (v_type is null or v_type='I' or v_type='A' or v_type='Q') then
                        v_val := 0;
                    end if;
             else
                v_val := 0;
            end if;
        end if;
    end if;
    close cr;
    return v_val;
end Med_SelectByTechnicianNew;

 

[SundancerKid]

I think I might have gotten past the syntax problem, but I am not getting any results here is my code.

I can see the results for @EvalTechnician but not @EvaluateWhereExists .

Any Suggestions?????

@EvaluateWhereExists
I am using this code as a suppress formula because I can not use it in the Select Expert section for evaluation because I am using a shared variable from a subreport.

WhilePrintingRecords;
IF (((({Q.SAMPLENO} = {Q.MASTERSAMPLENO}) OR ISNULL({Q.MASTERSAMPLENO}) OR 
   (({Q.ISTIMEWEIGHTED} <> -1))) AND {Q.APPROVED} = -1  AND 
   {@EvalTechnician} = 1)) then
   1
Else
   0

EvalTechnician

WhilePrintingRecords;
IF {@TechConsultant} = -1 AND 
    NOT ISNULL({Q.MONITORBY}) AND 
    UPPERCASE({Q.MONITORBY}) = UPPERCASE(CurrentCEUserName) AND 
    (ISNULL({Q.SAMPLESTATUSTYPE}) OR 
    {Q.SAMPLESTATUSTYPE}='I' OR 
    {Q.SAMPLESTATUSTYPE}='A' OR
    {Q.SAMPLESTATUSTYPE}='Q') then
    0
Else 
    1

Detail Line Suppress Formula

IF {@EvaluateWhereExists} = 1 then
   False
Else
   True

 

[lbass]

You must ALWAYS check for nulls before referencing the same field. So your formula should be changed to:

WhilePrintingRecords;
IF (
ISNULL({Q.MASTERSAMPLENO}) OR
{Q.SAMPLENO} = {Q.MASTERSAMPLENO}
) OR
(
{Q.ISTIMEWEIGHTED} <> -1 AND
{Q.APPROVED} = -1 AND
{@EvalTechnician} = 1
) then
1 Else
0

Not sure whether I clustered the clauses together correctly within the parens though. Adding unnecessary extra parens makes it difficult to see the logic, but parens are necessary with "or" statements in order to ensure the correct logic is applied.

You probably don't need the whileprintingrecords either and which impairs your ability to insert summaries on formulas.

-LB