How to retrieve all data including NULL with the CASE statement?
How to retrieve all data including NULL with the CASE statement?
(OP)
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------------
---@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------------
Talk To Other Members
RE: How to retrieve all data including NULL with the CASE statement?
(
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
RE: How to retrieve all data including NULL with the CASE statement?
RE: How to retrieve all data including NULL with the CASE statement?
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" ----????
RE: How to retrieve all data including NULL with the CASE statement?
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