Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • Last Modified:

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
0
MikeM670
Asked:
MikeM670
  • 3
  • 3
1 Solution
 
mlmccCommented:
What is the full query? or at least the full FROM and WHERE clauses?

mlmcc
0
 
MikeM670Author Commented:
{ClosedCallControl.EnrouteBadge} like ["*" + {?Officer}] and
{ClosedCallControl.InitiationTime} >= {?Start Date} and
{ClosedCallControl.InitiationTime} <= {?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)
0
 
mlmccCommented:
<> 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
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MikeM670Author Commented:
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
0
 
mlmccCommented:
If you don't want "house watch" then use NOT

({CaseMaster.UCRCodeAll} <> "*" & {ListUCR.Code} & "*") and (NOT(({ListUCR.Param3} Like 'HOUSE WATCH')))

mlmcc
0
 
MikeM670Author Commented:
That did it!  I had tried using not before but not against the Param3 value specifically.  

Thank you very much for the assistance!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now