Constraining query on decimal field 4

[jpl458]

I am using ACCESS against a Progress Database that has some number fields stored as Decimal. Using the QCBE grid to create queries, and when I enter >0 in the Criteria field it still returns data with zero in that column. I've tried cinverting to integer and nothing works. What am I missing.

Thanks

JPL

Access 10
windows 7

 

[dhookom]

What are you converting to zero? Can you share the SQL of your query?

Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

Here is the essential part of the query (Which is much larger)that still returns unexpected results:

SELECT PUB_PSHISTOR.[PSH-RcptNumb], PUB_PSHISTOR.[PSH-DiscAmt]
FROM PUB_PSCODES INNER JOIN PUB_PSHISTOR ON PUB_PSCODES.[PSC-TC] = PUB_PSHISTOR.[PSH-TC]
WHERE (((PUB_PSHISTOR.[PSH-DiscAmt])>0));

In the answerset There are zeros returned in the field PSH-DiscAmt.
In the database PSH-DiscAmt is defined as a decimal field.

I want to be able to find all the rows where the discount Amt is greater than Zero.

jpl

 

[jpl458]

Here is more information.
If I look for any number (1 thru ) I get the that number returned along with all the values of zero.

If I test for Zero I just get all Zeros back - no numbers.

Thanks

jpl

 

[Andrzejek]

What if you would just run the simplest query, like:
[tt]
SELECT [PSH-DiscAmt]
FROM PUB_PSHISTOR
WHERE [PSH-DiscAmt] > 0
[/tt]
Do you still have the same problem and get zeros anyway?

Have fun.

---- Andy

 

[PHV]

What about this ?
WHERE Nz(PUB_PSHISTOR.[PSH-DiscAmt],0)>0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

I'd try PHV's test first...

Have you linked the tables to Access? What is the datatype in the table design view in access?

This seems vaguely familiar to me. But it has been over a decade since I touched Progress and surely things have changed - not to mention the fact that I did little with it. Back then what I remember is the ODBC drivers for Progress are junk but surely they have fixed it by now. What I remember doing is writing Progress code to dump the data to a text file and importing that into Access. I think the command in Progress code is OpenBrowser to get data and there is some sort of for / foreach construct to loop the records. But I wouldn't trust my memory, I only mention it because it might help find it faster.

It could also be that the Access database engine sends requests the ODBC drivers don't properly understand (back to bad ODBC drivers). If Progress supports a Query language you might have better luck with a Pass through query (I don't remember it having one).

You might also try the below where clauses... Note the second uses the INT functon or FLOOR function. This will only help if there is some Floating point weirdness in datatype conversion. You might also try grater than a really small number (something with a bunch of zeros after the decimal and a 1). This too seems familar.

Where CDbl(PUB_PSHISTOR.[PSH-DiscAmt])>0

Where Int(PUB_PSHISTOR.[PSH-DiscAmt])>0

 

[jpl458]

Is there a way to display the data in a column in an ACCESS table they way it is actually stored in the database, how it looks in the file system. That way I could see if there is spurious date in that field. I tried both solution s above and get the same bad results. I think there is something in the field that displays a zero in decimal but is less than one and greater than zero.

Thanks for the help

jpl

 

[PHV]

And this ?
WHERE Round(Nz(PUB_PSHISTOR.[PSH-DiscAmt],0),0)>0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

Looks like some Progress ODBC Drivers don't play nice on decimal columns...

http://knowledgebase.progress.com/articles/Article/5372

http://communities.progress.com/pcom/thread/48986

Looks like Progress SQL wraps column and field names in double quotes much like Access SQL uses square brackets for long names... Odd but rolling with that and not digging too much, I'd try the following SQL in an SQL Pass through query...

SELECT "PUB_PSHISTOR"."PSH-RcptNumb", "PUB_PSHISTOR"."PSH-DiscAmt"
FROM "PUB_PSCODES" INNER JOIN "PUB_PSHISTOR" ON "PUB_PSCODES"."PSC-TC" = "PUB_PSHISTOR"."PSH-TC"
WHERE "PUB_PSHISTOR"."PSH-DiscAmt">0

The example I saw did not use a join, so if it does not work, research proper join syntax first unless of course you get a meaningful error.

 

[jpl458]

Done more research and have pared things down to this;
There are two tables: a point of sale table that records all transactions. If a sale includes more than 1 item it creates a row in the POS table for each of the item on the sales ticket, but all have the same receipt number. The second table is a facility table and is joined to the POS table via a transaction code and contains the name of the facility along with other stuff, none of which is germane to the problem.

When I run the following query with a constraint of >0 I get only one answer in the set.

SELECT PUB_PSHISTOR.[PSH-RcptNumb], PUB_PSHISTOR.[PSH-Date], PUB_PSHISTOR.[PSH-DiscAmt], PUB_PSHISTOR.[PSH-TC], [Facility Table].Facility
FROM PUB_PSHISTOR INNER JOIN [Facility Table] ON PUB_PSHISTOR.[PSH-TC] = [Facility Table].[PSC-TC]
WHERE (((PUB_PSHISTOR.[PSH-RcptNumb])=2330461) AND ((PUB_PSHISTOR.[PSH-Date]) Between #5/1/2011# And Now()) AND ((PUB_PSHISTOR.[PSH-DiscAmt])>0));

The same query with the >0 constraint produces the correct answer which is two receipt numbers for the two item that were sold

SELECT PUB_PSHISTOR.[PSH-RcptNumb], PUB_PSHISTOR.[PSH-Date], PUB_PSHISTOR.[PSH-DiscAmt], PUB_PSHISTOR.[PSH-TC], [Facility Table].Facility
FROM PUB_PSHISTOR INNER JOIN [Facility Table] ON PUB_PSHISTOR.[PSH-TC] = [Facility Table].[PSC-TC]
WHERE (((PUB_PSHISTOR.[PSH-RcptNumb])=2330461) AND ((PUB_PSHISTOR.[PSH-Date]) Between #5/1/2011# And Now()));

I have talked to the vendor and they don't have an answer. I can work around this but am curious as to why adding a constraint would produce a wrong answer.

Thanks in advance

jpl

 

[lameid]

If I recall what those links I posted said, the PROGRESS ODBC driver gets confused somehow and tries to use text instead of the correct data types in certain instances with the Decimal datatype.

Because the data types of the parameter is not as expected, you get weird results. You should be able to bypass this weirdness by sending a Progress SQL statement directly to the database as I suggested with a SQL Pass-through query. Because it is native code, it should figure it out correctly. Otherwise you do have to work around it.