Parameters - using one parameter "OR" another to query 1

[piovesan]

Hi all. I am using Crystal 11.5 and I am having a problem with a parameter I am trying to set. So far, I have the following:

SELECT "LND_PR_REVIEW_YN"."ID", "LND_PR_REVIEW_YN"."ASSET_ID",
"LND_PR_REVIEW_YN"."PID", "LND_PR_REVIEW_YN"."PROJ_DATE",
"LND_PR_REVIEW_YN"."RE_NOTES"
FROM "SDEADM"."LND_PR_REVIEW_YN" "LND_PR_REVIEW_YN"
WHERE "LND_PR_REVIEW_YN"."PID"='00186387' OR
"LND_PR_REVIEW_YN"."ASSET_ID"=0
ORDER BY "LND_PR_REVIEW_YN"."ID" DESC

I have the tables linked on the PID (Left outer join, not enforced) where LND_PR_REVIEW_YN is the left table.

PID is a character field (text) whereas ASSET_ID is an integer.

My need is to have the user be able to enter EITHER a PID, or an Asset_ID and get the related reviews. Sometimes the records in the PR_REVIEW table has both an Asset_ID AND a PID, but sometime it is one or the other.......

My select expert looks like this:{LND_PR_REVIEW_YN.ASSET_ID} = {?Query by AssetID} or
{LND_PR_REVIEW_YN.PID} = {?Query by PID}
and with this one, I can get all records in the PR_REVIEW table that have ONLY an ASSET_ID, or both an Asset_ID as well as a PID, by entering either one as a parameter. BUT I CANNOT RETRIEVE RECORDS THAT HAVE ONLY A PID.

If I reverse it, and have the select expert like this:
{LND_PR_REVIEW_YN.PID} = {?Query by PID}or LND_PR_REVIEW_YN.ASSET_ID} = {?Query by AssetID}
then I can get all records that have ONLY a PID, or both a PID and ASSET_ID, but I CANNOT RETRIEVE RECORDS THAT ONLY HAVE AN ASSET_ID.

What am I doing wrong???
Thanks!!

 

[lbass]

Your query shows only one table as far as I can tell, so not sure how a second table might factor in. It looks like both fields might be null at times, so you could try something like this:

(
(
isnull({LND_PR_REVIEW_YN.ASSET_ID}) or
{LND_PR_REVIEW_YN.ASSET_ID} = {?Query by AssetID}
) or
(
isnull({LND_PR_REVIEW_YN.PID}) or
{LND_PR_REVIEW_YN.PID} = {?Query by PID}
)
)

This would allow in records where one or more of the fields are null.

-LB

 

[piovesan]

With the exception of about 10 records in the table though, ALL the records in this table have either the PID or Asset_ID null..... most records in this table have EITHER a PID, or an Asset_ID but not both........... so your formula actually dosn't work for me because it gives back all but the 10 records that have both.......

 

[lbass]

Please explain what you want returned more specifically. I think that formula should return all null records or those that meet the parameter criteria. If you want to show ONLY those that meet the parameter criteria, try:

(
(
isnull({LND_PR_REVIEW_YN.PID}) and
not isnull({LND_PR_REVIEW_YN.ASSET_ID}) and{LND_PR_REVIEW_YN.ASSET_ID} = {?Query by AssetID}
) or
(
isnull({LND_PR_REVIEW_YN.ASSET_ID}) and
not isnull({LND_PR_REVIEW_YN.PID}) and
{LND_PR_REVIEW_YN.PID} = {?Query by PID}
) or
(
not isnull({LND_PR_REVIEW_YN.ASSET_ID}) and
not isnull({LND_PR_REVIEW_YN.PID}) and
(
{LND_PR_REVIEW_YN.ASSET_ID} = {?Query by AssetID} or
{LND_PR_REVIEW_YN.PID} = {?Query by PID}
)
)
)

-LB

 

[piovesan]

This is exactly what I needed!! Thank you so much!