command table?
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22739
Printed Date: 02 May 2024 at 6:04pm
Topic: command table?
Posted By: cbaldwin
Subject: command table?
Date Posted: 07 Mar 2019 at 4:36am
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
|
Replies:
Posted By: hilfy
Date Posted: 07 Mar 2019 at 6:10am
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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: hilfy
Date Posted: 07 Mar 2019 at 6:12am
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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: cbaldwin
Date Posted: 07 Mar 2019 at 6:16am
|
Thanks for the input. I will try option 2.
|
|