Strange Problem

[Mighty]

I am having a rather strange problem. When I run the following query from Pervasive Control Center:

SELECT "Order Master"."ORDNUM_10","Order Master"."CURQTY_10","Order Master"."STATUS_10","Job Progress"."OPRSEQ_14","Job Progress"."WRKCTR_14","Job Progress"."QTYREM_14","Job Progress"."MOVDTE_14","Order Master"."CURDUE_10" FROM "Order Master","Job Progress" WHERE ("Job Progress"."OPRSEQ_14"='0099') AND ("Job Progress"."QTYREM_14"> 0) AND ("Order Master"."ORDER_10" = "Job Progress"."ORDNUM_14") ORDER BY "Order Master"."STATUS_10"

it returns several hundred records. In those records, the Status_10 field contains either '3' or '5'. So I only want to get the status '3' records so I modified the query accordingly:

SELECT "Order Master"."ORDNUM_10","Order Master"."CURQTY_10","Order Master"."STATUS_10","Job Progress"."OPRSEQ_14","Job Progress"."WRKCTR_14","Job Progress"."QTYREM_14","Job Progress"."MOVDTE_14","Order Master"."CURDUE_10" FROM "Order Master","Job Progress" WHERE ("Order Master"."STATUS_10"='3') AND ("Job Progress"."OPRSEQ_14"='0099') AND ("Job Progress"."QTYREM_14"> 0) AND ("Order Master"."ORDER_10" = "Job Progress"."ORDNUM_14") ORDER BY "Order Master"."STATUS_10"

However, this modified query returns no records - makes absolutely no sense to me. can anyone offer a suggestion. The status_10 field is a 1 character text field.

Mighty

 

[zemp]

Try isolating the original part of your where clause from the new part.

...WHERE ("Order Master"."STATUS_10"='3') AND [COLOR=red]([/color]("Job Progress"."OPRSEQ_14"='0099') AND ("Job Progress"."QTYREM_14"> 0) AND ("Order Master"."ORDER_10" = "Job Progress"."ORDNUM_14")[COLOR=red])[/color]...

zemp

 

[Mighty]

NO - that didn't work

Mighty

 

[Mighty]

When I don't specify a status it returns several hundred records where the status field is either '3' or '5'. I want just the '3' records. However, if I change the query to pull just the records where STATUS_10 = '5' it works??? But it won't work if I try to pull the records where STATUS_10 = '3'.

What the hell is going on???

Mighty

 

[Mighty]

The possible values for the STATUS_10 field are '3','4' and '5'. When I try to pull all the '3' records I get nothing (i.e. STATUS_10 = '3'). However, when I pull every except '4' and '5' (i.e. STATUS_10 NOT IN ('4','5')) - I get all the '3' records.

This is baffling me!!

Mighty

 

[zemp]

Just thinking out loud, could there be a space with the '3' values (' 3' or '3 ')?

Try using the trim function with your clause.

trim(STATUS_10) = '3'

zemp

 

[Mighty]

It is only a one character field - so it's not possible that there are few rogur spaces!!

Mighty

 

[mirtheil]

What actual data type is the status_10 field? I know you said it's "Text" but what's the actual data type according to the PCC?
WHat happens if you issue a statement with:

STATUS_10 like '3%'

What version of PSQL are you using?



Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Mighty]

How do I check that in PCC. When I click on Properties for the table in question I just get an error message. (I get this no matter what table I try!!)

I am using Pervasive SQL 2000i.

When I use:

STATUS_10 like '3%'

I get no records returned.

Mighty

 

[mirtheil]

What error message do you get?
Run a Database Consistency Check (right click the database name and select "Tasks" then "Check Database"). Post any failures for the table in question.
Also, right click the table and select "Edit Table Design" (it might be in "Tasks"). That'll give better information than "Table Properties".


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Mighty]

mirtheil,

Thanks for the response. Will check out the error message in a few minutes. But from the "Edit Table Design" the format of the field in question is "char" and the size is 1.

Mighty

 

[mirtheil]

Did the table pass a consistency check?
One more thing to check. Is this field and index? If so, what's the data type according to Btrieve (not SQL)? You can check that through the Maintenance Utility or a BUTIL -STAT on the file. If it's not an index, what happens if you add one using a statement like:
CREATE INDEX idxStat10 on "Order Master" (Status_10)

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Mighty]

This database is the backend DB behind our ERP application. I can't really go and change indexes as the software provider will not support it if I make any modifications. Will the consistency check affect anything - like the performance of the DB while it's running??

Mighty

 

[mirtheil]

No, the check won't affect performance. It's fairly quick.
Actually you can add indexes without affecting the operation of the ERP application but I can understand your apprehension.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Mighty]

While running a consistency check, I got the following error message:

The server threw an exception.
(0x80010105)

While checking the properties on a table, I get the following error message:

Unknown Error obtaining table properties
Unknown Scode (0x80040422)

Mighty

 

[mirtheil]

I've seen that from the client. Try running it at the server itself.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Mighty]

Got the same error message when I tried it on the server.

Mighty

 

[mirtheil]

Sounds like some serious problems with the DDFs. Which ERP application are you using? Have you contacted them to see if they have updated DDFs that are more ODBC compliant?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com