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.
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.