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.

Jobs

SQL Question

SQL Question

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

RE: SQL Question

Hi,

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: SQL Question

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

RE: SQL Question

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: SQL Question

(OP)
It gives me error 'Invalid use of keyword'

RE: SQL Question

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: SQL Question

Quote:

It gives me error 'Invalid use of keyword'

You are prepending "SELECT [columnlist] FROM [table]" 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: Corel: Paradox

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.

RE: SQL Question

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

RE: SQL Question

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


Quote:

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: Corel: Paradox 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.

RE: SQL Question

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

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!

Resources

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