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



Hi,

I've the next SQL statements in an ILE/RPG program:

 insert into everpda/cabfra select * from everpda/cabfralb where (serifr
concat digits(nfrafr) concat digits(fechfr))  not in  (select (serifr concat
digits(nfrafr) concat digits(fechfr)) from everpda/cabfra where tipofr<>'F')


 insert into everpda/cabfra select * from everpda/cabfralc where (serifr
concat digits(nfrafr) concat digits(fechfr))  not in  (select (serifr concat
digits(nfrafr) concat digits(fechfr)) from everpda/cabfra where tipofr<>'F')



where CABFRALB and CABFRALC are two views generated by the next SQL
statements:

CREATE VIEW CABFRALB AS ( SELECT SUBSTR ( A.HCDOC , 4 , 1 ) AS SERIFR , CAST
( SUBSTR ( A.HCDOC , 5 , 6 ) AS DECIMAL ( 6 , 0 ) ) AS NFRAFR , B.ACNALB AS
ALBAFR , ( CASE WHEN A.HCTMV = 12 THEN 'C' WHEN A.HCTMV = 11 THEN 'A' END )
AS CRABFR , A.HCCCN AS CLIEFR , A.HCFCH AS FECHFR , A.HCBRUD AS BRUTFR ,
A.HCNETD AS NETOFR , A.HCNETD AS TOTAFR , 'D' AS TIPOFR FROM IQS60F002.ALMHC
A JOIN IQS60F002.ALMAC B ON ( A.HCCIN2 = B.ACCIN AND A.HCAÑO2 = B.ACAÑO )
WHERE DATE ( SUBSTRING ( DIGITS ( A.HCFCH ) , 7 , 2 ) CONCAT '.' CONCAT
SUBSTRING ( DIGITS( A.HCFCH ) , 5 , 2 ) CONCAT '.' CONCAT SUBSTRING ( DIGITS
( A.HCFCH ) , 1 , 4 ) ) >= ( CURRENT DATE - 90 DAYS ) AND A.HCCCN IN (
SELECT
 CLCDG FROM IQS60F002.CLNCL WHERE CLZON BETWEEN 01000 AND 13999 ) AND
A.HCTMV BETWEEN 11 AND 12 AND A.HCALM BETWEEN 600 AND 699 )

CREATE VIEW CABFRALC AS (SELECT SUBSTR(A.HCDOC, 4, 1) AS SERIFR,
CAST(SUBSTR(A.HCDOC, 5, 6) AS DECIMAL (6, 0)) AS NFRAFR, B.ACNALB AS ALBAFR,
(CASE WHEN A.HCTMV=78 THEN 'C' WHEN A.HCTMV=77 THEN 'A' END) AS CRABFR,
A.HCCCN AS CLIEFR, A.HCFCH AS FECHFR, A.HCBRUD AS BRUTFR, A.HCNETD AS
NETOFR, A.HCNETD AS TOTAFR, 'M' AS TIPOFR FROM IQS60F002.ALMHC A JOIN
IQS60F002.ALMAC B ON (A.HCCIN=B.ACCIN AND A.HCAÑO=B.ACAÑO) WHERE
DATE(SUBSTRING(DIGITS(A.HCFCH), 7, 2) CONCAT '.' CONCAT
SUBSTRING(DIGITS(A.HCFCH), 5, 2) CONCAT '.' CONCAT
SUBSTRING(DIGITS(A.HCFCH), 1, 4)) >= (CURRENT DATE - 90 DAYS) AND A.HCCCN IN
(SELECT CLCDG FROM IQS60F002.CLNCL WHERE CLZON BETWEEN 01000 AND 13999) AND
A.HCTMV BETWEEN 77 AND 78 AND A.HCALM = 803)

and CABFRA is a table generated  by

CREATE TABLE EVERPDA.CABFRA (   SERIFR CHAR(1) CCSID 284 NOT NULL DEFAULT ''
,       NFRAFR DECIMAL(6, 0) NOT NULL DEFAULT 0 , ALBAFR NUMERIC(9, 0) NOT
NULL DEFAULT 0 ,        CRABFR VARCHAR(1) CCSID 65535 DEFAULT NULL , CLIEFR
NUMERIC(10, 0) NOT NULL DEFAULT 0 , FECHFR NUMERIC(8, 0) NOT NULL DEFAULT 0
, BRUTFR DECIMAL(11, 2) NOT NULL DEFAULT 0 , NETOFR DECIMAL(11, 2) NOT NULL
DEFAULT 0 , TOTAFR DECIMAL(11, 2) NOT NULL DEFAULT 0 ,  TIPOFR VARCHAR(1)
CCSID 65535 NOT NULL DEFAULT '' )    

The first insert runs ok, but in the second one I get the following
messages:

              MCH3601 Pointer not set for location reference
              CPF5257 Failure for device or member CABFRALC file CABFRALC in
library EVERPDA
             CPF4273 Error occurred during creation of temporary result.
If I run both statements in interactive SQL I get the same results. When I
run only the SELECT statement it runs ok. If I run the same INSERT statement
inserting the result in other table created by CRTDUPOBJ from EVERPDA/CABFRA
it runs ok.

Any idea about this?

Thanks.

Juan Carlos

As an Amazon Associate we earn from qualifying purchases.

This thread ...


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.