SQL Question

[OrcusDanny]

Hi,
I am working for a bespoke application provider and am stuck with an SQL query I am trying to write.
I only have basic to intermediate level SQL knowledge and the main issue I have is that the application is BDE/Paradox SQL based so some of the standard SQL queries that would work in MSSQL do not function.
I want to select all items from a table where the values in the relevant column have more than 4 decimal places but nothing I can find will work due to the limitations of the SQL database.
I have the ability to build multi-stage queries using answer tables to refer back to where necessary but am stuck at an empass on how to write it. Can anyone help me?

 

[SkipVought]

Hi,

Plz give some specific examples of values that should and should not be selected.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[OrcusDanny]

Ones that SHOULD be selected include numbers like -1.234567790909E-45 and 4.00267821245E-52 and 0.0200000009
Numbers that should NOT be selected are ALL numbers that have LESS than 5 decimal places.
The issue is mainly the fact that some of the values I want to select are NEGATIVE values and others are POSITIVE as well as some having E-** values and others not but I am attempting to build a query that will take all of the values that have 5 or MORE decimal places and replace them with a 0.

 

[SkipVought]

Where ([YourField]*10^5)<>INT([YourField]*10^5)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[OrcusDanny]

It gives me error 'Invalid use of keyword'

 

[SkipVought]

You might need to look at the functions in your db library.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ChrisHirst]

It gives me error 'Invalid use of keyword'

You are prepending "SELECT [columnlist] FROM

" to the query?

Plus, as I recall from my days of working with Borland tools and Paradox, while it is ANSI-92 there are differences. So instead of trying to shoehorn MySQL queries into Paradox, you probably should ask your question in the appropriate section of Tek-Tips ... which is forum177



Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

 

[OrcusDanny]

Yes I am writing the query as
SELECT [columnnames] from [tablename]
Where ([column]*10^5)<>INT([column]*10^5)

And I get the error message 'Invalid use of keyword'

Please note that the system is based on Borland Database Engine and paradox SQL so it doesn't support a chunk of the stuff you would get through MS-SQL or Oracle.

 

[ChrisHirst]

The error means you are using a Paradox command, known as a 'reserved word' as a table or column name.

Please note that the system is based on Borland Database Engine and paradox SQL so it doesn't support a chunk of the stuff you would get through MS-SQL or Oracle.

EXACTLY RIGHT!!!!!!!!!

This is the MySQL forum not a Paradox forum which is why I suggested you post in the section where the members who KNOW and USE Paradox will be able to tell you how to solve the issue.

In case you missed it forum177 is the appropriate location.

Click the above link ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ask your question there.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

 

[OrcusDanny]

My apologies, I didn't see that forum in the listings. However, I did manage to find a solution:

Query 1 - Returns all rows that have the error I am looking for:
SELECT costcentre, productcode, freestock
FROM product
WHERE CAST(freestock * 1000 AS INTEGER) <> freestock * 1000
AND costcentre ='01'


Query 2 - corrects each value to an acceptable value for the data it is holding:
UPDATE product
SET freestock = CAST(CAST(((freestock * 1000)+0.5) AS INTEGER) AS FLOAT(10,4)) / 1000
WHERE costcentre + '||' + productcode IN ( SELECT costcentre + '||' + productcode FROM [ANSWER1] )
AND costcentre ='01'

Query 3 - generates a report on the changes made:
SELECT a.costcentre,
a.productcode,
a.freestock FreeStkOld,
p.freestock FreeStkNew
FROM [ANSWER1] A,
product P
WHERE p.costcentre = a.costcentre
AND p.productcode = a.productcode
ORDER BY a.costcentre,
a.productcode

Thank you to everyone for trying to help and I will sign on to the forum you pointed out now. Thanks again.

There are 10 types of people in the world - those who understand binary and those who don't.
Best error message in the world: ID-10-T