Error in SQLRPGLE program

[John192]

Hi,


I want to write an equivalent SQLRPGLE Program to generate a physical file and logic wise it should have same records generated in file3 after using file1 and file2 like below SQL query.
select * from file1 where fld1='ABCDEF' and fld2= (int(current date) - 19000000) and fld3 in (select fld7 from file2 where fld8='1') and fld4 in ('TYM','IOP')


where fld1,fld2,fld3,fld 4 are from file1 and fld7, fld8 are from file2.
Please Note:- fld2 is that specific numeric field having a numeric type and a length of 7 and which has a date field in it in CYY/MM/DD format ( as in the DDS of the file Edit word with slashes have been defined in it's definition part length 7 with packed data type ) in which we want to have all
the records for current system date.
so I tried to write this SQLRPGLE program ( in which some fields and files name are different but main logic to cover above SQL query criteria is same.) (attached snapshot of program and error when tried to call it)

So Any idea how to resolve this error ("The target for a numeric operation is too small to hold the result (C G D F ) when I am trying to call this SQLRPGLE program?


I tried to debug it as well and when trying to debug after F10 it's coming at statement number 7 'If sqlcod =0 ;

and when in debug mode i am checking it's value is 077952576. and then if i do f10 again then get this error 'receiver value too small to hold result.'

so please advise what corrective action should i take to resolve this error and get desired output from this program.



Thanks..

[Scott M]

why are you checking sqlcod before doing any sql operation? The error says line 76, what line is that, look at the compile listing.

[John192]

Thanks,

I commented that line and when I ran it failed displaying the error message coded inside the program that is 'some error'.
when i did wrkjob and then did F10 and then did shift + f6 then I found few messages like:-

1. y3 in t21 not valid for operation.
details:- reason code 3:- Y3 not journaled,no authority to the journal,or
the journal state is *STANDBY. Files with an RI constraint action of CASCADE,
SET NULL,or SET DEFAULT must be journaled to the same journal.

2. member (some name) not journaled to journal *N.


Detail:- Member some mmber name ile Y3 in library T21 is not journaled to journal *N in library *N
for commitment definition *DFTACTGRP.If *N is specified for the journal,the file is not journaled, If the ile is a
logical file, all based-on physical files may not be journaled to the same
journal as required by commitment control.

Recovery:- Ensure the file being opened is journaled.For logical
files,make sure that the based-on physical fles are journaled to the same
journal.


Technical Description :- the commitment definition identifier is X'5CC4C6E3C1C3E3C7D9D7'.


3. Buffer length longer than record for member some member name.

Details:-

Cause:-The file record length for member some member name file Y3 in library T21 is 592.
The buffer entry length is 603. The remainder of the buffer entry is filled with blanks.


Recovery:- If necessary,change the record length specified in the program to the length of the longest record in the member.
Then try your request again.



So out of these above mentioned errors in which sequence should I start working ..?

please advise steps to work to resolve these errors and to make this program generate desired output.

Please refer updated snapshots of this program attached here.

[John192]

Any idea, how to resolve it ?


Thanks much...

[John192]

Any idea, how to resolve it ?


Thanks much...

Hi, Could someone please advise on the same and please find this program as following:-

**FREE
DCL-PR R9 EXTPGM('R9');
END-PR;
DCL-PI R9;
END-PI;
DCL-S SQLString char(1000);
// If sqlcod = 0;
sqlstring = ' insert into t21/y3 '+
' select * from y1 a where A4AJCD=''ASVCIT'' and ' +
' A4CRTD=(int(CURRENT DATE)-19000000) and A4AKNB in ' +
' (select ARAKNB from y2 where AREBST= ''1'') and ' +
' A4B6CE in (''API'',''APC'') ';
exec sql prepare sqlrun2 from:SqlString;
exec sql Execute sqlrun2;
If SQLCOD <> 0;
dsply 'Some Error';
EndIf;
eval *inlr =*on;

Thanks...

[Vectorspace]

From the first part of the error message you copy/pasted:

1. y3 in t21 not valid for operation.
details:- reason code 3:- Y3 not journaled.

This is the most likely reason. The file you are inserting records to, Y3, is not journalled. The fact it is erroring means the program expects it to be journalled, which means it is running the SQL statement under commitment control.

So you have to journal the file, or set the SQL statement to not run under commitment control, or set the program compile option so that it does not default all SQL statements inside it to have commitment control.


I get the impression that you are learning RPGLE right now. Are you working through any kind of training or reference material that should be explaining all this to you? Because it seem to me that you are coming to this forum for every issue you come across.

[John192]

so how can i journal that file, or set the SQL statement to not run under commitment control, or set the program compile option so that it does not default all SQL statements inside it to have commitment control.

Thanks..

[Vectorspace]

Have you tried reading the documentation on IBM's website?

https://www.google.com/search?client=firefox-b-d&q=ibm+i+journalling+files

https://www.google.com/search?client=firefox-b-d&q=ibm+i+sqlrpgle+commitment+control

https://www.google.com/search?client=firefox-b-d&q=ibm+i+sqlrpgle+sql+commitment+control

I'm sorry, but none of us are here to write your programs for you, or to carefully instruct you in every step. We can point you in the right direction, but you have to work some things out for yourself if you want to actually learn.