Excluding Bad Data Records

[AngChristman]

Hello,
I am having an issue where we have three jobs that contain bad data. In our Database the field is defined for only 3 characters. However, the User Interface allows operators to input 8 characters. Now when I go to run a Crystal report it will not allow me to pull up any data and gives me an error first stating:

"Failed to open Row set"

Second:

"Query Engine Error: ‘HY000:[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Column stat-code in table PUB.sfeventcds has value exceeding its max length or precision.’"

So what I am attempting to do is use selection expert to exclude the jobs with the bad stat codes. This isn’t working because Crystal is reading all the records first before excluding the bad jobs. As soon as it encounters the bad data it goes to this error and the report comes up blank.

How can I exclude the bad data as it encounters it?

Thank you,
Angie

 

[Madawc]

Have you tried Database > Verify Database?

It always helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. For your query, please also say what it is your are accessing.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

How did you attempt to exclude the data in the select expert? What was your selection formula?

-LB

 

[AngChristman]

Hello LB,
This is what my selection expert formula looks like:

{@Week} = {?Week} and
{@Step} in ["150", "130"] and
{@AddDate} > DateTime (2008, 01, 01, 0, 0, 0) and
{@Year} = {?year} and
{@WC Type} in ["Color", "Blank"] and
not ({sfeventcds.kjobcode} in ["315755", "316566", "316858"])

I have tried to move excluding the job number up so it does this prior to reading the statcode, but it will not allow me to, or stay after I have made the change.

Also, this is what my SQL Looks like. I would like it to exclude the bad data prior to reading the Statcode (it’s in blue).

SELECT "sfeventcds"."start-time", "sfeventcds"."PlantCode", "sfeventcds"."mach-id", "sfeventcds"."kjobcode", "sfeventcds"."stat-code", "sfeventcds"."event-type", "sfeventcds"."op-code", "sfeventcds"."elapse-time"
FROM "SFDC"."PUB"."sfeventcds" "sfeventcds"
WHERE NOT ("sfeventcds"."kjobcode"='315755' OR "sfeventcds"."kjobcode"='316566' OR "sfeventcds"."kjobcode"='316858')
ORDER BY "sfeventcds"."PlantCode"

Thank you,
Angie

 

[lbass]

Try removing the not() and instead try:

len({sfeventcds.kjobcode}) <= 3 //or = 3?

-LB