|
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.
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
