Problem with an OR statement

[anonymouscdn]

When I run this code. It is only doing what is ahead of the or statement and never what's after it. If I run the code in SQL Designer it runs correctly. But I don't want to run it in the designer if I don't have to.

{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
)

Also I have one more quick question. Is there an easy formula i could put in a report alert. That would display all the duplicate data (example of sometihng specific would be employee ID's) in a report. I have to convert old cobolt programs in to crystal. basically cut and paste the sql code into crystal. So sometimes i could have up to an 80 page report. And don't want to search through all 80 pages to find duplicates. So I thought I could have a report alert displaying all the duplicate entries. Thnx in advanced for any help you can give me.

 

[synapsevampire]

Please post techincal information when requesting it, such as Crystal version, database and connectivity used.

When you say SQL Designer, are you speaking of Crystal's product that not even Crystal suggests anyone use ? Honestly, try to avoid using it, it's just an extra layer of potential problems.

I'm not sure what you expect to be returned, yuo might try providing example data and expected output.

Try this (assuming that you're speaking of a record selection formula):

(
{EC_EMPLOYEE.EMP_GROUP_CODE} startswith "CU"
)
and
(
{EC_EMPLOYEE.PREVIOUS_TERMINATION_DATE} > "19971231"
and
{EC_EMPLOYEE.PREVIOUS_TERMINATION_CODE} = "RETIRED"
)
and
(
{EC_EMPLOYEE.TERMINATION_DATE} > "19971231"
AND
{EC_EMPLOYEE.TERMINATION_CODE} = "RETIRED"
)

-k

 

[Naith]

I would agree with the suggestions of supplying some example data, and also with populating your record selection criteria with something like the suggested code above.

You might want to change the "and" to "or" between the previous and current termination clauses in SynapseVampire's code.

Also report if switching the PREVIOUS_TERMINATION_DATE and TERMINATION_DATE clauses affects the data returned.

Naith

 

[anonymouscdn]

I am using Crystal Reports 8.5, Oracle DB, ODBC - Oracle8 v3.6, and it was the Crystal Reports SQL Designer. The report is grabbing retirees over the last 5 years. The report was displaying Employee ID, Name, Address, Group, Termination Date, Termination Code, Previous Termination Code and Previous Termination Date. If I would run the code above it would only return people that had Previous Termination Code and Date. But I also need it to return Poeple that have a termination date and code. So basically it will only display one or the other depending on which group I put before the OR statement. It will not show anything after the OR statment. I actually got by that problem and hoping you can answer this question. I happened to check the SQL Query and noticed even though I had brackets in the Select Expert. They where not showing up in the SQL Query. The Brackets that where missing in the SQL Query are the extra bracket before ("EC_EMPLOYEE"."PREVIOUS_TERMINATION_DATE" >
and a bracket after
"EC_EMPLOYEE"."PREVIOUS_TERMINATION_CODE" = 'RETIRED')
and an extra bracket after
"EC_EMPLOYEE"."TERMINATION_CODE" = 'RETIRED'))
So if I manually add in the missing brackets in the SQL Query. The formula will run fine. Why would the Select Expert ignore brackets that i have typed in and it accepts? Is it bad programming on my part? or is the formula I am using correct. Or it just has to be done a different way for crystal reports to interpret. Hopefully this clears things up a bit more. So if you could clear up the issue of. Is my formula just plain ugly, or it's fine. just needs to be done alittle differently for crystal to understand it. Thx again!!

 

[anonymouscdn]

Just incase here is how the report should look when displaying both types of termination dates and codes.
Prev Prev
Term Term Term Term
ID Name Address Group date code Date Code
--- ------ ---------- ------ -------------- --------
1 Test 123 test CUPPP 20000630 retired
2 Test2 123 test2 CUTTT 19990502 Ret
ired


this is a bit ugly.. hopefully you can see what i was doing.

 

[Ngolem]

I am not sure why those brackets are ignored....but to answer your question about Duplicate data alerts

In the detail section Expert for background color place this formula

WhilePrintingRecords;
if not onfirstRecord then
(
if previous({Table.value1} = {Table.value1} and
previous({Table.value2} = {Table.value2} and
previous({Table.value3} = {Table.value3} and
previous({Table.value4} = {Table.value4} and
... add the rest of the fields ...
previous({Table.valueN} = {Table.valueN} then
crYellow
else
crNoColor;
)
else
crNoColor;


this will highlight all duplicate rows in yellow for you.






Jim Broadbent

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

 

[Naith]

The brackets are ignored because you are using the Select Expert, which executes "artistic licence" on your code.

To stop this occuring, directly input your selection criteria by going to the Report menu, Selection Formulas, and either Record or Group. In this case, Record.

Now check the brackets.

Naith