Join BUT000 & CRMD_ORDERADM_H

[Former Member]

hi Guys,

I want to join two tables BUT000 & CRMD_ORDERADM_H, to create INFOSETS.

How do i join these two tables in SQ02?

Thanks,

Krish

[Former Member]

Hi

crmd_orderadm_h-guid --> crmd_link-guid_hi

crmd_link-guid_set --> crmd_partner-guid

crmd_partner-partner_no --> but000-partner_guid

Regards

Gregory

[Former Member]

Hey,

Thanks for the quick response. i joined the tables with one change:

crmd_orderadm_h-guid --> crmd_link-guid_hi

crmd_link-guid_set --> crmd_partner-guid

crmd_partner-PARTNER_GUID--> but000-partner_guid

Now the Infoset is generated. But when i try to query the infoset the result is zero. I checke the table "crmd_orderadm_h" it has values.

Any idea/suggestion

Krish

[Former Member]

Hi

Be careful when you access to the table crmd_link and crmd_partner.

In my case. i needed to use these fields: crmd_link-objtype_hi, crmd_link-objtype_set and crmd_partner-partner_fct

Check my search:

clear crmd_link.

select single *

from crmd_link

where guid_hi eq it_crmd_orderadm_h-guid

and objtype_hi eq '05'

and objtype_set eq '07'.

if sy-subrc eq 0.

clear crmd_partner.

select single *

from crmd_partner

where guid eq crmd_link-guid_set

and partner_fct eq 'ZHR00015'.

if sy-subrc eq 0.

select single *

from but000

where partner_guid eq crmd_partner-partner_no.

if sy-subrc eq 0.

move but000-partner to it_salida-emp_res.

concatenate but000-name_first but000-name_last

into it_salida-texto_emp_res separated by space.

endif.

endif.

Regards

Gregory

[Former Member]

Hi Krish,

You need to join the tables in this manner only

crmd_orderadm_h-guid --> crmd_link-guid_hi

crmd_link-guid_set --> crmd_partner-guid

crmd_partner-partner_no --> but000-partner_guid

The change which you have made

crmd_partner-partner_guid --> but000-partner_guid

will not fetch you any record, as the partner_guid is just the primary key for the table crmd_partner has nothing to do with the partner_guid field in the table BUT000 table.

[Former Member]

Hi Guys,

Thanks for the reply. well, I cant Join like this -- crmd_partner-partner_no --> but000-partner_guid in SQ02. It says, Ilegal Join Conditions.

Any Idea/suggestion

Regards,

Krish

[Former Member]

Joining these two table doesn't make any Sense . CRMD_ORDERADM_H is Header data for CRM Business Transaction , which doesn't hold any info about the related Partners , So why you want to join the two.

[Former Member]

hey,

Thanks for suggestion. My requirement, to create a infoset in turn use the infoset to create Attribute list in Segment Builder.

Infoset should identify all business partners with transaction.

Any idea/suggestion

Krish

[former_member97292]

As Kiran stated:

crmd_orderadm_h-guid --> crmd_link-guid_hi

crmd_link-guid_set --> crmd_partner-guid

crmd_partner-partner_no --> but000-partner_guid

on the crmd_link-guid_set   use the one    with    crmd_link-objtype_set  =   '07' 

One may see these objtype_set values in     table     /SAPSRM/C_SETTY

[Former Member]

Hi,

Instead of joining two tables you can use CRMD_ORDER_INDEX table while you pass crm order header GUID and get the Partners involved in it.

Regards,

Abhishek R K

[Former Member]

Thanks very much to Joyca. We had exactly the same problem and was wondering why I could not join the 2 tables. But I was only able to link  - CRMD_Partner - ADDR-NR with BUT000 - PERSNUMBER.

[Former Member]

Hi,

If your objective is to find partners in the transaction , you will get all the partners in table <b>CRMD_PARTNER</b> .

pl. use following tables and joins,

1)Join object guid of Table <b>CRMD_ORDERADM_H</b> to <b>CRMD_LINK</b> Hi guid

2) take set_guid of <b>CRMD_LINK</b> and join it to <b>CRMD_PARTNER</b>

You will get all the partners in the transactions ( object ID ) number.

regards,

PD

Reward points if it helps !!!

[Former Member]

Hi Dusane

The table CRMD_PARTNER doesnt carry the partner names....it only carries the partner number..so there should be another join to table but000 to give the names.Joining the two is a problem am also going through...any work around??

[faisal_pc]

Hi Joel,

You can take the CRMD_PARTNER's partner number and get the partner names from BUT000.

Thanks,

Faisal

[Former Member]

Thanks Faisal,

but crmd_partner-partner_no --> but000-partner_guid in SQ02 join in the infoset says, Ilegal Join Conditions.....

how do i connect the two fields??? the data types for the fields are different but the two have the relevant data...

Will appreciate

[faisal_pc]

Hi Joel,

Why are you connecting the partner number to guid?. Either connect partner number to partner number field of but000 or connect guid to guid of the BUT00.

Thanks,

Faisal

[Former Member]

Hi Faisal,

The only relevant join for this case is crmd_partner-partner_no --> but000-partner_guid.....there should be a work around....

[faisal_pc]

Hi Joel,

I was wondering why the term 'workaround' is coming into picture when we have a straight way. If you go to CRMD_PARTNER table, the field PARTNER_GUID is of type CRMT_PARTNER_GUID. This guid you can pass to BUT000-PARTNER_GUID which is of type BU_PARTNER_GUID and both are of length 16. Why you insist to compare partner number with partner guid?. I don't understand. Again...it's not a work around. A straight way we usually do.

Thanks,

Faisal

[vervinckt_joyca]

It is quite clear why Joel insists to compare the partner number with the partner guid...

Because these are the fields that match, content-wise.

The content of CRMD_PARTNER-PARTNER_GUID and BUT000-PARTNER_GUID do not contain the same values, they will never match! See screenshot below.

I was having the same issue with illegal join conditions in SQVI on PARTNER_NO and PARTNER_GUID. However by making the below screenshot, I noticed that there is another field that matches AND allows to be joined upon!

ADDR_NP from CRMD_PARTNER can be linked to PERSNUMBER in BUT000. Now my quickview works.

I hope this info can still help someone

[faisal_pc]

Hi Joyca,

Yes. My mistake. Content wise they are the right fields.

Joel Rotich:Sorry for misunderstanding your question.

Thanks,

Faisal

[Former Member]

Thanks Joyca and Faisal. If i can remember, i had to use a longer route of extracting data from CRM using data services to a staging area then applied the joins. This worked for my situation..

Regards