On 18 Sep 2013 18:33, A Paul wrote:
<<SNIP>>
Like a prior recent SQL-related message, this message also seems to
have nothing to do with SQL programming in RPG as a host language.
People who know nothing of RPG, and thus who likely do not participate
here, probably do participate on the midrange-l where topics not
specific to the RPG would normally take place; e.g. topics on SQL, not
specific to any host language.
The SQL is not getting me the two records that do match as well.
Does anyone <ed: know> what I am missing?
Indeed what was given, seemed to show reported data from two files,
that when joined on equal-predicates across their consistent columns
would match according to those join predicates, thus producing a match
for every row; ¿although Kevin suggested otherwise? but I did not see
what he saw, unless possibly reviewing data from the original versus the
/latest/ data.?
There is sometimes no way however, for the reader to be sure that the
given text shown in the form of a /report/ [esp. formatted with tabs] is
what it *appears* to be; e.g. the /reports/ were formatted in a manner
that could deceive... such as the columns TYC which are 5-byte char but
each are only apparently 2-byte values with unknown padding and the
numeric columns have unknown editing for which negative indicators could
be suppressed, plus we can not know what files\members were accessed,
the column definitions were given for file MFILE2 but the report was
noted to be for the file MFILE1, and the given SELECT as join query
claimed to effect the incorrect report was obviously not used to produce
that report because there was a missing logical operator in the query so
it would have failed with a syntax error [i.e. unable to produce a
report] and besides that only half of the columns [of SELECT *] were
shown as the apparent report output, etc.
FWiW I find that presenting a problem with the DDL and necessary DML
to populate each TABLE to be beneficial to all [adding data shown as
INSERT of VALUES(literals)]. Anyone [given some properly formatted
statements] can just copy\paste or transfer the text of the statements
into an SQL statement processor and perform the entire test-case setup
with data, and then run the same statement that is noted to fail.
Having prepared and tested the issue that way, if that script does not
recreate for the person initially having the problem, then that effect
should be a clue that the problem may be getting presented incorrectly
or that whatever is thought to be the problem is instead something else.
To make it work am I supposed to change field definitions to match?
There should be no need for any changes. The SQL and database should
properly join the data, irrespective of the mismatched [but compatible]
data types. If the data is identical according to the casting of values
between the compared type+attributes for the compared columns, then
those equal predicates should determine they are a match.
For example, the following test-case script shows that the given
/apparent/ data and the [corrected] join should match all five of the
rows between the two files; similarly, the results of a UNION DISTINCT
and an EXCEPT query as additional tests:
<code>
set current schema QTEMP ;
drop table CFILE2 ;
create table CFILE2
( PSP VARCHAR ( 1 )
, ACCT CHARACTER ( 5 )
, BRCH CHARACTER ( 2 )
, TYC CHARACTER ( 5 )
, YRM CHARACTER ( 6 )
, POSITIVE NUMERIC ( 7 )
, NEGATIVE NUMERIC ( 7 )
)
;
drop table MFILE1 ;
create table MFILE1
( PSP CHARACTER ( 1 )
, ACCT CHARACTER ( 5 )
, BRCH CHARACTER ( 2 )
, TYC CHARACTER ( 5 )
, YRM CHARACTER ( 6 )
, POSITIVE DECIMAL (31 , 4)
, NEGATIVE DECIMAL (31 , 4)
)
;
insert into MFILE1 VALUES
( 'P', 'ABCPQ', '16', 'AB', '201312', 0.0000, 645.0000 )
,( 'P', 'ABCPQ', '16', 'PQ', '201312', 1920.0000, 0 )
,( 'P', 'ABCPQ', '16', 'RS', '201312', 0.0000, 192.0000 )
,( 'D', ' 234C', '16', '25', '201312', 144.0000, 270.0000 )
,( 'D', ' 234N', '16', '25', '201312', 149.0000, 277.0000 )
;
insert into CFILE2 VALUES
( 'P', 'ABCPQ', '16', 'AB', '201312', 0 , 645 )
,( 'P', 'ABCPQ', '16', 'PQ', '201312', 1920 , 0 )
,( 'P', 'ABCPQ', '16', 'RS', '201312', 0 , 192 )
,( 'D', ' 234C', '16', '25', '201312', 144 , 270 )
,( 'D', ' 234N', '16', '25', '201312', 149 , 277 )
;
drop table xfileJ ;
create table xfileJ as (
SELECT T1.*
FROM
( SELECT PSP, ACCT, BRCH, TYC, YRM, POSITIVE, NEGATIVE
FROM MFILE1 ) T1
INNER JOIN
( SELECT PSP, ACCT, BRCH, TYC, YRM, POSITIVE, NEGATIVE
FROM CFILE2 ) T2
ON T2.PSP = T1.PSP
AND T2.ACCT = T1.ACCT
AND T2.BRCH = T1.BRCH
AND T2.TYC = T1.TYC
AND T2.YRM = T1.YRM
AND T2.POSITIVE = T1.POSITIVE
AND T2.NEGATIVE = T1.NEGATIVE
) with data
;
call qsys/qcmdexc ('dspfd qtemp/xfileJ *mbrlist', 0000000027.00000)
; -- output shows that *five rows* was the result
drop table xfileU ;
create table xfileU as (
SELECT PSP, ACCT, BRCH, TYC, YRM, POSITIVE, NEGATIVE
FROM MFILE1
UNION DISTINCT
SELECT PSP, ACCT, BRCH, TYC, YRM, POSITIVE, NEGATIVE
FROM CFILE2
) with data
;
call qsys/qcmdexc ('dspfd qtemp/xfileU *mbrlist', 0000000027.00000)
; -- output shows that *five rows* was the result
drop table xfileE ;
create table xfileE as (
SELECT PSP, ACCT, BRCH, TYC, YRM, POSITIVE, NEGATIVE
FROM MFILE1
EXCEPT
SELECT PSP, ACCT, BRCH, TYC, YRM, POSITIVE, NEGATIVE
FROM CFILE2
) with data
;
call qsys/qcmdexc ('dspfd qtemp/xfileE *mbrlist', 0000000027.00000)
; -- output shows that *zero rows* was the result
set current schema DEFAULT ;
</code>
If the above script functions as described, such that all of the rows
are matched by the database\SQL query requests, then the original
problem that was presented is somewhat deceptive; i.e. what the
/reports/ imply is apparently not what is really the situation. Could
the values of TYC be padded differently in the unmatched rows? Could
the numeric values have a different sign between the files in the
unmatched rows? Could a file [or member] other than what was expected
or described to have been the source for the described data?
After the above script is run, those files remaining in QTEMP could
be used to perform similar comparative query requests to the original
files, in an attempt to determine if there is something [possibly
subtle] different about the original\existing data or files. Take care
to library-qualify the file names or at least beware\understand the
effects of the current schema in the scripted requests.
midrange.com
