Re: Weird COMMIT <> *NONE required in query

Just at “with ur” at the end of the sql statement. And you will have
“uncommitted read” for this particular statement and you can keep the
commit *none if you like

Give it a try
fre. 16. aug. 2024 kl. 21.42 skrev Javier Sanchez <
javiersanchezbarquero@xxxxxxxxx>:

I am trying to run this query using iACS:

With x as (
*-- Split IFS File into Rows (at CRLF)* Select Ordinal_Position
as
RowKey, Element as RowInfo
from Table(SysTools.Split(*Get_Clob_From_File*(
'/myfolder/EmailReceived.csv'), x'0D25')) a
Where *Trim*(Element) > '|'),
y as (
*-- Split IFS File Rows into Columns (and remove leading/trailing
double quotes ")*
Select x.*, Ordinal_Position ColKey,
*Trim*(B '"' from Element) as ColInfo
from x cross join Table(SysTools.Split(RowInfo, '|')) a)

*-- Return the Result as Table*Select RowKey,
*Min*(Case When ColKey = *1* Then ColInfo End) as From,
*Min*(Case When ColKey = *2* Then ColInfo End) as To,
*Min*(Case When ColKey = *3* Then ColInfo End) as CC,
*Min*(Case When ColKey = *4* Then ColInfo End) as Reply,
*Min*(Case When ColKey = *5* Then ColInfo End) as Subject,
*Min*(Case When ColKey = *6* Then ColInfo End) as SendDate,
*Min*(Case When ColKey = *7* Then ColInfo End) as Content,
*Min*(Case When ColKey = *7* Then ColInfo End) as Body
From y
Where RowKey > *1*
*-- Remove header*Group By RowKey;

The .csv file has the pipe character as field separator, and the header
line is as follows:

From|To|CC|ReplyTo|Subject|SentDate|ContentType|Body

There is sample contents in the .csv that I won't need to show.
The thing is that I am getting this SQL error:

<error>
Message: [SQL0443] LOB and XML locators are not allowed with

COMMIT(*NONE).

Cause . . . . . : Either a trigger program, external procedure, or
external function detected and returned an error to SQL. If the error
occurred in a trigger program, the trigger was on table QDBSSUDF2 in

schema

QSYS. If the error occurred in an external procedure or function, the
external name is QDBSSUDF2 in schema QSYS. The associated text is LOB

and

XML locators are not allowed with COMMIT(*NONE).. If the error occurred

in

a trigger program, the associated text is the type of trigger program.

If

the error occurred in an external function, the associated text is the

text

of the error message returned from the external function. Recovery . .

. :

Refer to the joblog for more information regarding the detected error.
Correct the error and try the request again.
</error>

Can someone please explain why is this? I am not using updates or

deletes.

LOB and XML locators are not allowed with COMMIT(*NONE)????

What is that? FCS!

Thanks in advance.

JS
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.