I have a query which results as below for the query
select store_id,error_message from SVC_UPLD where store_id = 19258;
STOREID ERROR_MESSAGE
19258 Box Number is mandatory.,Quantity cannot have any special
characters.,BRAND is not a valid Brand in
MFCS.,RRP_CURRENCY is not a valid currency code in MFCS.,HSCODE
will be with Minimum as 8
chars,SEASON not a valid season.,Selling_Retail_curr is not a
valid currency code in MFCS.,GENDER
is not a valid Gender in MFCS.,COLOR is invalid.
I have tried using the below query
select regexp_substr (
error_message,
'[^,]+',
1,
level
) value
from SVC_UPLD
connect by level <=
length ( error_message ) - length ( replace ( error_message, ',' ) ) + 1;
** Getting output as below **
STOREID ERROR_MESSAGE
19258 Quantity cannot have any special characters.
19258 BRAND is not a valid Brand in MFCS.
19258 RRP_CURRENCY is not a valid currency code in MFCS.
19258 HSCODE will be with Minimum as 8 chars
19258 SEASON not a valid season.
19258 Selling_Retail_curr is not a valid currency code in MFCS.
19258 GENDER is not a valid Gender in MFCS.
19258 COLOR is invalid.
19258 ALLOCATION SIZE is mandatory.
** I need output as to show the error message in same grid cell as next next line **
STOREID ERROR_MESSAGE
19258 Box Number is mandatory.,
Quantity cannot have any special characters.,
BRAND is not a valid Brand in MFCS.,
RRP_CURRENCY is not a valid currency code in MFCS.,
HSCODE will be with Minimum as 8 chars,
SEASON not a valid season.,
Selling_Retail_curr is not a valid currency code in MFCS.,
GENDER is not a valid Gender in MFCS.,
COLOR is invalid.
``
I think you mean to break on the character ".," but keep all data in the same row. Here is one option for Interactive Report and Interactive Grid
Use html break tag in the query:
SELECT storeid, REPLACE(error_message,'.,','<br>') as error_message FROM SVC_UPLD;
Then for the column "error_message" set attribute "Escape special characters" to "No"
Use a line feed and format using pre
tag.
SELECT storeid, REPLACE(error_message,'.,',CHR(13)||CHR(10)) as error_message FROM SVC_UPLD;
Then in column error_message > Column Initialization JavaScript Function set
function(config) {
config.defaultGridColumnOptions = {
cellTemplate: '<pre>&ERROR_MESSAGE.</pre>'
};
return config;
}