RE: CCSID and SQL -- MIDRANGE-L

You should be able to do this with constraints.  For example, (it can be 
done with DDS and ADDPFCST outside of DDS), in the SQL world:

CREATE TABLE ROB/PARENT 
 (KEYFLD CHAR (1 ) NOT NULL WITH DEFAULT, 
  DESCRIPTION VARCHAR (32000 ) ALLOCATE(15) NOT NULL WITH DEFAULT)
 CONSTRAINT UNIQUE_KEYFLD PRIMARY KEY (KEYFLD))

CREATE TABLE ROB/CHILD 
 (KEYFLD CHAR ( 1) NOT NULL WITH DEFAULT, 
  AMOUNT DEC (7 , 2) NOT NULL WITH DEFAULT, 
 CONSTRAINT PARENT_KEY 
  FOREIGN KEY (KEYFLD) REFERENCES ROB/PARENT (KEYFLD)
  ON DELETE RESTRICT
  ON UPDATE RESTRICT) 

INSERT INTO ROB/PARENT VALUES('A', 'Hi there')

INSERT INTO ROB/CHILD VALUES('A', 2)

delete from rob/parent
 where keyfld='A' 
SQL0532 - Delete prevented by referential constraint PARENT_KEY in ROB.

Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 





"Ducrest,Michel-Claude,VEVEY,CT-IP/FS" <Michel-Claude.Ducrest@xxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
08/06/2003 01:09 AM
Please respond to Midrange Systems Technical Discussion
 
        To:     "'Midrange Systems Technical Discussion'" 
<midrange-l@xxxxxxxxxxxx>
        cc: 
        Fax to: 
        Subject:        RE: CCSID and SQL


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.