We help IT Professionals succeed at work.

SQL Code error

Gordon Hughes
Gordon Hughes asked
on
Hi
Can anyone tell me what is wrong with this formula
{WOEQLIST.LOCATION} = {?Pm-?Factory} and
({WOEQLIST.UD3} = "Yes" or
({WO.WOTYPE} = 'WRF' and {WOEQLIST.UD3} is not 'Resolved')

It keeps syaing the ) is missing before the is not
Gordon
Comment
Watch Question

Commented:
you are miss a (:
{WOEQLIST.LOCATION} = {?Pm-?Factory} and
({WOEQLIST.UD3} = "Yes") or
({WO.WOTYPE} = 'WRF' and {WOEQLIST.UD3} is not 'Resolved')
Gordon HughesDirector

Author

Commented:
Hi
Thanks for your speedy response
It still says missing ) before the is not
Gordon
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
There is no IS in Crystal except for use with arrays

Try it this way

{WOEQLIST.LOCATION} = {?Pm-?Factory} and
({WOEQLIST.UD3} = "Yes" or
({WO.WOTYPE} = 'WRF' and {WOEQLIST.UD3} <> 'Resolved') )

What values are in WOEQLIST.UD3?

mlmcc
Gordon HughesDirector

Author

Commented:
Hi mimcc

I recognised it should be <> but is still does not work
The WOEQLIST.UD3 could have a value or be blank, which I think contibutes to the issue
I have got around the issue by puuting a value in the field, but am concerned the users may forget
Gordon
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
If it has no value do you want those?

{WOEQLIST.LOCATION} = {?Pm-?Factory} and
((Not IsNull({WOEQLIST.UD3}) AND {WOEQLIST.UD3}  = "Yes") or
({WO.WOTYPE} = 'WRF' and (IsNull({WOEQLIST.UD3})
OR  {WOEQLIST.UD3} <> 'Resolved')))

mlmcc

Gordon HughesDirector

Author

Commented:
This mmic is good