Crystal Reports 8.5: Conditional SQL Expression? 1

[greengo204]

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:

%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
)

I then have the below ‘formula’ which I display in the report :

@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:

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

Could someone please advise me whether the above is possible in Crystal? And/or I should be posting in an Informix forum.

 

[PHV]

In informix you may use the NVL function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[greengo204]

cheers will look into the NVL function

 

[PHV]

Have a look here:

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls1002.htm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[greengo204]

PHV:

NVL function worked perfect cheers