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

fredong · Nov 9, 2004

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 · Nov 9, 2004

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 · Nov 10, 2004

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

fredong · Nov 10, 2004

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 · Nov 10, 2004

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

fredong

IS-IT--Management

synapsevampire

Programmer

fredong

IS-IT--Management

fredong

IS-IT--Management

synapsevampire

Programmer

Similar threads

Part and Inventory Search

Sponsor