1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
select *
from (select count(1) over(partition by st_user.name) as cnt,
sm_mnu_adm.mnu_user_type,
(SELECT DISTINCT st_user.name as user_fio,
st_branch_scope.descr ofice_name,
st_crew_scheduling_vw.description job_desription,
st_user.phone as user_tel,
st_user.email as user_mail,
st_crew_scheduling_vw.username as user_login,
st_mnu_group_user.mnu_group_Name as user_group,
st_mnu_group.mnu_group_des as user_group_description,
st_crew_scheduling_vw.job as user_job,
st_crew_scheduling_vw.profile as user_profile,
DECODE(sm_mnu_adm.mnu_user_type,
'G',
'Групповое меню',
'U',
'Персональное меню',
'A',
'Полное меню') as pers_menu,
DECODE(st_user.login_locked,
'Y',
'Блокировка',
'N',
'Нет блокировки') as user_login_locked
FROM st_user
LEFT JOIN st_crew_scheduling_vw
ON st_crew_scheduling_vw.job = st_user.job
LEFT JOIN st_mnu_group_user
ON st_mnu_group_user.job_name = st_crew_scheduling_vw.job
LEFT JOIN st_branch_scope
ON st_crew_scheduling_vw.division = st_branch_scope.scope
LEFT JOIN st_mnu_group
ON st_mnu_group.mnu_group_name =
st_mnu_group_user.mnu_group_name
LEFT JOIN sm_mnu_adm
ON st_mnu_group.mnu_group_name = sm_mnu_adm.mnu_user
OR st_crew_scheduling_vw.job = sm_mnu_adm.mnu_user)
-- FROM что-то
)
where cnt = 1
or sm_mnu_adm.mnu_user_type = 'U'
ORDER BY user_login_locked DESC, user_fio ASC
select count(1) over(partition by st_user.name) as cnt
из чего селект?
после этого:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
(SELECT DISTINCT st_user.name as user_fio,
st_branch_scope.descr ofice_name,
st_crew_scheduling_vw.description job_desription,
st_user.phone as user_tel,
st_user.email as user_mail,
st_crew_scheduling_vw.username as user_login,
st_mnu_group_user.mnu_group_Name as user_group,
st_mnu_group.mnu_group_des as user_group_description,
st_crew_scheduling_vw.job as user_job,
st_crew_scheduling_vw.profile as user_profile,
DECODE(sm_mnu_adm.mnu_user_type,
'G',
'Групповое меню',
'U',
'Персональное меню',
'A',
'Полное меню') as pers_menu,
DECODE(st_user.login_locked,
'Y',
'Блокировка',
'N',
'Нет блокировки') as user_login_locked
FROM st_user
LEFT JOIN st_crew_scheduling_vw
ON st_crew_scheduling_vw.job = st_user.job
LEFT JOIN st_mnu_group_user
ON st_mnu_group_user.job_name = st_crew_scheduling_vw.job
LEFT JOIN st_branch_scope
ON st_crew_scheduling_vw.division = st_branch_scope.scope
LEFT JOIN st_mnu_group
ON st_mnu_group.mnu_group_name =
st_mnu_group_user.mnu_group_name
LEFT JOIN sm_mnu_adm
ON st_mnu_group.mnu_group_name = sm_mnu_adm.mnu_user
OR st_crew_scheduling_vw.job = sm_mnu_adm.mnu_user)
должен быть from
DracoDierАнна, спасибо.
Может подскажете еще, где сейчас в синтаксисе запроса ошибка?
select *
from (select count(1) over(partition by st_user.name) as cnt, sm_mnu_adm.mnu_user_type,
(SELECT DISTINCT
st_user.name as user_fio,
st_branch_scope.descr ofice_name,
st_crew_scheduling_vw.description job_desription,
st_user.phone as user_tel,
st_user.email as user_mail,
st_crew_scheduling_vw.username as user_login,
st_mnu_group_user.mnu_group_Name as user_group,
st_mnu_group.mnu_group_des as user_group_description,
st_crew_scheduling_vw.job as user_job,
st_crew_scheduling_vw.profile as user_profile,
DECODE(sm_mnu_adm.mnu_user_type, 'G', 'Групповое меню',
'U', 'Персональное меню',
'A', 'Полное меню') as pers_menu,
DECODE(st_user.login_locked, 'Y', 'Блокировка',
'N', 'Нет блокировки') as user_login_locked
FROM st_user
LEFT JOIN st_crew_scheduling_vw
ON st_crew_scheduling_vw.job = st_user.job
LEFT JOIN st_mnu_group_user
ON st_mnu_group_user.job_name = st_crew_scheduling_vw.job
LEFT JOIN st_branch_scope
ON st_crew_scheduling_vw.division = st_branch_scope.scope
LEFT JOIN st_mnu_group
ON st_mnu_group.mnu_group_name = st_mnu_group_user.mnu_group_name
LEFT JOIN sm_mnu_adm
ON st_mnu_group.mnu_group_name = sm_mnu_adm.mnu_user
OR st_crew_scheduling_vw.job = sm_mnu_adm.mnu_user
))
where cnt = 1 or sm_mnu_adm.mnu_user_type = 'U'
ORDER BY user_login_locked DESC, user_fio ASC
Ругается где скобки - [1]: ORA-00923: FROM keyword not found where expected
