×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Strange Problem

Strange Problem

Strange Problem

(OP)
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

RE: Strange Problem

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

CODE

...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"))...

zemp

RE: Strange Problem

(OP)
NO - that didn't work

Mighty

RE: Strange Problem

(OP)
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

RE: Strange Problem

(OP)
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

RE: Strange Problem

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

RE: Strange Problem

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

Mighty

RE: Strange Problem

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
http://www.mirtheil.com

RE: Strange Problem

(OP)
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

RE: Strange Problem

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

RE: Strange Problem

(OP)
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

RE: Strange Problem

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

RE: Strange Problem

(OP)
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

RE: Strange Problem

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

RE: Strange Problem

(OP)
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

RE: Strange Problem

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

RE: Strange Problem

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

Mighty

RE: Strange Problem

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close