How do you determine multi-level categories using HANA SQL

    I have written the following SQL logic to retrieve the category and aspect so as to get the category list presented in the Web UI.  The problem I have is that at the point of joining to table CRMD_SERV_SUBJECT the join results in multiple records and in many cases the records are identical except for their GUID, although in some cases I get records with different asp_ID and cat_ID values.  The logic used by the web UI is able to determine just one result where these multiple records exist, and I am wondering how it does this.

    Select admh.client
    , admh.guid
    , admh.object_id
    , admh.description
    , zadmh.statustxt
    , admh.process_type
    , subj.*

    from CRMD_ORDERADM_H as admh

    inner join CRMD_LINK as link
    on link.client = admh.client
    and link.guid_hi = admh.guid
    and link.objtype_hi = ’05’
    and link.objtype_set = ’29’

    inner join CRMD_SRV_OSSET as oset
    on oset.client = link.client
    and oset.guid_set = link.guid_set

    inner join CRMD_SRV_SUBJECT as subj
    on oset.client = subj.client
    and oset.guid = subj.guid_ref

    I have tried getting the subject record with the lowest sort number, and with the lowest effective date, but still end up with multiple records at the join of crmd_srv_subject

    My thoughts are that the additional records are corrupt data, but I still need to be able to code around this corrupt data to get the valid asp_ID and cat_ID for the record.

    Any help appreciated.

    Add Comment
    0 Answer(s)

    Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.