How many records do you get with the following:
select B.PSTRH, count(*) from
DEVLIB/BOOKF B
WHERE B.PSTRH IN('9K','8U')
GROUP BY B.PSTRH
And
select A.PSTRH, count(*) from
DEVLIB/MASTERF A
WHERE A.PIOTY='S' AND A.PSTRH
IN('9K','8U')
GROUP BY A.PSTRH
Your result of your final query will have something close to 673*673 records.
It will actually be (CountofMASTERF.PSTRH='9K')*( CountofBOOKF.PSTRH='9K')+ (CountofMASTERF.PSTRH='8U')*( CountofBOOKF.PSTRH='8U')
You need some more exclusive Join criteria.
-Tom Stieger
IT Manager
California Fine Wire
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of A Paul
Sent: Tuesday, January 15, 2013 1:13 PM
To: Midrange Systems Technical Discussion
Subject: left join : SQL
select count(*) from
DEVLIB/MASTERF A
WHERE A.PIOTY='S' AND A.PSTRH
IN('9K','8U')
gets me 673 records.
-----------
dspfd devlib/BOOKf--> 5822443
dspfd devlib/MASTERF--> 1940039
--------
Ideally
the below query should get me 673 records, but SQL keeps running for several minutes and fetch me unlimited number of records.
SELECT A.*,B.PSTRH,B.QNTY,B.VATAMT
FROM DEVLIB/MASTERF A left join DEVLIB/BOOKF B ON A.PSTRH = B.PSTRH WHERE A.PIOTY='S' AND A.PSTRH in('9K','8U') does anyone see anything wrong here?
From: "Stone, Joel" <Joel.Stone@xxxxxxxxxx>
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Sent: Tuesday, 15 January 2013, 20:07
Subject: RE: SQL help - Data base conversion mapping error. 0 fields in error.
This particular file definition pre-dates me at this company by about 20 years :)
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, January 15, 2013 1:17 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL help - Data base conversion mapping error. 0 fields in error.
Still using DDS to define files, aren't you?
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com/
From: "Stone, Joel" <Joel.Stone@xxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 01/15/2013 01:38 PM
Subject: RE: SQL help - Data base conversion mapping error. 0
fields in error.
Sent by: midrange-l-bounces@xxxxxxxxxxxx
The field did have hex '40's in it, as seen with DBU. It is an externally
described file. It had blanks in a numeric field. Maybe thru CPYF
*NOCHK??
The fields are not null capable.
I used DBU to change the spaces to zeros in that one field and all is OK
now!
Thanks
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [
mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Tuesday, January 15, 2013 12:34 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL help - Data base conversion mapping error. 0 fields in
error.
I think that would be a different problem. A different reason code.
Blanks are not the same as NULL, right? There might be what look like
blanks when you do a DSPPFM, but they are meaningless, if the field is
NULL.
I think we need to see more - maybe the statement, maybe a screenshot of
how you saw blanks in a numeric field, which would be a decimal data
error, unless the field is an integer.
This is getting confusing!!
Vern
On 1/15/2013 10:36 AM, Stone, Joel wrote:
Oops - I looked closer in that RRN and there IS a numeric field
containing blanks.
Is it possible to have SQL ignore this problem and copy the row with the
"bad data" ?
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [
mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Tuesday, January 15, 2013 9:43 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL help - Data base conversion mapping error. 0 fields in
error.
Can anyone help with the following error?
This has worked well for a long time. Today it blew.
I looked at RRN 7348969 in the input file and didn't notice anything
askew.
Thanks
Additional Message Information
Message ID . . . . . . : MCH1801
Date sent . . . . . . : 13/01/15 Time sent . . . . . . :
09:33:57
Message . . . . : Data base conversion mapping error. 0 fields in
error.
Cause . . . . . : The data space number is 1. Ordinal entry number
is
7348969, and the incorrect fields are
X'80000008000000000000103C00010001007022E90000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000'.
Next msg in job log:
Additional Message Information
Message ID . . . . . . : CPF2479
Date sent . . . . . . : 13/01/15 Time sent . . . . . . :
09:33:57
Message . . . . : Call stack entry not found.
Cause . . . . . : Call stack entry QQQJOINI, specified for the send,
receive, move or delete message operation, could not be found in the
call
stack.
Recovery . . . : Change the call stack entry name or be sure the
specified
entry is in the call stack when doing the requested operation.
Next msg:
Additional Message Information
Message ID . . . . . . : CPF5035
Date sent . . . . . . : 13/01/15 Time sent . . . . . . :
09:33:58
Message . . . . : Data mapping error on member *QUERY0001.
Cause . . . . . : A data mapping error occurred on field C010040052
in
record number 0, record format *QUERYHASH, member number 1, in
member
*QUERY0001 file *QUERY0001 in library QTEMP, because of error code
20. The
error codes and their meanings follow:
1 -- There is data in a decimal field that is not valid.
2 -- A significant digit was truncated.
3 -- A floating point value exceeded the maximum representable
value.
4 -- A floating point value was less than the minimum
representable value.
5 -- A floating point value that is not a number could not be
mapped from
double to single precision or from single to double.
6 -- A floating point value could not be mapped to packed decimal,
zoned
______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com/
______________________________________________________________________
