MikeM670
asked on
Excluding data from one table field looking up that value from another
Using Crystal Reports IX with SQL Server 2014
I need to exclude a value based on the value stored in a table field where I lookup the value in another table. Since this report would be run by multiple agencies and they use their own user assigned value for a "House Watch" I need to find that value from the param3 field in the table ListUCR.
Table and Field I will be excluding data on:
Table Name: CaseMaster
Table Field: UCRCodeAll (Varchar)
7725,7741
7725
7314,7725
7314,7741,7725
Table and Fields I am looking up to obtain the value in the Code field based on a string value in a param3 field.
Table Name: ListUCR
Table Fields: Code (Char)
Table Field Param3 (Varchar)
Example Department 1
Code Description Param3
7225 HOUSE WATCH HOUSE WATCH (Query would use 7225)
Example Department 2
Code Description Param3
7845 HOUSE WATCH HOUSE WATCH (Query would use 7845)
I can get it to work if I hardcode a particular value into the query:
not ({CaseMaster.UCRCodeAll} like "*7225*")
But when I try the below listed query it fails to return any records.
{CaseMaster.UCRCodeAll} <> {ListUCR.Code} and ({ListUCR.Param3} Like 'HOUSE WATCH')
Mike
I need to exclude a value based on the value stored in a table field where I lookup the value in another table. Since this report would be run by multiple agencies and they use their own user assigned value for a "House Watch" I need to find that value from the param3 field in the table ListUCR.
Table and Field I will be excluding data on:
Table Name: CaseMaster
Table Field: UCRCodeAll (Varchar)
7725,7741
7725
7314,7725
7314,7741,7725
Table and Fields I am looking up to obtain the value in the Code field based on a string value in a param3 field.
Table Name: ListUCR
Table Fields: Code (Char)
Table Field Param3 (Varchar)
Example Department 1
Code Description Param3
7225 HOUSE WATCH HOUSE WATCH (Query would use 7225)
Example Department 2
Code Description Param3
7845 HOUSE WATCH HOUSE WATCH (Query would use 7845)
I can get it to work if I hardcode a particular value into the query:
not ({CaseMaster.UCRCodeAll} like "*7225*")
But when I try the below listed query it fails to return any records.
{CaseMaster.UCRCodeAll} <> {ListUCR.Code} and ({ListUCR.Param3} Like 'HOUSE WATCH')
Mike
ASKER
{ClosedCallControl.Enroute Badge} like ["*" + {?Officer}] and
{ClosedCallControl.Initiat ionTime} >= {?Start Date} and
{ClosedCallControl.Initiat ionTime} <= {?End Date} and
{ClosedCallControl.Agency} = {?Agency} and
{CaseMaster.UCRCodeAll} <> {ListUCR.Code} and ({ListUCR.Param3} Like 'HOUSE WATCH')
//not ({CaseMaster.UCRCodeAll} like "*7225*") (This works)
//{CaseMaster.UCRCode1} like {ListUCR.Code} where ({ListUCR.Param3} Like 'HOUSE WATCH') (This did not)
{ClosedCallControl.Initiat
{ClosedCallControl.Initiat
{ClosedCallControl.Agency}
{CaseMaster.UCRCodeAll} <> {ListUCR.Code} and ({ListUCR.Param3} Like 'HOUSE WATCH')
//not ({CaseMaster.UCRCodeAll} like "*7225*") (This works)
//{CaseMaster.UCRCode1} like {ListUCR.Code} where ({ListUCR.Param3} Like 'HOUSE WATCH') (This did not)
<> is not the same as NOT LIKE
Should this
{CaseMaster.UCRCodeAll} <> {ListUCR.Code} and ({ListUCR.Param3} Like 'HOUSE WATCH')
be
({CaseMaster.UCRCodeAll} <> "*" & {ListUCR.Code} & "*") and ({ListUCR.Param3} Like 'HOUSE WATCH')
mlmcc
Should this
{CaseMaster.UCRCodeAll} <> {ListUCR.Code} and ({ListUCR.Param3} Like 'HOUSE WATCH')
be
({CaseMaster.UCRCodeAll} <> "*" & {ListUCR.Code} & "*") and ({ListUCR.Param3} Like 'HOUSE WATCH')
mlmcc
ASKER
mlcc,
Thanks for getting back to me.
I tried the corrected query you provided and it only returns House Watches. Which is funny because that is what I wish to exclude from the query and return all other UCRCodes.
If I specifically hardcode a UCR Code value into the query it works fine but when I try to check the ListUCR Table for the Param3 value of "House Watch" it returns only house watches.
Mike
Thanks for getting back to me.
I tried the corrected query you provided and it only returns House Watches. Which is funny because that is what I wish to exclude from the query and return all other UCRCodes.
If I specifically hardcode a UCR Code value into the query it works fine but when I try to check the ListUCR Table for the Param3 value of "House Watch" it returns only house watches.
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did it! I had tried using not before but not against the Param3 value specifically.
Thank you very much for the assistance!
Thank you very much for the assistance!
mlmcc