Chuck you went above and beyond with this response. Thanks so much!
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Friday, May 03, 2013 2:41 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL Alter table: can I remove the UNIQUE attribute from a key?
Indeed that thread does not match exactly the stated requirements.
Sorry, but I had actually not even noticed the additional statement
about accomplishing the task "without naming each field", and so I had
answered only how to find the thread to which I had alluded as
describing the use of SQL to compare sets.
However dealing with the omission of column names is a relatively
trivial task with the SQL, because the catalogs have all the data
necessary to dynamically build a VIEW of the TABLE(s) which can then be
used for a comparison. As has been noted a few times over some several
past messages lamenting similarly. the lack of an ability to omit
field(s) rather than having to explicitly name all of the other columns,
the dynamic SQL can be used to generate a SELECT statement from the
metadata; metadata coming from any of the SQL catalogs, the *DBXREF on
which the SQL catalogs are based, DSPFFD to an output file, QDBRTVFD
API, et al. Admittedly, I do not recall specifically that anyone
actually ever had posted a routine to effect that... so I will here; I
had alluded as much as recently as 06-Mar-2013:
http://archive.midrange.com/midrange-l/201303/msg00178.html
FWiW: In another thread here, such a suggestion was made
http://archive.midrange.com/midrange-l/201212/msg01040.html
and that message made reference to this thread for the source
http://archive.midrange.com/midrange-l/201212/msg00186.html
except apparently Tom was unaware that a line starting with two
dashes followed by a blank and a CRLF terminates data copy into the
archives. However the message is available on the NewsGroup on the
NewsServer. But that routine uses short name and library as inputs and
uses DECLARE, OPEN, FETCH, CLOSE instead of a FOR procedural statement
which is even messier than what I have below. Oh... and I just found
that the shadow site that archives messages from here [but with nicer
formatting] also does not truncate at the conventional EOM marker for
/news/ messages:
http://permalink.gmane.org/gmane.comp.hardware.ibm.midrange/181169
I just wrote the following VIEW_ON procedure, earlier last night, and
I have done very little of either testing or validation since; re the
lack of validation, that includes what might be better parameter and
variable declarations [e.g. lengths and CCSID] and for any possible
trimming of values:
/* A script for RUNSQLSTM COMMIT(*NONE) NAMING(*SYS) ERRLVL(30) */
/* Improvements and defect corrections allow /cleaner/ code, */
/* but this is functional on v5r3 and should work going forward.*/
drop procedure rtvsn
;
set path *LIBL
;
create procedure rtvsn
( inout char(20)
, in char(128)
, in int
, in char(10)
, inout char( 8)
) /* See API QDBRTVSN */
language CL parameter style general
external name qdbrtvsn
;
drop procedure view_of
;
create procedure view_of /* not functional for TABLE files in QTEMP! */
( OverPF in varchar(128) /* CREATE VIEW /over/ this long PF name */
, OmitCol in varchar( 10) /* could be expanded; just one col for now */
) language sql specific view_of
not deterministic modifies sql data
set option dbgview=*source,commit=*none,srtseq=*hex,dynusrprf=*user
,datfmt=*iso
begin
declare CrtView varchar(10000) ;
declare SltList varchar(2000) default '' ;
declare NameOfPF char( 10) not null default '' ;
/* parms for QDBRTVSN invocation via RTVSN routine */
declare LibOfPF char( 10) not null default '*LIBL' ; /* reused! */
declare QualPF char( 20) not null default '' ;
declare NameLen int not null default 0 ;
declare ErrCde char( 8) for bit data not null
default x'0000000000000000';
set NameLen = length(rtrim(OverPF)) ;
call rtvsn ( QualPF, OverPF, NameLen, LibOfPF, ErrCde ) ;
set LibOfPF = substr(QualPF, 11, 10) ;
set NameofPF = substr(QualPF, 01, 10) ;
for VW as CrtViewOf cursor for
select sys_cname
from qsys2/syscolumns
where table_name = OverPF
and table_schema = LibOfPF
and sys_cname <> OmitCol
order by colno
do
set SltList = SltList concat sys_cname concat ',' ;
end for;
set SltList = strip(SltList, T, ',') ;
set CrtView = 'CREATE VIEW QTEMP/' concat OverPF
concat ' AS SELECT ' concat SltList
concat ' FROM ' concat LibOfPF /* or OverPF and snip rest */
concat ' / ' concat NameofPF ;
execute immediate CrtView ;
end
;
So given there is a SQL TABLE named REALLYLONGNAME in *LIBL with a
key field named KEYFIELD, if I want to create a VIEW named
QTEMP/REALLYLONGNAME which omits the column KEYFIELD, I just call the
VIEW_OF procedure created above. Then I can use that VIEW in a query;
e.g. a UNION [ALL] of two EXCEPT queries each issuing a SELECT * FROM
QTEMP/a_VIEW. After I am done, I can DROP VIEW QTEMP.REALLYLONGNAME or
let the end of job effect the delete of the VIEW file object. So for
example, I might perform the following actions:
call view_of ('REALLYLONGNAME', 'KEYFIELD')
;
select * from qtemp/reallylongname
;
drop view qtemp/reallylongname
;
Regards, Chuck
On 02 May 2013 12:50, Stone, Joel wrote:
OK but this thread doesn't state how to compare all columns while
ignoring the key and NOT naming each individual column.
Is this possible?
CRPence on Thursday, May 02, 2013 2:35 PM wrote:
FWiW the "SQL" is unlikely to have been mentioned by most people
even though they are offering an SQL statement. If the likely
statement is "SELECT", then use that token instead of "SQL".
Anyhow, one possible search:
http://www.google.com/search?q=comparing+union+except+2013+site%3Aarchive.midrange.com%2Fmidrange-l
The above search yielded two threads [plus this one; for obvious
reasons] from 2013. One had the "2 days ago" conspicuously noted
in the results:
http://archive.midrange.com/midrange-l/201304/msg01365.html
_i Re: Easy way to determine if two tables contain the same data i_
2 days ago ... EXCEPT select * from table2) UNION ALL (select *
from table2 ... On Thu, Mar 28, 2013 at 7:28 PM, Charles
Wilt<charles.wilt@xxxxxxxxx>wrote: D ... If you have a need to
compare the contents of two tables to determine if ...
On 02 May 2013 12:22, Stone, Joel wrote:
How can I find thread?
I searched
http://archive.midrange.com/midrange-l/index.htm
for "sql union except"
How can SQL compare all fields except the key field WITHOUT
naming each field?
CRPence on Thursday, May 02, 2013 2:00 PM wrote:
Why use CMPPFM when SQL can compare the data? The SQL can
easily ignore the /key/ column and compare all of the row data
as a SET; see a very recent message from a somewhat recent
thread about /comparing/ using the EXCEPT in a UNION.
midrange.com
