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
515 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

20 Experts available now in Live!

Get 1:1 Help Now