How to link two tables with two fields that are different data types and require a formula before linking

Hopefully there is a simple answer for this ?

I have two tables that I need to link:
INVOICE TABLE
{Invoice}
{Product}
{Date}
{Quantity}

SALES TABLE
{Invoice}
{Sales Code}
{Category}
{Value}

I need to link {Invoice.Invoice} to {Sales.Invoice}.

However {Invoice.Invoice} is a Number data type and {Sales.Invoice} is a String data type
Furthermore, the data in {Sales.Invoice} needs a formula to trim the first 2 characters.

The data in {Invoice.Invoice} =
123,345
123,346
123,347
123,348
123,349


The data in {Sales.Invoice} =
I0123345
I0123346
I0123347
I0123348
I0123349


Therefore I would like to trim the first two characters from {Sales.Invoice} then convert to a Number data type and then link {Sales.Invoice} to {Invoice.Invoice}.

Please suggest how I can do this with links in the database expert?


MchallinorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
Can you use the Add Command feature with your databse.

If so then you cann create virtual table by creating a query on the sales table which would convert the invoiceid to a number.  You can then link the Command with the invoice table.

something like...
Select *, val(mid(Invoice,3,6)) as InvNum from Sales
0
MchallinorAuthor Commented:
Sorry, but I am a novice, I have never created a virtual table with the Add Command.

I've given it a quick try, but it failed. Perhaps because the Two tables I would like to link are PROGRESS databases.
We use a Merant 3.60 ODBC driver.

I'm not sure we can use SQL queries?  (I could be wrong)

Do you know what select statement I might use with Progress Tables?

0
peter57rCommented:
As I understand it if the AddCommand option is there, you can use it.
But I'm afraid I know nothing about Progress sql.

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

MchallinorAuthor Commented:
Thanks Perter57r,

Yes it is possible to use SQL queries with Progress Databases (Merant ODBC Driver).

For example this query works:

SELECT sales.invoice, sales.period, sales.yearno
FROM OPENACC.sales sales
WHERE (sales.period=10.00) AND (sales.yearno=2008)

This returns a "Command" table with the {Sales.Invoice},{Sales.Period},{Sales.YearNo} data.

However, this formula to trim the "123349" from "I0123349" is not working.

I have tried :

SELECT sales.period, sales.yearno, val(mid(sales.invoice,3,6)) as sales.invoice
FROM OPENACC.sales sales
WHERE (sales.period=10.00) AND (sales.yearno=2008)

Does not work?

The error message  " Unable to Understand after -- "DECALARE". (247) "


Can you please suggest where I might be going wrong?

0
peter57rCommented:
val(mid(sales.invoice,3,6))
Looking at the progress 92 manual on-line this appears to convert to:

To_Number(substr(sales.invoice,3,6))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MchallinorAuthor Commented:
Hi perter57r,

Sorry, this didn't work?

SELECT sales.period, sales.yearno, to_number(substr(sales.invoice,3,6)) as sales.InvoiceNumber
FROM OPENACC.sales sales
WHERE (sales.period=10.00) AND (sales.yearno=2008)

Do you have a link for the Progress manual?  or any other suggestions?   Thanks!
0
peter57rCommented:
"as sales.InvoiceNumber"

I would not have expected to use the sales. prefix for this (but I don't know if that's required by progress.)

http://www.psdn.com/library/servlet/KbServlet/download/1094-102-902/s92.pdf
0
MchallinorAuthor Commented:
Thanks Peter57r.

The error message  " Unable to Understand after -- "DECLARE". (247) " was caused by the incorrect ODBC driver.
I was actually using an Openlink Driver and this does not support all the SQL-92 commands.

I have upgraded my driver to a Merant ODBC Driver and now all the SQL commands work, and this formula works too.

Thanks for you help

Mchallinor.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.