Solved

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

Posted on 2008-10-29
8
516 Views
Last Modified: 2008-11-10
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?


0
Comment
Question by:Mchallinor
  • 4
  • 4
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22833116
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
 

Author Comment

by:Mchallinor
ID: 22833625
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
 
LVL 77

Expert Comment

by:peter57r
ID: 22833868
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
 

Author Comment

by:Mchallinor
ID: 22841277
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 22841512
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
 

Author Comment

by:Mchallinor
ID: 22841581
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
 
LVL 77

Expert Comment

by:peter57r
ID: 22841881
"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
 

Author Comment

by:Mchallinor
ID: 22922676
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now