Crystal Reports 8.5: Conditional SQL Expression?
Crystal Reports 8.5: Conditional SQL Expression?
(OP)
Informix Database
Crystal Reports 8.5
Hello, im trying to create an SQL expression (Sub Query) in Crystal Reports which based on a conditional statement. At current I have the below SQL expression:
I then have the below 'formula' which I display in the report :
The reason I've used the formula is to produce a "0" if any null values are returned, previously when I tried totally the %INV. To Date SQL expression the report just returned a null value and ignored any other values.
The report is working correctly at the moment but I was wondering if I could produce a 'Conditional Statement' in a SQL Expression to replicate what the above SQL Expression and formula do.
I am thinking something like:
Could someone please advise me whether the above is possible in Crystal? And/or I should be posting in an Informix forum.
Crystal Reports 8.5
Hello, im trying to create an SQL expression (Sub Query) in Crystal Reports which based on a conditional statement. At current I have the below SQL expression:
CODE
%INV. To Date:
(
SELECT
sum (po_order_line.order_line_amnt)
FROM
popes:popesdba.po_order po_order,
popes:popesdba.po_order_line po_order_line,
popes:popesdba.po_invc po_invc
WHERE
po_fi_cnrct.cnrct_id = po_order.fi_cnrct_id AND
po_order.order_id = po_order_line.order_id AND
po_order.order_stat_cd = po_order_line.order_stat_cd AND
po_order.invc_id = po_invc.invc_id
)
(
SELECT
sum (po_order_line.order_line_amnt)
FROM
popes:popesdba.po_order po_order,
popes:popesdba.po_order_line po_order_line,
popes:popesdba.po_invc po_invc
WHERE
po_fi_cnrct.cnrct_id = po_order.fi_cnrct_id AND
po_order.order_id = po_order_line.order_id AND
po_order.order_stat_cd = po_order_line.order_stat_cd AND
po_order.invc_id = po_invc.invc_id
)
I then have the below 'formula' which I display in the report :
CODE
@INV. To Date:
if isnull({%INV. To Date}) then 0 else {%INV. To Date}
if isnull({%INV. To Date}) then 0 else {%INV. To Date}
The reason I've used the formula is to produce a "0" if any null values are returned, previously when I tried totally the %INV. To Date SQL expression the report just returned a null value and ignored any other values.
The report is working correctly at the moment but I was wondering if I could produce a 'Conditional Statement' in a SQL Expression to replicate what the above SQL Expression and formula do.
I am thinking something like:
CODE
IF NOT IS NULL (sum (po_order_line.order_line_amnt)) THEN
SELECT sum (po_order_line.order_line_amnt)
ELSE 0
FROM
popes:popesdba.po_order po_order,
popes:popesdba.po_order_line po_order_line,
popes:popesdba.po_invc po_invc
WHERE
po_fi_cnrct.cnrct_id = po_order.fi_cnrct_id AND
po_order.order_id = po_order_line.order_id AND
po_order.order_stat_cd = po_order_line.order_stat_cd AND
po_order.invc_id = po_invc.invc_id
SELECT sum (po_order_line.order_line_amnt)
ELSE 0
FROM
popes:popesdba.po_order po_order,
popes:popesdba.po_order_line po_order_line,
popes:popesdba.po_invc po_invc
WHERE
po_fi_cnrct.cnrct_id = po_order.fi_cnrct_id AND
po_order.order_id = po_order_line.order_id AND
po_order.order_stat_cd = po_order_line.order_stat_cd AND
po_order.invc_id = po_invc.invc_id
Could someone please advise me whether the above is possible in Crystal? And/or I should be posting in an Informix forum.
Talk To Other Members
RE: Crystal Reports 8.5: Conditional SQL Expression?
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Crystal Reports 8.5: Conditional SQL Expression?
RE: Crystal Reports 8.5: Conditional SQL Expression?
http://pu
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Crystal Reports 8.5: Conditional SQL Expression?
NVL function worked perfect cheers