Hello!
"I wonder if it’s possible to run a CTE in Novacura? It keeps giving an error about the application owner
"I have tried using IFS Applications, but the error still occurs in antoher way.
"ORA-00905: missing keyword
Failed executing query (ORA-00905: missing keyword)"
Missing application owner on: data
Missing application owner on: extracted_data
Missing application owner on: final_data
Missing application owner on: section_content
Missing application owner on: section_starts
Missing application owner on: sections
Missing application owner on: yellow_note
WITH yellow_note AS (
SELECT
TO_CHAR(‘YELLOW NOTE’) AS type,
TO_CHAR(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
sys.dbms_lob.substr(
REPLACE(
REPLACE(p.text, CHR(13), ‘’),
CHR(10), ‘’
),
2000,
1
),
‘^.?fs20 ', ‘’
),
'\lang.$’, ‘’
),
‘\f6’, ‘ö’),
‘\e5’, ‘å’),
‘\e4’, ‘ä’),
‘\par’, CHR(10))
) AS content,
TO_CHAR(p.modified_date, ‘YYYY-MM-DD HH24:MI:SS’) AS date_time,
TO_CHAR(p.modified_by) AS author
FROM ifsapp.FND_NOTE_BOOK fn
JOIN ifsapp.FND_NOTE_PAGE p ON fn.note_id = p.note_id
WHERE fn.key_ref = ‘WO_NO=4880470^’
),
data AS (
SELECT
sys.dbms_lob.Substr(c_work_log, 3840, 1) AS clob_data
FROM ifsapp.active_separate
WHERE wo_no = ‘4880470’
),
section_starts AS (
SELECT
LEVEL AS position,
REGEXP_SUBSTR(clob_data, ‘—[^-]±–’, 1, LEVEL) AS raw_section_header,
REGEXP_INSTR(clob_data, ‘—[^-]±–’, 1, LEVEL) AS start_pos
FROM ifsapp.data
CONNECT BY REGEXP_INSTR(clob_data, ‘—[^-]±–’, 1, LEVEL) > 0
),
sections AS (
SELECT
ss.position,
TRIM(REPLACE(REPLACE(raw_section_header, ‘-’, ‘’), ’ ', ’ ')) AS section_header,
ss.start_pos,
CASE
WHEN LEAD(ss.start_pos) OVER (ORDER BY ss.position) IS NULL THEN LENGTH(d.clob_data) + 1
ELSE LEAD(ss.start_pos) OVER (ORDER BY ss.position)
END AS end_pos
FROM ifsapp.section_starts ss, ifsapp.data d
),
section_content AS (
SELECT
s.position,
s.section_header,
TRIM(SUBSTR(d.clob_data,
s.start_pos + LENGTH(ss.raw_section_header),
s.end_pos - s.start_pos - LENGTH(ss.raw_section_header))) AS full_content
FROM ifsapp.sections s
JOIN ifsapp.data d ON 1=1
JOIN ifsapp.section_starts ss ON s.position = ss.position
),
extracted_data AS (
SELECT
UPPER(section_header) type,
CASE
WHEN INSTR(full_content, ‘|’) > 0
THEN TRIM(SUBSTR(full_content, 1, INSTR(full_content, ‘|’) - 1))
ELSE full_content
END AS content,
CASE
WHEN INSTR(full_content, ‘|’) > 0
THEN TRIM(SUBSTR(full_content, INSTR(full_content, ‘|’) + 1))
ELSE NULL
END AS author
FROM ifsapp.section_content
),
final_data AS (
SELECT
type,
CASE
WHEN REGEXP_LIKE(content, ‘\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}’)
THEN TRIM(SUBSTR(content, 1, LENGTH(content) - 20))
WHEN REGEXP_LIKE(content, ‘\d{4}-\d{2}-\d{2} \d{2}:\d{2}’)
THEN TRIM(SUBSTR(content, 1, LENGTH(content) - 16))
ELSE content
END AS content,
CASE
WHEN REGEXP_LIKE(content, ‘\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}’)
THEN TRIM(REGEXP_SUBSTR(content, ‘\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}’))
WHEN REGEXP_LIKE(content, ‘\d{4}-\d{2}-\d{2} \d{2}:\d{2}’)
THEN TRIM(REGEXP_SUBSTR(content, ‘\d{4}-\d{2}-\d{2} \d{2}:\d{2}’)) || ‘:00’
ELSE NULL
END AS date_time,
author
FROM ifsapp.extracted_data
UNION ALL
SELECT * FROM ifsapp.yellow_note
)
SELECT
type “type”,
content “content”,
date_time “dateTime”,
author “author”
FROM ifsapp.final_data
WHERE author IS NOT NULL
ORDER BY date_time DESC;