I apologize in advance for the long code. When creating a view, an error occurs when adding the "rating3" case (highlighted with several line breaks). Everything works without it. Otherwise, the script is fully working. What could be the reason for the error? Oracle throws an error in this line: CREATE OR REPLACE FORCE VIEW "EDUC"."PF_RATING" ("YEAR", "SEMESTER", "PERSONID", "UNITID", "WORKID", "EDITED_ITEMS_1", "EDITED_ITEMS_2", "ITEMS0", "ITEMS1", "ITEMS2", "RATING0", "RATING1", "RATING2","RATING3", "REJECT1", "REJECT2")
CREATE OR REPLACE FORCE VIEW "EDUC"."PF_RATING" ("YEAR", "SEMESTER", "PERSONID", "UNITID", "WORKID", "EDITED_ITEMS_1", "EDITED_ITEMS_2", "ITEMS0", "ITEMS1", "ITEMS2", "RATING0", "RATING1", "RATING2","RATING3", "REJECT1", "REJECT2") AS
SELECT r.YEAR, st.semester, r.personid, u.unitid, w.workid,
SUM(
CASE
WHEN ( a1.affirm1date IS NULL
AND NVL(A1.ERRORDATE,to_date('1.01.1000')) < A1.POSTEDDATE
AND NVL(A1.REJECT1DATE,to_date('1.01.1000')) < A1.POSTEDDATE
AND NVL(A1.REJECT2DATE,to_date('1.01.1000')) < A1.POSTEDDATE)
THEN NVL(r.item,0)
ELSE 0
END) edited_items_1,
SUM(
CASE
WHEN ( ( a1.affirm2date IS NULL
OR NVL(A1.affirm2date,to_date('1.01.1000')) < A1.POSTEDDATE)
AND a1.affirm1date IS NULL
AND NVL(A1.ERRORDATE,to_date('1.01.1000')) < A1.POSTEDDATE
AND NVL(A1.REJECT2DATE,to_date('1.01.1000')) < A1.POSTEDDATE
AND NVL(A1.REJECT1DATE,to_date('1.01.1000')) < A1.POSTEDDATE )
THEN NVL(r.item,0)
ELSE 0
END) edited_items_2,
SUM(
CASE
WHEN (a1.posteddate IS NOT NULL AND (a1.posteddate>a1.errordate OR a1.errordate IS NULL))
THEN NVL(r.item,0)
ELSE 0
END) items0,
SUM(
CASE
WHEN (a1.affirm2date IS NOT NULL AND ( a1.affirm2date>a1.reject2date OR a1.reject2date IS NULL ))
THEN NVL(r.item,0)
ELSE 0
END) items1,
SUM(
CASE
WHEN (a1.affirm1date IS NOT NULL AND ( a1.affirm1date >a1.reject1date OR a1.reject1date IS NULL))
THEN NVL(r.item,0)
ELSE 0
END) items2,
SUM(
CASE
WHEN (a1.posteddate IS NOT NULL AND ( a1.errordate IS NULL OR a1.posteddate >a1.errordate))
THEN DECODE(p.items,0,1,r.item/p.items)*( case when (r.year*10 st.semester <=20190) then p.price
when (r.year*10 st.semester >20190 and u.unitid!=15) then p.price
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=0) then p.price
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=1) then 1
end )
* (case when (r.year*10 st.semester <=20190) then 1
when (r.year*10 st.semester >20190 and u.unitid!=13 and u.unitid!=34 and u.unitid!=15 ) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)=0) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)>0 and evp.reward_code in(9,11)) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)>0 and evp.reward_code in(5,6,7,8)) then nvl(evp.student_games,0)/ed.games
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=0) then 1
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=1) then 0.1
else 1
end
)
ELSE 0
END) rating0,
SUM(
CASE
WHEN (a1.affirm2date IS NOT NULL
AND ( a1.reject2date IS NULL
OR a1.affirm2date >a1.reject2date ) )
THEN DECODE(p.items,0,1,r.item/p.items)*( case when (r.year*10 st.semester <=20190) then p.price
when (r.year*10 st.semester >20190 and u.unitid!=15) then p.price
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=0) then p.price
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=1) then 1
end )
*
(case when (r.year*10 st.semester <=20190) then 1
when (r.year*10 st.semester >20190 and u.unitid!=13 and u.unitid!=34 and u.unitid!=15 ) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)=0) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)>0 and evp.reward_code in(9,11)) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)>0 and evp.reward_code in(5,6,7,8)) then nvl(evp.student_games,0)/ed.games
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=0) then 1
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=1) then 0.1
else 1
end
)
ELSE 0
END) rating1,
SUM(
CASE
WHEN (U.UNITID = 17 OR U.UNITID=18 OR U.UNITID=22) AND (a1.posteddate IS NOT NULL AND (a1.errordate IS NULL OR a1.posteddate>a1.errordate))
THEN DECODE(P.ITEMS,0,1,R.ITEM/P.ITEMS)*P.PRICE
WHEN (a1.affirm1date IS NOT NULL AND ( a1.reject1date IS NULL OR a1.affirm1date>a1.reject1date))
THEN DECODE(p.items,0,1,r.item/p.items)*( case when (r.year*10 st.semester <=20190) then p.price
when (r.year*10 st.semester >20190 and u.unitid!=15) then p.price
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=0) then p.price
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=1) then 1
end )
*
(case when (r.year*10 st.semester <=20190) then 1
when (r.year*10 st.semester >20190 and u.unitid!=13 and u.unitid!=34 and u.unitid!=15 ) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)=0) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)>0 and evp.reward_code in(9,11)) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)>0 and evp.reward_code in(5,6,7,8)) then nvl(evp.student_games,0)/ed.games
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=0) then 1
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=1) then 0.1
else 1
end
)
ELSE 0
END) rating2,
SUM(
CASE
WHEN (U.UNITID = 17 OR U.UNITID=18 OR U.UNITID=22) AND (a1.posteddate IS NOT NULL AND (a1.errordate IS NULL OR a1.posteddate>a1.errordate))
THEN DECODE(P.ITEMS,0,1,R.ITEM/P.ITEMS)*P.PRICE
WHEN (a1.affirm1date IS NOT NULL AND ( a1.reject1date IS NULL OR a1.affirm1date>a1.reject1date))
THEN DECODE(p.items,0,1,r.item/p.items)*( case when (r.year*10 st.semester <=20190) then p.price
when (r.year*10 st.semester >20190 and u.unitid!=15) then p.price
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=0) then p.price
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=1) then 1
end )
*
(case when (r.year*10 st.semester <=20190) then 1
when (r.year*10 st.semester >20190 and u.unitid!=13 and u.unitid!=34 and u.unitid!=15 ) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)=0) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)>0 and evp.reward_code in(9,11)) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)>0 and evp.reward_code in(5,6,7,8)) then nvl(evp.student_games,0)
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=0) then 1
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=1) then 0.1
else 1
end
)
ELSE 0
END) rating3,
SUM(
CASE
WHEN (a1.reject1date IS NOT NULL AND (a1.affirm1date IS NULL OR a1.affirm1date<a1.reject1date) AND a1.posteddate<a1.reject1date AND (a1.errordate IS NULL OR a1.errordate < a1.reject1date))
THEN DECODE(p.items,0,1,r.item/p.items)*( case when (r.year*10 st.semester <=20190) then p.price
when (r.year*10 st.semester >20190 and u.unitid!=15) then p.price
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=0) then p.price
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=1) then 1
end )
*
(case when (r.year*10 st.semester <=20190) then 1
when (r.year*10 st.semester >20190 and u.unitid!=13 and u.unitid!=34 and u.unitid!=15 ) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)=0) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)>0 and evp.reward_code in(9,11)) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)>0 and evp.reward_code in(5,6,7,8)) then nvl(evp.student_games,0)/ed.games
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=0) then 1
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=1) then 0.1
else 1
end
)
ELSE 0
END) REJECT1,
SUM(
CASE
WHEN (a1.reject2date IS NOT NULL AND (A1.AFFIRM2DATE IS NULL OR A1.AFFIRM2DATE < A1.REJECT2DATE) AND a1.posteddate < a1.reject2date AND (a1.errordate IS NULL OR a1.errordate < a1.reject2date))
THEN DECODE(p.items,0,1,r.item/p.items)*( case when (r.year*10 st.semester <=20190) then p.price
when (r.year*10 st.semester >20190 and u.unitid!=15) then p.price
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=0) then p.price
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=1) then 1
end )
*
(case when (r.year*10 st.semester <=20190) then 1
when (r.year*10 st.semester >20190 and u.unitid!=13 and u.unitid!=34 and u.unitid!=15 ) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)=0) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)>0 and evp.reward_code in(9,11)) then 1
when (r.year*10 st.semester >20190 and (u.unitid=13 or u.unitid=34) and nvl(ed.games,0)>0 and evp.reward_code in(5,6,7,8)) then nvl(evp.student_games,0)/ed.games
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=0) then 1
when (r.year*10 st.semester >20190 and u.unitid=15 and nvl(ed.is_preparation,0)=1) then 0.1
else 1
end
)
ELSE 0
END) reject2
FROM pf_rating_items r
JOIN sw_works w ON r.workid=w.workid
JOIN sw_units u ON w.unitid=u.unitid
JOIN rb_semester_terms st ON r.item_date BETWEEN ST.BEGIN_STUDENT_RATING_DATE AND ST.END_STUDENT_RATING_DATE
JOIN sw_workprices p ON r.workid =p.workid AND r.year =p.year AND p.semester=st.semester
LEFT JOIN
(SELECT table_name, row_id, MAX(s_date) s_date
FROM sw_affirmation
GROUP BY table_name, row_id
) a ON r.table_name=a.table_name AND r.row_id=A.row_id
JOIN sw_terms swt ON SWT.year=R.year AND swt.semester=st.semester
JOIN SW_AFFIRMATION_LASTSTATUS A1 ON A1.TABLE_NAME=R.TABLE_NAME AND A1.ROW_ID=R.ROW_ID
left JOIN pf_events_persons evp ON EVP.ID=R.ROW_ID AND R.PERSONID=EVP.PERSONID
LEFT JOIN pf_events_DIRECTORY ed ON ed.eventid=evp.eventid
WHERE u.isteacher = 0
AND SWT.ISTEACHER =0
GROUP BY R.year, ST.SEMESTER, R.PERSONID, U.UNITID, W.WORKID
UNION ALL
SELECT ST.YEAR, ST.SEMESTER, SR.PERSONID, 14, 482, 0, 0, 1, 1, 1, sum(DECODE(P.ITEMS,0,1,1/P.ITEMS)*P.PRICE),
sum(DECODE(P.ITEMS,0,1,1/P.ITEMS)*P.PRICE), sum(DECODE(P.ITEMS,0,1,1/P.ITEMS)*P.PRICE), 0, 0
FROM DOP_LESSONS L
left JOIN DOP_THEME T ON T.THEMEID = L.THEMEID
JOIN DOP_JOURNAL J ON J.LESSONID = L.LESSONID
JOIN FC_STUDENTRECORDS SR ON SR.RECORDID = J.STUDENTID and sr.deleted=0
JOIN RB_SEMESTER_TERMS ST ON L.L_DATE BETWEEN ST.BEGIN_STUDENT_RATING_DATE AND ST.END_STUDENT_RATING_DATE
JOIN SW_WORKPRICES P ON P.WORKID= 482 AND P.YEAR = ST.YEAR AND P.SEMESTER=ST.SEMESTER
WHERE J.PRESENCE = 1 AND STUDENTRATINGYEAR(L.L_DATE) > 2016
AND (EXISTS (SELECT 1 FROM dop_application_form WHERE LESSONID = L.LESSONID AND STUDENTID=SR.RECORDID) or l.ACTIVITYTYPE in (3,4))
GROUP by ST.YEAR, ST.SEMESTER, SR.PERSONID
UNION ALL
SELECT ST.YEAR, ST.SEMESTER, SR.PERSONID, 14, 483, 0, 0, 1, 1, 1, sum(J.BALL), sum(J.BALL), sum(J.BALL), 0, 0
FROM DOP_LESSONS L
left JOIN DOP_THEME T ON T.THEMEID = L.THEMEID
JOIN DOP_JOURNAL J ON J.LESSONID = L.LESSONID
JOIN FC_STUDENTRECORDS SR ON SR.RECORDID = J.STUDENTID
JOIN RB_SEMESTER_TERMS ST ON L.L_DATE BETWEEN ST.BEGIN_STUDENT_RATING_DATE AND ST.END_STUDENT_RATING_DATE
WHERE J.BALL > 0 AND STUDENTRATINGYEAR(L.L_DATE) > 2016
AND (EXISTS (SELECT 1 FROM dop_application_form WHERE LESSONID = L.LESSONID AND STUDENTID=SR.RECORDID) or l.ACTIVITYTYPE in (3,4))
GROUP BY ST.YEAR, ST.SEMESTER, SR.PERSONID
UNION ALL
SELECT ST.YEAR, ST.SEMESTER, SR.PERSONID, 14, 483, 0, 0, 1, 1, 1, sum(TJ.TASK_BALL), sum(TJ.TASK_BALL), sum(TJ.TASK_BALL), 0, 0
FROM DOP_LESSONS L
left JOIN DOP_THEME T ON T.THEMEID = L.THEMEID
JOIN DOP_LESSONS_TASKS LT ON LT.LESSONID = L.LESSONID
JOIN DOP_TASK_JOURNAL TJ ON TJ.TASKID = LT.TASKID
JOIN FC_STUDENTRECORDS SR ON SR.RECORDID = TJ.STUDENTID
JOIN RB_SEMESTER_TERMS ST ON L.L_DATE BETWEEN ST.BEGIN_STUDENT_RATING_DATE AND ST.END_STUDENT_RATING_DATE
WHERE TJ.TASK_BALL > 0 AND STUDENTRATINGYEAR(L.L_DATE) > 2017
AND (EXISTS (SELECT 1 FROM dop_application_form WHERE LESSONID = L.LESSONID AND STUDENTID=SR.RECORDID) or l.ACTIVITYTYPE in (3,4))
GROUP BY ST.YEAR, ST.SEMESTER, SR.PERSONID
UNION ALL
SELECT ST.YEAR, ST.SEMESTER, SR.PERSONID, 14, 279, 0, 0, 1, 1, 1, sum(round(floor((D.END_TIME-D.BEGIN_TIME)/60) mod((D.END_TIME-D.BEGIN_TIME),60)/60)*P.PRICE),
sum(round(floor((D.END_TIME-D.BEGIN_TIME)/60) mod((D.END_TIME-D.BEGIN_TIME),60)/60)*P.PRICE), sum(round(floor((D.END_TIME-D.BEGIN_TIME)/60) mod((D.END_TIME-D.BEGIN_TIME),60)/60)*P.PRICE), 0, 0
FROM DOP_DUTY D
JOIN FC_STUDENTRECORDS SR ON SR.RECORDID = D.STUDENTID
JOIN RB_SEMESTER_TERMS ST ON D.DUTY_DATE BETWEEN ST.BEGIN_STUDENT_RATING_DATE AND ST.END_STUDENT_RATING_DATE
JOIN SW_WORKPRICES P ON P.WORKID= 279 AND P.YEAR = ST.YEAR AND P.SEMESTER=ST.SEMESTER
WHERE ST.YEAR*10 ST.SEMESTER > 20170 AND NVL(D.PRESENCE,0) = 1
GROUP BY ST.YEAR, ST.SEMESTER, SR.PERSONID
UNION ALL
select ST.YEAR, ST.SEMESTER, SR.PERSONID, 14, 1886, 0, 0, 1, 1, 1, sum(DECODE(P.ITEMS,0,1,1/P.ITEMS)*P.PRICE),
sum(DECODE(P.ITEMS,0,1,1/P.ITEMS)*P.PRICE), sum(DECODE(P.ITEMS,0,1,1/P.ITEMS)*P.PRICE), 0, 0
from (select ds.documentid, max(ds.status_date) last_status_date
from dop_document_status ds
group by ds.documentid
) ds
join dop_document_status s on s.DOCUMENTID=ds.documentid and s.STATUS_DATE=ds.last_status_date
join dop_documents d on d.documentid=ds.documentid
JOIN FC_STUDENTRECORDS SR ON SR.RECORDID = D.STUDENTID
JOIN RB_SEMESTER_TERMS ST ON d.end_date BETWEEN ST.BEGIN_STUDENT_RATING_DATE AND ST.END_STUDENT_RATING_DATE
JOIN SW_WORKPRICES P ON P.WORKID= 1886 AND P.YEAR = ST.YEAR AND P.SEMESTER=ST.SEMESTER
where s.STATUS_CODE=3
GROUP by ST.YEAR, ST.SEMESTER, SR.PERSONID
UNION ALL
/*документы юр.клиники*/
select ST.YEAR, ST.SEMESTER, SR.PERSONID, 14, decode(d.DOC_TYPE,1,1944,2,1945,3,1946,4,1947,5,1948,6,1949,7,1950,8,1951,9,1952,10,1953,11,1954,12,1955,13,1956,14,1957,15,1958,16,1959,17,1960) workid,
0, 0, 1, 1, 1, sum(DECODE(P.ITEMS,0,1,1/P.ITEMS)*P.PRICE),
sum(DECODE(P.ITEMS,0,1,1/P.ITEMS)*P.PRICE), sum(DECODE(P.ITEMS,0,1,1/P.ITEMS)*P.PRICE), 0, 0
from (select ds.documentid, max(ds.status_date) last_status_date
from dop_document_status_new ds
group by ds.documentid
) ds
join dop_document_status_new s on s.DOCUMENTID=ds.documentid and s.STATUS_DATE=ds.last_status_date
join dop_documents_new d on d.documentid=ds.documentid
join ad_users u on u.username=d.username and u.ISSTUDENT=1
left JOIN FC_STUDENTRECORDS SR ON SR.RECORDID = U.STUDENTID
left JOIN RB_SEMESTER_TERMS ST ON ds.last_status_date BETWEEN ST.BEGIN_STUDENT_RATING_DATE AND ST.END_STUDENT_RATING_DATE
JOIN SW_WORKPRICES P ON P.WORKID=decode(d.DOC_TYPE,1,1944,2,1945,3,1946,4,1947,5,1948,6,1949,7,1950,8,1951,9,1952,10,1953,11,1954,12,1955,13,1956,14,1957,15,1958,16,1959,17,1960)
AND P.YEAR = ST.YEAR AND P.SEMESTER=ST.SEMESTER
where s.STATUS_CODE=3
GROUP by ST.YEAR, ST.SEMESTER, SR.PERSONID, d.DOC_TYPE;
CodePudding user response：
The column list for the view definition and the first branch of the union in your query have 16 items.
The other branches like
UNION ALL
SELECT ST.YEAR, ST.SEMESTER, SR.PERSONID, 14, 482, 0, 0, 1, 1, 1, sum(DECODE(P.ITEMS,0,1,1/P.ITEMS)*P.PRICE),
sum(DECODE(P.ITEMS,0,1,1/P.ITEMS)*P.PRICE), sum(DECODE(P.ITEMS,0,1,1/P.ITEMS)*P.PRICE), 0, 0
FROM DOP_LESSONS L
...
only have 15. You've added the rating3 column so you need a corresponding calculation or dummy value in the same place - i.e. add an expression before the , 0, 0 on all the other branches.
That might not be the only issue, of course. You can query the user_errors or all_errors views to see the actual problem, filtering on your view name.
CodePudding user response：
Query is too complex to analyze it (especially as we don't have your tables), so:
when I copied your code into TOAD, it complained that the number of items in the alias-list and query does not match
that can - possibly - be easily fixed by removing column list from the first line and keep only
CREATE OR REPLACE FORCE VIEW "EDUC"."PF_RATING" ASpresuming that you used aliases throughout the code. If so, Oracle will automatically use these aliases while creating the view
if that's not "it", then run the
SELECTstatement alone. If it is correctly written, good - view should then be created with no problems. If not, fix errors first. As I said, I can't tell what might be wrong without additional information