command table?

I am using the following Case statement in my Command Table SQL (oracle) script.

CASE
    WHEN
      bl.bag_type_cd IN ('DOUBLE CPD','TRIPLE CPD') THEN MOD(biad.reference_value,10)

    WHEN
      bl.bag_type_cd IN ('ALYX RBC','PLAS PHER') THEN MOD(biad.reference_value,6)

    WHEN
      bl.bag_type_cd = 'PLT PHER' THEN MOD(biad.reference_value,3)

    ELSE 99
END AS remainder


I would like to be able to use the"remainder" field in my WHERE statement and say

WHERE
remainder<>0

Does anyone have any suggestions? If there is a better forum for this question please let me know.

CJB

This is actually a SQL question rather than a Crystal question.

There are a couple of ways to go about this.

1. Put the entire case statement in the Where clause instead of referencing the field.

2. Leave the condition out of the where clause of your current query. Then set up an outer select statement that looks something like this:

Select *
from (
current query
) as data
where data.remainder <> 0

-Dell

You might also want to look at this blog post for more information about using commands:

https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

-Dell

Thanks for the input. I will try option 2.