Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Strange Problem

Status
Not open for further replies.
Mighty

Mighty

Programmer
Feb 22, 2001
1,682
0
0
US
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
 
Sort by date Sort by votes
Try isolating the original part of your where clause from the new part.
Code: 
...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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Just thinking out loud, could there be a space with the '3' values (' 3' or '3 ')?

Try using the trim function with your clause.
Code: 
trim(STATUS_10) = '3'

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

Mighty
 
Upvote 0 Downvote
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:
Code: 
STATUS_10 like '3%'
What version of PSQL are you using?



Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
 
Upvote 0 Downvote
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:

Code: 
STATUS_10 like '3%'

I get no records returned.

Mighty
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Got the same error message when I tried it on the server.

Mighty
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

richardleehl
  • Locked
  • Question
problem with aggregating data
Replies
1
Views
35
mirtheil
mirtheil
MzKitty
  • Locked
  • Question
Problems with a "Delete From" query.
Replies
1
Views
59
TNGPicard
TNGPicard
dison123
  • Locked
  • Question
Need help with a self join problem - PervasiveSQL
Replies
4
Views
43
dison123
dison123
hotzambo
  • Locked
  • Question
Problem Creating view Pervasive 8
Replies
8
Views
54
hotzambo
hotzambo
datenis
  • Locked
  • Question
Problem with insert trigger
Replies
5
Views
43
mirtheil
mirtheil

Part and Inventory Search

Sponsor

Back
Top