OR statement Help

[anonymouscdn]

Running Crystal Reports 8.5 / Oracle DB
Having an problem with and OR Statement. It will only display what is before the OR but will not show anything after the OR. Here is the formula:
{EC_EMPLOYEE.EMP_GROUP_CODE} startswith "CU" and
(
({EC_EMPLOYEE.PREVIOUS_TERMINATION_DATE} > "19971231" and
{EC_EMPLOYEE.PREVIOUS_TERMINATION_CODE} = "RETIRED&quot
OR
({EC_EMPLOYEE.TERMINATION_DATE} > "19971231" AND
{EC_EMPLOYEE.TERMINATION_CODE} = "RETIRED&quot
)
Now if I run this exact code in the Crystal SQL Designer. It works with no problems. I do not want to have to use the SQL Designer. Another problem I noticed is that When I go into SQL Query. It is missing brackets. If I go back into the Select Expert. I can see the brackets. So I tried going back into the SQL Query and manually put in the missing brackets. The report worked fine. But again also I do not want to have to do that. I feel I should be able run this simple or statement through the select expert. Here is what the table looks like. If I run this above formula through the select expert.

TD = Termination Date
TC = Termination Code
PTD = Previous Termination Date
PTC = Previous Termination Code

Name TD TC PTD PTC
-----------------------------------------
Test 19990610 Retired
Test2 20001120 Retired

It is only displaying what is before the OR. Here is what the table should look like. As in when i go into SQL Query and manually put in the missing brackets.

Name TD TC PTD PTC
-----------------------------------------
Test 19990610 Retired
Test2 20001120 Retired
Test3 19980212 Retired
Test4 20010519 Retired

It displays both sides of the OR. So what I have noticed is that the above formula looks right in the select expert. But when you view the acutal code in the Sql Query. Some of the brackets are missing.

 

[synapsevampire]

Try:

(
{EC_EMPLOYEE.EMP_GROUP_CODE} startswith "CU"
)
and
(
{EC_EMPLOYEE.PREVIOUS_TERMINATION_CODE} = "RETIRED&quot
)
and
(
{EC_EMPLOYEE.PREVIOUS_TERMINATION_DATE} > "19971231"
OR
{EC_EMPLOYEE.TERMINATION_DATE} > "19971231"
)

-k

 

[anonymouscdn]

That one didn't work. Still only showing people that have Previous Termination Date and Code.

 

[Madawc]

My advice would be:

a) Put the more complex tests into Formula fields. Thus maybe @L_Prev
{EC_EMPLOYEE.PREVIOUS_TERMINATION_DATE} > "19971231"
and
{EC_EMPLOYEE.PREVIOUS_TERMINATION_CODE} = "RETIRED"
Then @L_Term
{EC_EMPLOYEE.TERMINATION_DATE} > "19971231" AND
{EC_EMPLOYEE.TERMINATION_CODE} = "RETIRED&quot
Then @L_Retired
@L_Prev or @L_Term
And so on till the actual selection is simple.


You could also try selecting everything and displaying the formula fields (which will say True or False), just to check you have the data you think you have.

Madawc Williams
East Anglia, Great Britain

 

[Ngolem]

How is this different from your previous post on this subject...????

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[Ngolem]

thread149-710295 in CR forum 4

this was your post and Naith seemed to have a reason why you were having problems.

Otherwise, give a lot more detail about your application since you are not helping your cause

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[anonymouscdn]

Ngolem... I realized that I had posted in the incorrect form. Also I thought I gave alot more detail this time than in my fisrt post. And Naith did not have the solution to my problem. Thx anyways