Formula in Select Expert

[mdewhurst]

im trying to set the following formula into the select expert. Crystal accepts the formula but doesnt seem to get the correct data. Are "And" - "or" statement ok for "Select Expert Formulas" ?

{part_bins.store} = {?Store} and
{part_bins.rop} = 0 or isnull ({part_bins.rop}) and {@Free Stock} < 0
or
{part_bins.store} = {?Store} and
{part_bins.qty} <= {part_bins.rop} and {part_bins.rop} > 0

 

[Naith]

When you're using IsNull in your selection criteria, it must come first, or it'll be overlooked.

It would have been helpful to see an example of the incorrect data being returned, but, if I may take a stab in the dark, if I was rewriting your criteria, I would do it like this:

((
IsNull({part_bins.rop})
OR {part_bins.rop} = 0
)
AND {@Free Stock} < 0
AND {part_bins.store} = {?Store}
)
OR ({part_bins.store} = {?Store}
AND {part_bins.qty} <= {part_bins.rop}
AND {part_bins.rop} > 0)

If that doesn't work it out, give us an example of how screwy the data being returned is.

Naith

 

[synapsevampire]

The important thing is to verify what is getting passed to the database, use Database->Show SQL Query.

And isnull statement isn't always true, Naith. I just tested the following and it was passed to the Where just fine (SQL Server 2000):

{av_rpt_AtsAssocNotSubmittedBatches.chvContactName} <> &quot;SFD&quot;
and
{av_rpt_AtsAssocNotSubmittedBatches.Prev1Month} <> 12
and
({av_rpt_AtsAssocNotSubmittedBatches.chvYmcaName} = &quot;&quot;
or
isnull({av_rpt_AtsAssocNotSubmittedBatches.chvYmcaName}))

returned:

WHERE
av_rpt_AtsAssocNotSubmittedBatches.&quot;chvContactName&quot; <> 'SFD' AND
av_rpt_AtsAssocNotSubmittedBatches.&quot;Prev1Month&quot; <> 12 AND
(av_rpt_AtsAssocNotSubmittedBatches.&quot;chvYmcaName&quot; = '' OR
av_rpt_AtsAssocNotSubmittedBatches.&quot;chvYmcaName&quot; IS NULL)

Akin to Naiths':

({part_bins.store} = {?Store}
and
({part_bins.rop} = 0 or isnull ({part_bins.rop}))
and
{@Free Stock} < 0)

or

({part_bins.store} = {?Store}
and
{part_bins.qty} <= {part_bins.rop}
and
{part_bins.rop} > 0)

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Naith]

I can't say that putting IsNull anywhere other than first has ever been processed for me - regardless of if it passes to the database or not.

What driver are you using? If you switch between odbc and native, is your query treated the same?

Naith