Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
537 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

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 2000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

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…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

876 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