cancel
Showing results for 
Search instead for 
Did you mean: 

Query BOM

Former Member
0 Kudos

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'

Accepted Solutions (0)

Answers (1)

Answers (1)

agustin_marcoscividanes
Active Contributor
0 Kudos

Hi

If you want to remove blank spaces you have to use TRIM function.

If you want to remove null rows, you have to use isnull(field, '').

Kind regards

Agustín Marcos Cividanes