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

My first experience with CRW was Crystal 7. It was a dog. It did bring the
entire database down to the pc. If you were lucky and careful, it could group
on the serve and just send the group results.

Currently, the application I support uses Crystal 8.5. It is much faster. Tends to do a LOT more on the server. Simple reports at Crystal 7 could take
hours, but the same report at Crystal 8.5 takes seconds. Some of that might
also be the server upgrade, and/or change from btrieve to MS-SQL. Don't know
and no way to find out as that server is long gone.

Late Friday, I printed a portion of the report I had generated so far. Came to
180+ pages.

Looking at that big partial report and the much smaller report now, after adding
the first part of the AS/400 primary key as a parameter to the subreport, I see
differences. The big report has information on it that is not on the smaller
report. Simple terms: names were skipped on the small report. I am looking
into that now. I am wondering if my first effort Friday, where subreport only
had key 2 given to it might have retrieved records incorrectly.

John McKee

Quoting vhamberg@xxxxxxxxxxx:

John

It's been so long that I've messed with Crystal and hated it! Crystal makes least common denominator decisions. - Crystal will tend to bring everything down, then do the filtering and sorting on the local machine - horrible if you need a small subset from the server. But there is some way to make things run on the server.

Maybe that will start you in a good direction

Vern

-------------- Original message ----------------------
From: John McKee <jmmckee@xxxxxxxxxxxxxx>

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.

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