sql server:create view WMS_TEAM_USER_VIEW
as
select
owner_id,
user_id,
en_first_name,
en_last_name,
en_full_name,
cn_name,
group_id,
group_desc,
parent_user_id,
parent_en_first_name,
parent_en_last_name,
parent_en_full_name,
parent_cn_name,
backup_user_id,
backup_en_first_name,
backup_en_last_name,
backup_en_full_name,
backup_cn_name,
email,
phone,
status,
role_id,
role_desc,
locale
from (
select
team_rel.PARENT as owner_id,
wms_user.USER_ID as user_id,
wms_user.EN_FIRST_NAME as en_first_name,
wms_user.EN_LAST_NAME as en_last_name,
/**decode(wms_user.EN_FIRST_NAME, null, null, wms_user.EN_LAST_NAME || ', ' || wms_user.EN_FIRST_NAME) as en_full_name,**/
case
when wms_user.EN_FIRST_NAME is null then null
else 'wms_user.EN_LAST_NAME' + ',' + 'wms_user.EN_FIRST_NAME'
end as en_full_name,
wms_user.CN_NAME as cn_name,
wms_user.GROUP_ID as group_id,
grp.en_name as group_desc,
parent.USER_ID as parent_user_id,
parent.EN_FIRST_NAME as parent_en_first_name,
parent.EN_LAST_NAME as parent_en_last_name,
/**decode(parent.EN_FIRST_NAME, null, null, parent.EN_LAST_NAME || ', ' || parent.EN_FIRST_NAME) as parent_en_full_name,**/
case
when parent.EN_FIRST_NAME is null then null
else 'parent.EN_LAST_NAME' + ','+ 'parent.EN_FIRST_NAME'
end as parent_en_full_name,
parent.CN_NAME as parent_cn_name,
back.USER_ID as backup_user_id,
back.EN_FIRST_NAME as backup_en_first_name,
back.EN_LAST_NAME as backup_en_last_name,
/**decode(back.EN_FIRST_NAME, null, null, back.EN_LAST_NAME || ', ' || back.EN_FIRST_NAME) as backup_en_full_name,**/
case
when back.EN_FIRST_NAME is null then null
else 'back.EN_LAST_NAME' + ',' + 'back.EN_FIRST_NAME'
end as backup_en_full_name,
back.CN_NAME as backup_cn_name,
wms_user.EMAIL as email,
wms_user.PHONE as phone,
wms_user.STATUS as status,
user_role.ROLE_ID as role_id,
role_desc.DESCRIPTION as role_desc,
locale.LOCALE as LOCALE,
/**decode(team_rel.PARENT, wms_user.USER_ID, 0, 1) as display_order**/
case
when team_rel.PARENT = 'wms_user.USER_ID' then 0
else 1
end as display_order
from
WMS_MST_LOCALE locale,
WMS_USER_ROLE user_role,
WMS_MST_ROLE_DESC role_desc,
WMS_MST_GROUP grp,
WMS_USER wms_user left outer
join WMS_USER parent on wms_user.PARENT_ID = parent.USER_ID
join WMS_USER back on wms_user.BACKUP_USER_ID = back.USER_ID
join WMS_TEAM_RELATION team_rel on wms_user.USER_ID = team_rel.CHILD
where
/** wms_user.USER_ID = team_rel.CHILD (+)
and wms_user.PARENT_ID = parent.USER_ID (+)
and wms_user.BACKUP_USER_ID = back.USER_ID (+)**/
wms_user.USER_ID = user_role.USER_ID
and user_role.ROLE_ID = role_desc.ROLE_ID
and role_desc.LOCALE = locale.LOCALE
and wms_user.GROUP_ID = grp.GROUP_ID
and wms_user.DELETE_FLAG <> 'Y'
as
select
owner_id,
user_id,
en_first_name,
en_last_name,
en_full_name,
cn_name,
group_id,
group_desc,
parent_user_id,
parent_en_first_name,
parent_en_last_name,
parent_en_full_name,
parent_cn_name,
backup_user_id,
backup_en_first_name,
backup_en_last_name,
backup_en_full_name,
backup_cn_name,
email,
phone,
status,
role_id,
role_desc,
locale
from (
select
team_rel.PARENT as owner_id,
wms_user.USER_ID as user_id,
wms_user.EN_FIRST_NAME as en_first_name,
wms_user.EN_LAST_NAME as en_last_name,
/**decode(wms_user.EN_FIRST_NAME, null, null, wms_user.EN_LAST_NAME || ', ' || wms_user.EN_FIRST_NAME) as en_full_name,**/
case
when wms_user.EN_FIRST_NAME is null then null
else 'wms_user.EN_LAST_NAME' + ',' + 'wms_user.EN_FIRST_NAME'
end as en_full_name,
wms_user.CN_NAME as cn_name,
wms_user.GROUP_ID as group_id,
grp.en_name as group_desc,
parent.USER_ID as parent_user_id,
parent.EN_FIRST_NAME as parent_en_first_name,
parent.EN_LAST_NAME as parent_en_last_name,
/**decode(parent.EN_FIRST_NAME, null, null, parent.EN_LAST_NAME || ', ' || parent.EN_FIRST_NAME) as parent_en_full_name,**/
case
when parent.EN_FIRST_NAME is null then null
else 'parent.EN_LAST_NAME' + ','+ 'parent.EN_FIRST_NAME'
end as parent_en_full_name,
parent.CN_NAME as parent_cn_name,
back.USER_ID as backup_user_id,
back.EN_FIRST_NAME as backup_en_first_name,
back.EN_LAST_NAME as backup_en_last_name,
/**decode(back.EN_FIRST_NAME, null, null, back.EN_LAST_NAME || ', ' || back.EN_FIRST_NAME) as backup_en_full_name,**/
case
when back.EN_FIRST_NAME is null then null
else 'back.EN_LAST_NAME' + ',' + 'back.EN_FIRST_NAME'
end as backup_en_full_name,
back.CN_NAME as backup_cn_name,
wms_user.EMAIL as email,
wms_user.PHONE as phone,
wms_user.STATUS as status,
user_role.ROLE_ID as role_id,
role_desc.DESCRIPTION as role_desc,
locale.LOCALE as LOCALE,
/**decode(team_rel.PARENT, wms_user.USER_ID, 0, 1) as display_order**/
case
when team_rel.PARENT = 'wms_user.USER_ID' then 0
else 1
end as display_order
from
WMS_MST_LOCALE locale,
WMS_USER_ROLE user_role,
WMS_MST_ROLE_DESC role_desc,
WMS_MST_GROUP grp,
WMS_USER wms_user left outer
join WMS_USER parent on wms_user.PARENT_ID = parent.USER_ID
join WMS_USER back on wms_user.BACKUP_USER_ID = back.USER_ID
join WMS_TEAM_RELATION team_rel on wms_user.USER_ID = team_rel.CHILD
where
/** wms_user.USER_ID = team_rel.CHILD (+)
and wms_user.PARENT_ID = parent.USER_ID (+)
and wms_user.BACKUP_USER_ID = back.USER_ID (+)**/
wms_user.USER_ID = user_role.USER_ID
and user_role.ROLE_ID = role_desc.ROLE_ID
and role_desc.LOCALE = locale.LOCALE
and wms_user.GROUP_ID = grp.GROUP_ID
and wms_user.DELETE_FLAG <> 'Y'
select
wms_user.BACKUP_USER_ID as owner_id,
child.USER_ID as user_id,
child.EN_FIRST_NAME as en_first_name,
child.EN_LAST_NAME as en_last_name,
/**decode(child.EN_FIRST_NAME, null, null, child.EN_LAST_NAME || ', ' || child.EN_FIRST_NAME) as en_full_name,**/
case
when child.EN_FIRST_NAME is null then null
else 'child.EN_LAST_NAME '+ ',' + 'child.EN_FIRST_NAME'
end as en_full_name,
child.CN_NAME as cn_name,
child.GROUP_ID as group_id,
grp.EN_NAME as group_desc,
parent.USER_ID as parent_user_id,
parent.EN_FIRST_NAME as parent_en_first_name,
parent.EN_LAST_NAME as parent_en_last_name,
/**decode(parent.EN_FIRST_NAME, null, null, parent.EN_LAST_NAME || ', ' || parent.EN_FIRST_NAME) as parent_en_full_name,**/
case
when parent.EN_FIRST_NAME is null then null
else 'parent.EN_LAST_NAME ' + ','+ 'parent.EN_FIRST_NAME'
end as parent_en_full_name,
parent.CN_NAME as parent_cn_name,
back.USER_ID as backup_user_id,
back.EN_FIRST_NAME as backup_en_first_name,
back.EN_LAST_NAME as backup_en_last_name,
/**decode(back.EN_FIRST_NAME, null, null, back.EN_LAST_NAME || ', ' || back.EN_FIRST_NAME) as backup_en_full_name,**/
case
when back.EN_FIRST_NAME is null then null
else 'back.EN_LAST_NAME' + ',' + ' back.EN_FIRST_NAME'
end as backup_en_full_name,
back.CN_NAME as backup_cn_name,
child.EMAIL as email,
child.PHONE as phone,
child.STATUS as status,
role.ROLE_ID as rold_id,
role_desc.DESCRIPTION as role_desc,
locale.LOCALE as locale,
/**decode(wms_user.BACKUP_USER_ID, wms_user.USER_ID, 0, 1) as display_order**/
case
when wms_user.BACKUP_USER_ID = 'wms_user.USER_ID' then 0
else 1
end as display_order
from
WMS_MST_GROUP grp,
WMS_MST_ROLE_DESC role_desc,
WMS_USER_ROLE role,
WMS_MST_LOCALE locale,
WMS_USER wms_user,
WMS_TEAM_RELATION rel,
WMS_USER child left outer
join WMS_USER parent on child.PARENT_ID = parent.USER_ID
join WMS_USER back on child.BACKUP_USER_ID = back.USER_ID
where
/**child.BACKUP_USER_ID = back.USER_ID (+)
child.PARENT_ID = parent.USER_ID (+)**/
wms_user.USER_ID = rel.PARENT
and wms_user.BACKUP_USER_ID is not null
and wms_user.BACKUP_USER_ID <> child.USER_ID
and child.USER_ID = rel.CHILD
and child.GROUP_ID = grp.GROUP_ID
and child.USER_ID = role.USER_ID
and child.DELETE_FLAG <> 'Y'
and role.ROLE_ID = role_desc.ROLE_ID
and role_desc.LOCALE = locale.LOCALE
and rel.PARENT <> rel.CHILD -- remove the backup user itself
union
select -- show those users do not have parent_id
null as owner_id,
wms_user.USER_ID as user_id,
wms_user.EN_FIRST_NAME as en_first_name,
wms_user.EN_LAST_NAME as en_last_name,
/**decode(wms_user.EN_FIRST_NAME, null, null, wms_user.EN_LAST_NAME || ', ' || wms_user.EN_FIRST_NAME) as en_full_name,**/
case
when wms_user.EN_FIRST_NAME is null then null
else 'wms_user.EN_LAST_NAME' +',' + 'wms_user.EN_FIRST_NAME'
end as en_full_name,
wms_user.CN_NAME as cn_name,
wms_user.GROUP_ID as group_id,
grp.en_name as group_desc,
null as parent_user_id,
null as parent_en_first_name,
null as parent_en_last_name,
null as parent_en_full_name,
null as parent_cn_name,
back.USER_ID as backup_user_id,
back.EN_FIRST_NAME as backup_en_first_name,
back.EN_LAST_NAME as backup_en_last_name,
/**decode(back.EN_FIRST_NAME, null, null, back.EN_LAST_NAME || ', ' || back.EN_FIRST_NAME) as backup_en_full_name,**/
case
when back.EN_FIRST_NAME is null then null
else 'back.EN_LAST_NAME' + ',' +'back.EN_FIRST_NAME'
end as backup_en_full_name,
back.CN_NAME as backup_cn_name,
wms_user.EMAIL as email,
wms_user.PHONE as phone,
wms_user.STATUS as status,
user_role.ROLE_ID as role_id,
role_desc.DESCRIPTION as role_desc,
locale.LOCALE as LOCALE,
1 as display_order
from
WMS_USER_ROLE user_role,
WMS_MST_ROLE_DESC role_desc,
WMS_MST_GROUP grp,
WMS_MST_LOCALE locale,
WMS_USER wms_user left outer
join WMS_USER back on wms_user.BACKUP_USER_ID = back.USER_ID
where
/**wms_user.BACKUP_USER_ID = back.USER_ID (+)**/
wms_user.PARENT_ID is null
and wms_user.USER_ID = user_role.USER_ID
and user_role.ROLE_ID = role_desc.ROLE_ID
and user_role.ROLE_ID <> 'BM'
and role_desc.LOCALE = locale.LOCALE
and wms_user.GROUP_ID = grp.GROUP_ID
and wms_user.DELETE_FLAG <> 'Y'
)as b
报的错误信息是?