Skip to Content
0
Former Member
May 27, 2016 at 07:41 AM

Query BOM

24 Views

I would like to remove blanks ...can someone help me? Thanks .

Select

T3.Code,

T4.ItemName,

CASE T0.CHILDNUM WHEN 0 THEN T0.CODE END AS 'Mat1' ,

CASE T0.CHILDNUM WHEN 0 THEN T1.ITEMNAME END as 'Mat1',

CASE T0.CHILDNUM WHEN 0 THEN T0.Quantity end as 'Mat1',

CASE T0.CHILDNUM WHEN 1 THEN T0.CODE END AS 'Mat1',

CASE T0.CHILDNUM WHEN 1 THEN T1.ITEMNAME END as 'Mat1',

CASE T0.CHILDNUM WHEN 1 THEN T0.Quantity END as 'Mat1' ,

CASE T0.CHILDNUM WHEN 2 THEN T0.Code END AS 'Mat1',

CASE T0.CHILDNUM WHEN 2 THEN T1.ITEMNAME END as 'Mat1',

CASE T0.CHILDNUM WHEN 2 THEN T0.Quantity END as 'Mat1',

CASE T0.CHILDNUM WHEN 3 THEN T0.CODE END AS 'Mat1' ,

CASE T0.CHILDNUM WHEN 3 THEN T1.ITEMNAME END as 'Mat1',

CASE T0.CHILDNUM WHEN 3 THEN T0.Quantity end as 'Mat1',

CASE T0.CHILDNUM WHEN 4 THEN T0.CODE END AS 'Mat1',

CASE T0.CHILDNUM WHEN 4THEN T1.ITEMNAME END as 'Mat1',

CASE T0.CHILDNUM WHEN 4 THEN T0.Quantity END as 'Mat1' ,

CASE T0.CHILDNUM WHEN 5 THEN T0.Code END AS 'Mat1',

CASE T0.CHILDNUM WHEN 5 THEN T1.ITEMNAME END as 'Mat1',

CASE T0.CHILDNUM WHEN 5 THEN T0.Quantity END as 'Mat1',

CASE T0.CHILDNUM WHEN 6 THEN T0.CODE END AS 'Mat1' ,

CASE T0.CHILDNUM WHEN 6 THEN T1.ITEMNAME END as 'Mat1' ,

CASE T0.CHILDNUM WHEN 6 THEN T0.Quantity end as 'Mat1' ,

CASE T0.CHILDNUM WHEN 7 THEN T0.CODE END AS 'Mat1',

CASE T0.CHILDNUM WHEN 7 THEN T1.ITEMNAME END as 'Mat1',

CASE T0.CHILDNUM WHEN 7 THEN T0.Quantity END as 'Mat1',

CASE T0.CHILDNUM WHEN 8 THEN T0.Code END AS 'Mat1',

CASE T0.CHILDNUM WHEN 8 THEN T1.ITEMNAME END as 'Mat1',

CASE T0.CHILDNUM WHEN 8 THEN T0.Quantity END as 'Mat1'

FROM ITT1 T0

INNER JOIN OITM T1 ON T0.Code=T1.ITEMCODE

INNER JOIN OITB T2 ON T1.ItmsGrpCod=T2.ItmsGrpCod

INNER JOIN OITT T3 ON T0.Father = T3.Code

INNER JOIN OITM T4 ON T3.Code = T4.ItemCode

where T4.[Phantom] like 'Y'