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?

 

[HowardHammerman]

If your only objective is to produce a zero when a value is null you can, in Crystal 8.5, set null values to default in the dialog box found by clicking File, then Report Options.

Then you can use the field formatting options to show the zero.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[greengo204]

Howard Hammerman:

Cheers that did the trick although i did have to deselect grouping on server,

On a similar subject i don't suppose you know or anyone else knows how to add two SQL expressions together

something like:

(
SELECT
    sum(po_fi_invoice.amnt)
FROM
    popes:popesdba.po_fi_invoice po_fi_invoice
WHERE
    po_fi_cnrct.cnrct_id = po_fi_invoice.cnrct_id AND
    po_fi_invoice.invc_dt IS NULL
)
+
(
SELECT
    sum(po_fi_invoice.amnt)
FROM
    popes:popesdba.po_fi_invoice po_fi_invoice
WHERE
    po_fi_cnrct.cnrct_id = po_fi_invoice.cnrct_id AND
    po_fi_invoice.invc_dt IS NULL
)

I currently have the total using a formula but i was hoping to use an SQL expression.

Also for future reference can conditional statements be used through SQL expressions?

please forgive all the questions...

Cheers for your help.

 

[HowardHammerman]

I beleive you need a UNION or UNION ALL clause. And yes, you can add parameters. I bet one of the other experts can help you better at that than I can.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[greengo204]

thanks, will research further.

 

[greengo204]

just in case anyone has been following this and alternative way for Informix DB's to return zeros for NULL values is to use the NVL funtion:

(
SELECT
    NVL (sum (po_order_line.order_line_amnt), 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
)

this allows to keep the grouping on server option enabled.

 

[lbass]

Did you try the code you showed that adds the two subqueries? Should work as is.

-LB

 

[greengo204]

First my sample code was incorect above i meant:

(
SELECT
    sum(po_fi_invoice.amnt)
FROM
    popes:popesdba.po_fi_invoice po_fi_invoice
WHERE
    po_fi_cnrct.cnrct_id = po_fi_invoice.cnrct_id AND
    po_fi_invoice.invc_dt IS NULL
)
+
(
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
)

This did not give a correct total see previous post, this is tested checking and unchecking the 'convert NULL field value to default'

I have managed to total correctly by changing my SQL slightly to:

(
SELECT
    NVL (sum(po_fi_invoice.amnt), 0)
FROM
    popes:popesdba.po_fi_invoice po_fi_invoice
WHERE
    po_fi_cnrct.cnrct_id = po_fi_invoice.cnrct_id AND
    po_fi_invoice.invc_dt IS NULL
)

+

(
SELECT
    NVL (sum (po_order_line.order_line_amnt), 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
)

I think the NVL function is specific to Informix databases, see

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

Not sure why the origanal SQL expression never worked with 'convert NULL field value to default' enabled

Any suggestions?

Cheers

 

[lbass]

I think this is because SQL expressions directly access the database so the convert null settings within CR would not be applied.

-LB