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
526 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

 

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
 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

617 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