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



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/
______________________________________________________________________


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.