Omnitracker – reports in different way

  • ‚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

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *