GlobalSearch im SDM mit mehr als 2 CIs

Welcome to the POB User Group Online Community! Forums PUG DACH GlobalSearch im SDM mit mehr als 2 CIs

Tagged: 

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #3435
    TK
    Member

    Wir verwenden im Case Management mehr als 2 CIs und möchten diese bei der globalen Suche entsprechend berücksichtigen. Da es eine Weile gedauert hat, bis wir den hierzu notwendigen Dataload zusammengebaut hatten, anbei unsere Lösung dazu. Vielleicht hilft sie dem ein oder anderen weiter.

    Dataload für die Vollindizierung (einmalig oder z.B. einmal im Monat) :
    SELECT distinct b.root_PK, c.id,
    ISNULL(NULLIF(+coalesce(c.id,”) + ‘ ‘
    +coalesce(c.description,”) + ‘ ‘
    +coalesce(c.reference,”) + ‘ ‘
    +coalesce(c.callback,”) + ‘ ‘
    +coalesce(cust1.description,”) + ‘ ‘
    +coalesce(cust2.description,”) + ‘ ‘
    +stuff((
    SELECT ‘ ‘ + cast(ci.description as varchar(max)), ‘ ‘ + cast(ci.serial_number as varchar(max)), ‘ ‘ + cast(ci.pc_name as varchar(max)), ‘ ‘ + cast(ci.id as varchar(max))
    FROM configuration_item ci
    left outer join Case_CI cix with (nolock) on cix.case_pk = c.case_pk
    WHERE ci.ci_pk = cix.ci_pk
    FOR XML PATH(”)
    ), 1, 2, ”), ”),
    +coalesce(c.id,”) + ‘ ‘
    +coalesce(c.description,”) + ‘ ‘
    +coalesce(c.reference,”) + ‘ ‘
    +coalesce(c.callback,”) + ‘ ‘
    +coalesce(cust1.description,”) + ‘ ‘
    +coalesce(cust2.description,”) + ‘ ‘
    +coalesce(ci1.description,”) + ‘ ‘
    +coalesce(ci1.serial_number,”) + ‘ ‘
    +coalesce(ci1.pc_name,”) + ‘ ‘
    +coalesce(ci1.id,”) + ‘ ‘ + ‘ ‘
    +coalesce(ci2.description,”) + ‘ ‘
    +coalesce(ci2.serial_number,”) + ‘ ‘
    +coalesce(ci2.pc_name,”) + ‘ ‘
    +coalesce(ci2.id,”))
    AS memo, ‘EntityData’ as memotype, last_modified_time
    FROM Case_Table c with (nolock)
    inner join root b with (nolock) on b.root_pk = c.case_pk
    left outer join Customer cust1 with (nolock) on cust1.customer_pk = c.customer_pk
    left outer join Customer cust2 with (nolock) on cust2.customer_pk = c.vip_customer_pk
    left outer join configuration_item ci1 with (nolock) on ci1.ci_pk = c.ci_pk
    left outer join configuration_item ci2 with (nolock) on ci2.ci_pk = c.ci2_pk
    where c.delete_date is null

    Lösung für den Dataload als Update der neuen Cases (z.B. stündlich)
    SELECT distinct b.root_PK, c.id,
    ISNULL(NULLIF(+coalesce(c.id,”) + ‘ ‘
    +coalesce(c.description,”) + ‘ ‘
    +coalesce(c.reference,”) + ‘ ‘
    +coalesce(c.callback,”) + ‘ ‘
    +coalesce(cust1.description,”) + ‘ ‘
    +coalesce(cust2.description,”) + ‘ ‘
    +stuff((
    SELECT ‘ ‘ + cast(ci.description as varchar(max)), ‘ ‘ + cast(ci.serial_number as varchar(max)), ‘ ‘ + cast(ci.pc_name as varchar(max)), ‘ ‘ + cast(ci.id as varchar(max))
    FROM configuration_item ci
    left outer join Case_CI cix with (nolock) on cix.case_pk = c.case_pk
    WHERE ci.ci_pk = cix.ci_pk
    FOR XML PATH(”)
    ), 1, 2, ”), ”),
    +coalesce(c.id,”) + ‘ ‘
    +coalesce(c.description,”) + ‘ ‘
    +coalesce(c.reference,”) + ‘ ‘
    +coalesce(c.callback,”) + ‘ ‘
    +coalesce(cust1.description,”) + ‘ ‘
    +coalesce(cust2.description,”) + ‘ ‘
    +coalesce(ci1.description,”) + ‘ ‘
    +coalesce(ci1.serial_number,”) + ‘ ‘
    +coalesce(ci1.pc_name,”) + ‘ ‘
    +coalesce(ci1.id,”) + ‘ ‘ + ‘ ‘
    +coalesce(ci2.description,”) + ‘ ‘
    +coalesce(ci2.serial_number,”) + ‘ ‘
    +coalesce(ci2.pc_name,”) + ‘ ‘
    +coalesce(ci2.id,”))
    AS memo, ‘EntityData’ as memotype, last_modified_time
    FROM Case_Table c with (nolock)
    inner join root b with (nolock) on b.root_pk = c.case_pk
    left outer join Customer cust1 with (nolock) on cust1.customer_pk = c.customer_pk
    left outer join Customer cust2 with (nolock) on cust2.customer_pk = c.vip_customer_pk
    left outer join configuration_item ci1 with (nolock) on ci1.ci_pk = c.ci_pk
    left outer join configuration_item ci2 with (nolock) on ci2.ci_pk = c.ci2_pk
    join history h with (nolock) on h.root_pk = c.case_pk AND h.create_datetime > getdate()-0.2125
    where c.delete_date is null
    and c.case_pk NOT IN (SELECT root_pk l from Pob_Lock)
    and c.case_pk NOT IN (SELECT root_pk sl from Pob_Soft_Lock)

    • This topic was modified 4 years, 10 months ago by TK.
    #3445
    ms
    Keymaster

    Hallo Tobias,

    vielen Dank für den guten Tipp. Das können sicher einige hier gebrauchen.

    Besonders möchte ich nochmal auf den Teil mit der XML Path Abfrage hinweisen.
    Das ist eine sehr praktische Technik um Abfrageergebnisse nicht untereinander Zeile für Zeile, sondern nebeneinander z.B. durch Komma getrennt in einer Zeile zu erhalten.

    Aber auch die Einbindung der Tabellen POB_LOCK und POB_SOFT_LOCK sind gut, damit die Datensätze nicht während der Bearbeitung durch einen anderen Benutzer überschrieben werden können. Das nämlich würde zu unschönen Meldungen bei dem Benutzer führen.

    Ich würde mich über weitere Tipps und Tricks von euch freuen!

    Viele Grüße aus München,
    Christian

Viewing 2 posts - 1 through 2 (of 2 total)
  • You must be logged in to reply to this topic.