RE: Using SQL to join an i5/os databasde and a Windows database

John,

Your life could be much easier if you could create a SQL view on that iSeries file (by the way, don't worry too much about the naming... even the veterans lose track of what's the current name. So you can call it anyway you want).

If you, or somebody you know is able, create a view using SQL. It would look something like the following:

CREATE VIEW yourViewName AS
SELECT char(youNumericField) as yourNewField,
... your other fields here ...
FROM PMEMPM

With that, you get a "logical" file named "yourViewName" with a character field instead of a numeric field.

If it is not possible to create a view, then you can probably use an SQL query.

I hope that helps,

Luis

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John McKee
Sent: Saturday, February 09, 2008 2:22 AM
To: Midrange Systems Technical Discussion
Subject: Using SQL to join an i5/os databasde and a Windows database

I am supporting a Windows application that is built on MS-SQL database. I write
Crystal reports, among other things.

I need to retrieve data from the i5 (forgive me, I still do not know what the
current name is) based on data from the MS-SQL database.

Bad news is that the key field I am using on the MS-SQL database is a character
field, which I convert to a numeric fiel to access the i5. The only direct way
to do that with standard Crystal syntax is to use a subreport. VERY VERY, VERY
SSSSSLLLLLLOOOOOOOOOOWWWWWWWWWWW.

In addition, the key field I am using is incomplete, as the database I am
accesing has two key fields: the one I have as second key, and a numeric field
(fixed value of 1 in this case) as the first key.

Since Crystal does allow SQL to be used, I was wondering if that would allow for
faster processing. Specifically, can a join be constructed between the two
systems so that the sub report is not needed?

Specifically, on the MS-SQL database, I have a string field, length 20. The
numeric field on the (I wish I knew the proper name. Keep wanting to say
AS/400, and I know from reading previous posts that is a dead name) i5 is
numeric, 7 packed, no decimal places. I think I might have tried, but can't
remember, creating a numeric field on the MS-SQL database and tring to link
that. If I recall, that fails because the field I create is not a database
field. Could be mistaken. Been a long time ago.

Sorry for the long winded question. But, I have to do something to improve
performance. I ran this mess for six hours today, and created a partial report
about 180 pages. Full report would be around 1000. This can't be good for
either MS-SQL or the i5 to do this much heavy lifting.

Thanks for suggestions.

John McKee