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

Would it make sense to import the iSeries table to mySQL, so the whole query runs locally? Obviously, the main concern would be that the table would have to be updated before running the report. An option could be to run nightly extracts.

Regarding to the big discrepancy in the number or pages... maybe the "1" do not really occur more than 99% of the time. Or, if you have a large number of records, even a small percentage makes a lot of difference (1% of 1,000,000 records is 10,000, which is equivalent to roughly 200 pages).

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John McKee
Sent: Monday, February 11, 2008 11:34 AM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Using SQL to join an i5/os databasde and a Windows database

Ideally, I would create a view. But, that could also cause other issues to
surface, due to among other things, politics.

Crystal Report Writer does have the capability to convert to SQL. I
did not lwt
it complete, but retrieved the initial definition, which is below. In looking
at that SQL, it does not contain anything about the database on the AS/400,
which is accessed, painfully slowly, by a subreport.

Is there a way to modify this SQL to access both databases at one time?

This is the main report SQL:

SELECT
POST_CASE."MEDICAL_RECORD_NO", POST_CASE."PATIENT_NAME",
POST_CASE."DELETE_FLAG",
CLINICAL."FACILITY_ACCOUNT_NO",
FACILITY_PATIENT."FMRN",
IMPLANT."CASE_NO",
DEMOGRAPHIC."PATIENT_NAME"
FROM
{ oj ((("ORSOSLIVE"."ORSOSLIVE"."POST_CASE" POST_CASE LEFT OUTER JOIN
"ORSOSLIVE"."ORSOSLIVE"."FACILITY_PATIENT" FACILITY_PATIENT ON
POST_CASE."MEDICAL_RECORD_NO" =
FACILITY_PATIENT."MEDICAL_RECORD_NO" AND
POST_CASE."FACILITY_ID" = FACILITY_PATIENT."FACILITY_ID")
LEFT OUTER JOIN "ORSOSLIVE"."ORSOSLIVE"."IMPLANT" IMPLANT ON
POST_CASE."CASE_NO" = IMPLANT."CASE_NO")
LEFT OUTER JOIN "ORSOSLIVE"."ORSOSLIVE"."CLINICAL" CLINICAL ON
POST_CASE."ACCOUNT_NO" = CLINICAL."ACCOUNT_NO")
LEFT OUTER JOIN "ORSOSLIVE"."ORSOSLIVE"."DEMOGRAPHIC" DEMOGRAPHIC ON
FACILITY_PATIENT."MEDICAL_RECORD_NO" = DEMOGRAPHIC."MEDICAL_RECORD_NO"}
WHERE
(POST_CASE."DELETE_FLAG" IS NULL OR
(POST_CASE."DELETE_FLAG" = 'z' OR
POST_CASE."DELETE_FLAG" = ' ')) AND
IMPLANT."CASE_NO" IS NOT NULL
ORDER BY
DEMOGRAPHIC."PATIENT_NAME" ASC

And this is the subreport SQL. For purposes of getting immediate
access to the
SQL, I had to enter the subreport parameter, which is just a short string,
specifically "123456". This is not the entire key field, as the
subreport also
needs to test for BRMDRWU."RWHSP#" = 1

SELECT
BRMDRWU."RWMRC#", BRMDRWU."RWACCT"
FROM
"S1015362"."BSYDTAC"."BRMDRWU" BRMDRWU
WHERE
BRMDRWU."RWACCT" = 123456

(DUH category now)

I changed the subreport to use a hard codes record selection for
RWHSP#, so now
subreport SQL now looks like this:

SELECT
BRMDRWU."RWHSP#", BRMDRWU."RWMRC#", BRMDRWU."RWACCT"
FROM
"S1015362"."BSYDTAC"."BRMDRWU" BRMDRWU
WHERE
BRMDRWU."RWACCT" = 123456 AND
BRMDRWU."RWHSP#" = 1

I am very troubled by this. Before I added the proper second parameter, for
RWHSP#, it was agonizingly slow, but produced a report for last name starting
with "A" that was over 80 pages long. Now, it is blazing fast, but report is
now 8 pages?????

Since RWHSP# is key 1 and RWACCT is key 2 of the AS/400 database file, it is
obvious that both should be in the selection report. In better than 99
percent
of the time, RWHSP# will be equal to 1. But, why is report so very much
shorter? That is a question I am asking myself and also wondering if
something
else might be going on that some guru might be able to answer for me.

Thanks.

John McKee


Quoting Luis Colorado <LuisC@xxxxxxxxxxxxxxxx>:

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.