- ‚pokazuje wszystkie pola z danego katalogu oprocz inherited
select
ufd.id, pa.parent,ufd.area, pa.name asFolder, ufd.label, ufd.alias, ufd.run_time_field, otFieldTypes.field_type,
case
when ufd.[type] = 254 then ‚ReferenceList’+ Convert(nvarchar(50), ufd.id)
else”
end as RefTable,
case
when ufd.[type] = 254 then ‚ReferenceList’+ Convert(nvarchar(50), ufd.id) + ‚.Request = ‚+ rtrim(pa.name) + ‚ Object’
else”
end as Description_Request,
case
when ufd.[type] = 254 then ‚ReferenceList’+ Convert(nvarchar(50), ufd.id) + ‚.Reference = ‚+ rtrim(ufd.label) + ‚ Object’
else ”
end as Description_Reference
from
UserFieldDef ufd left outer join ProblemArea pa on ufd.area = pa.id left outer join
(
select
id,area,
case
when [type] = 1 then ‚otUfBoolean’
when [type] = 2 then ‚otUfByte’
when [type] = 3 then ‚otUfInteger’
when [type] = 4 then ‚otUfLong’
when [type] = 5 then ‚otUfCurrency’
when [type] = 6 then ‚otUfSingle’
when [type] = 7 then ‚otUfDouble’
when [type] = 8 then ‚otUfDate’
when [type] = 10 then ‚otUfText’
when [type] = 12 then ‚otUfMemo’
when [type] = 50 then ‚otUfAutonumber’
when [type] = 51 then ‚otUfDropDown’
when [type] = 52 then ‚otUfStampedMemo’
when [type] = 53 then ‚otUfWorkflow’
when [type] = 54 then ‚otUfSchedule’
when [type] = 55 then ‚otUfAttachments’
when [type] = 250 then ‚otUfRefUser’
when [type] = 251 then ‚otUfRefCustomer’
when [type] = 252 then ‚otUfRefRequest’
when [type] = 253 then ‚OtUfRefInvItem’
when [type] = 254 then ‚OtUfRefList’
else
Cast([type] as nvarchar(10))
end as field_type
from UserFieldDef)
otFieldTypes on otFieldTypes.id = ufd.id and otFieldTypes.area = ufd.area
where ufd.area = (select id from ProblemArea where alias =’Incidents’) order by label
- ‚polaczenie – pole w katalogu z powyzszego zapytania a jego nazwa z tabeli UserFields_area
SELECT
[fieldname]
FROM [OT_Development_10.2].[dbo].[RunTimeFieldDef]
where id=300 ‚run_time_field z powyzszego zapytania
- ‚wszystkie requests sa tutaj z danego katalogu (trzeba patrzec nazwe kolumny z powyzszego zapytania)
SELECT *
FROM [OT_Development_10.2].[dbo].[UserFields1] ‚gdzie 1=to jest area z pierwszego zapytania
- ‚grzebanie po historii
SELECT TOP (1000) h.[id]
,[report]
,[change_date]
,user1.display_name as username
,[attribute]
,[user_field]
,u.alias
,[old_value]
,[new_value]
,[state]
,[format]
,[guest]
FROM [OT_Development_10.2].[dbo].[History] h,UserFieldDef u,User_ user1
where h.user_field=u.id
and user1.id=user_
and u.area in (1,38) ‚tutaj trzeba wziazc id z PRoblemArea – te dwa odp.ServiceDesk oraz Tickets
and report=603709 ‚by wydobyc te id trzeba select request from UserFields1
order by 3