How to retrieve all data including NULL with the CASE statement?

[fredong]

I have a parameter field that will allow user to specify the vendor and I also have them put ALL to select the all VendorID include those with NULL values. However when user type in ALL it did not return any value in the report? There are 3 tables joined and below is my Edit Selection Formula-Record. Please advise. Thanks.

---@VendorID is a formula with the value {PRODUCT.VENDOR}

---------Edit Selection Formula-Record------------

select {?VendorID} --->from the parameter field


Case {?VendorID}{@VendorID} = {?VendorID})

Case "ALL"{@Vendor}={PRODUCT.VENDOR})

---------Edit Selection Formula-Record------------

 

[synapsevampire]

Try:

(
if {?VendorID} <> "ALL" then
{@VendorID} = {?VendorID}
else
if {?VendorID} = "ALL" then
true
)

I see that you're using a formula and NOT a database field. This may result in the record selection formula not being properly passed as SQL, so check the Database->Show SQL Query.

You may have better luck by using a SQL Expression instead of a formula, Crystal will pass SQL Expression references well.

-k

 

[fredong]

I am using Crystal v8.0 does this version comes with SQL expression? If yes, Where can I find it. Thanks.

 

[fredong]

How about how would you do 2 parameters with 2 conditions and one condition is selective and the other is ALL or vice versa.Example below.THanks.



if {?Itemid} <> "ALL" and {?WhseID} <> "ALL" then
{@itemid} = {?Itemid} and {@WhseID} = {?WhseID}

Else
if{@itemid} = {?Itemid} and {?WhseID} <> "ALL" then
{@itemid} = {?Itemid} and {@WhseID} = "ALL" ----????

 

[synapsevampire]

Perhaps you could include what's in the formula, as previously requested?

To add in a second parameter, do it all again:

(
if {?VendorID} <> "ALL" then
{@VendorID} = {?VendorID}
else
if {?VendorID} = "ALL" then
true
)
and
(
if {?ItemID} <> "ALL" then
{@ItemID} = {?ItemID}
else
if {?ItemID} = "ALL" then
true
)

You're missing the point of this, which is to ensure SQL Pass through.

You might have a look at my FAQ as well:

faq767-3825

-k