Child/Parent Relationship Query

[larryenet]

I'm using SAP BO 9.0

I use a query to view the BP Code child/parent relationships here:

SELECT T0.[ItemCode], T0.[CardCode], T0.[Substitute] FROM OSCN T0 WHERE T0.[CardCode] ='ENET-TD'

Result:

#	Item No.	BP Code	BP Catalog Number
1	0061003020-COR	ENET-TD	CWDM-SFP-1470-ENC	Y
2	0061003020-ENC	ENET-TD	CWDM-SFP-1470-ENC	Y
3	0061003021-COR	ENET-TD	CWDM-SFP-1490-ENC	Y
4	0061003021-ENC	ENET-TD	0061003021-ENC	Y

I need it to include more than one BP Code [CardCode]. The BP Codes [CardCode] I need included are ENET-TD, C000156, C000310, C000312.

I also need it to include all parts that don’t have any BP Codes [CardCode], Item Group, and Description

Here is an example of the query needed:

#	Item No.	Item Group	BP Code	BP Catalog Number	BP Code	BP Catalog Number	BP Code	BP Catalog Number	BP Code	BP Catalog Number	Description
1	0061003024-ENC	104	ENET-TD	1234567890-ENC	C000156	XJ4470	C000310	ECM-0061003024-ENC	C000312	ENE-0061--3024-ENC	1000BASE-CWDM SFP 1GE 2G FC 1550nm 80km DOM SMF LC Connector

Thank you in advance for your help.

[Former Member · Accepted solution]

Hi

Try this

SELECT T0.[ItemCode], T0.[ItemName], T3.[ItmsGrpNam], T1.[Substitute], T2.[CardCode], T2.[CardName] FROM OITM T0  INNER JOIN OSCN T1 ON T0.[ItemCode] = T1.[ItemCode] INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode] INNER JOIN OITB T3 ON T0.[ItmsGrpCod] = T3.[ItmsGrpCod] where T2.cardcode in ( 'ENET-TD', 'C000156', 'C000310', 'C000312')

[larryenet · Accepted solution]

Hello

I tried and this is the result:

[Former Member · Accepted solution]

Try this

SELECT T0.[ItemCode], T0.[ItemName], T3.[ItmsGrpNam], T1.[Substitute], T2.[CardCode], T2.[CardName] FROM OITM T0  INNER JOIN OSCN T1 ON T0.[ItemCode] = T1.[ItemCode] INNER JOIN OCRD T2 ON T1.[CardCode] = T2.[CardCode] INNER JOIN OITB T3 ON T0.[ItmsGrpCod] = T3.[ItmsGrpCod] where T2.cardcode in ( 'ENET-TD', 'C000156', 'C000310', 'C000312')

[larryenet · Accepted solution]

Thank you very much!! It worked!!

[larryenet · Accepted solution]

I also need the query to include all of the Items that do not have any BP Codes, so basically all of the items in our database. I would expect those fields to be blank, so I know I need to update them.

[Former Member · Accepted solution]

Use this

SELECT T0.[ItemCode], T0.[ItemName], T3.[ItmsGrpNam], T1.[Substitute], T2.[CardCode], T2.[CardName]

FROM OITM T0  left outer  JOIN OSCN T1 ON T0.[ItemCode] = T1.[ItemCode] left outer JOIN OCRD T2 ON T1.[CardCode] = T2.[CardCode] INNER JOIN OITB T3 ON T0.[ItmsGrpCod] = T3.[ItmsGrpCod]

Note: If you want to see only particular BP master then add this where T2.cardcode in ('Cardcode1','Cardcode2','Cardcode3',...........)

[larryenet · Accepted solution]

This is very good. It worked again!! You are awesome!!!