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



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

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.