× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.