RE: CCSID and SQL -- MIDRANGE-L

A couple ideas:

1. What if you put CCSID 66535 on both fields in the DDS. That's a "leave-it-alone" setting, IIRC.

2. Could you set up referential integrity with foreign keys and *DELETE *RESTRICT? Then you'd get a message if records exist in the dependent files. I don't know the CCSID implications.

3. Or use EXCEPTION JOIN on the key fields - then the only records returned are those with nothing in dependent files. Add all the key fields to the example, of course. It might be faster to use the key fields only in the field list - might allow index-only processing, esp. in the second version below.

select * from master exception join detail1 on master.fld1 = detail1.fld1 exception join detail2 on master.fld1 = detail2.fld1

This is the same, I think, as correlated subqueries in NOT EXISTS predicates

select * from master where not exists (select * from detail1 where fld1 = master.fld1) and not exists (select * from detail2 where fld1 = master.fld1)

4. There's always READ master with SETLL EQ on both detail files. Would CCSID matter there?

One thing I recommend to simulate multiple-format logicals with a UNION is to use the common key fields, then some identifier for each type of record (record indicator in RPGII terms), then a concatenation of all remaining fields that I need into a character field. When fetched, I'd use a SELECT CASE on the identifier to move that character field to a DS (RPG) or struct (C/C++).

I thought you could have VIEWs in the subselects that comprise a UNION. Is this not true? I know that only in V5R2 can a VIEW be constructed from a UNION.

HTH

Vern

At 08:09 AM 8/6/2003 +0200, you wrote:

Vern,

Our problem is: verify, before deleting a row in a master file, that the
item is not present in two other files.
We also try first to use SQL UNION.  But in our case, the two physical files
have the same key, but different format and we would use "UNION of VIEW",
which is not possible. It is the reason we must use logical multi-format.
CCSID keyword exist at field level, but our application must work anywhere
in the world, and we cannot hardcode the CCSID value.
Is there any other solution?

Michel-Claude Ducrest
tel. +41 21 924 16 14
fax +41 21 924 2882


-----Original Message-----
From: Vern Hamberg [mailto:vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx]
Sent: Tuesday,5. August 2003 16:38
To: Midrange Systems Technical Discussion
Subject: Re: CCSID and SQL

Michel-Claude

Great question.

My guess is that SQL uses intermediate results, translating the fields in
question as needed. DDS files need to match - is there a keyword for CCSID
at field level - I forget.

Of course, a multi-format logical is not the same as JOIN in SQL - the
logical is more like an SQL UNION. My assumption fits the UNION scenario
better.

Regards

Vern

At 10:56 AM 8/5/2003 +0200, you wrote:
>We encountered the following problem:
>
>We try to create a logical file with multi-format (I know, it is not the
>best way). The first physical file referenced by the logical one, has a
>CCSID of 500, and the second one has a CCSID of 297. So the creation abort
>with a CPF or CPD error. That's normal. To be able to create the
>multi-format, we have to accord both physical file CCSID first. That's what
>we do now.
>
>My question is the following:
>SQL join request is able to work on two different CCSID physical file, if
>the join fields are identical. Why is it not possible with DDS multi
format?
>
>Michel-Claude Ducrest
>tel. +41 21 924 16 14
>fax +41 21 924 2882


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