Inventory Inquiry Query

[larryenet]

Hello SAP Gurus,

Can someone help me add a column to the query below?

I need to add U_TigerDirect but keep getting errors when trying to add it.

This is a User Defined Field in Item Master Data - General.

Delete from V33_SYNNEX_846

/*

CREATE TABLE V33_SYNNEX_846(

EDI nvarchar(10),

CardCode  nvarchar(20),

Blank1  nvarchar(10),

DocDate DateTime,

DocDueDate DateTime,

Blank2  nvarchar(10), 

TD_ItemCode nvarchar(20),

ENET_ItemCode nvarchar(20),

Blank3  nvarchar(10), 

Quantity numeric (19,6),

)

*/

Insert into V33_SYNNEX_846

SELECT

'846', 'C000310', '', GetDate(), GetDate(), '',

'',

ItemCode,

''

,CONVERT(int, ROUND(isnull(Qty,0),3)) Qty

FROM

(SELECT DISTINCT

  SUB.ItemCode

  ,w.WhsCode

  ,w.WhsName 

  ,SUB.Qty

FROM

(SELECT T0.ItemCode

  ,T1.WhsCode

  ,SUM (T1.OnHand-T1.IsCommited + T1.OnOrder) Qty

FROM OSCN T0

JOIN OITW T1 on T0.Substitute=T1.ItemCode

WHERE T1.WhsCode='01' AND T0.CardCode='ENET-TD'

GROUP BY T0.ItemCode, T1.WhsCode

) SUB

JOIN OWHS w on w.WhsCode=SUB.WhsCode

) FIN

ORDER BY WhsCode

--Update V33_TD_846 Set TD_ItemCode = U_TigerDirect from V33_TD_846

--Join OITM on V33_TD_846.ENET_ItemCode = ItemCode COLLATE SQL_Latin1_General_CP850_CI_AS

Insert into V33_SYNNEX_846

Select '846', 'C000310', '', GetDate(), GetDate(), '',

OSCN.Substitute , OSCN.ItemCode, '', (T1.OnHand-T1.IsCommited + T1.OnOrder) Qty from OSCN

-- Join OSCN o2 on OSCN.Substitute = o2.ItemCode

JOIN OITW T1 on OSCN.ItemCode =T1.ItemCode

where OSCN.CardCode = 'C000310' and WhsCode = '01' and  OSCN.ItemCode not in (Select ENET_ItemCode from V33_SYNNEX_846)

Update V33_SYNNEX_846 Set TD_ItemCode = Substitute from V33_SYNNEX_846

Join OSCN on V33_SYNNEX_846.ENET_ItemCode = OSCN.ItemCode COLLATE SQL_Latin1_General_CP850_CI_AS

Where OSCN.CardCode = 'C000310'

Update V33_SYNNEX_846 Set Quantity = FLOOR(ISNULL(T1.Quantity/4,0)) from V33_SYNNEX_846 T1

WHERE T1.ENET_ItemCode IN ('M-ASR1K-1001-8GB-ENA', 'MEM-ASR1002X-8GB-ENA',

     'S26361F3031L410E','S26361F3070L600E','S26361F3070L800E')

Update V33_SYNNEX_846 Set Quantity = FLOOR(ISNULL(T1.Quantity/2,0)) from V33_SYNNEX_846 T1

WHERE T1.ENET_ItemCode IN ('M-ASR1K-1001-8GB-ENA', 'MA940G/A-ENC','MEM-7835-I2-2GB-ENC',

     'S26361F2762L526E','S26361F2762L625E','S26361F2762L626E',

     'SSG-500-MEM-1GB-ENA','MEM-SUP2T-4GB-ENA','MEM-4400-4GU16G-ENC',

                                               'LC2-OM4-10M-ENT','LC2-OM4-3M-ENT','SCLC-OM4-3M-ENT')

Update V33_SYNNEX_846 Set Quantity = FLOOR(ISNULL(T1.Quantity/128,0)) from V33_SYNNEX_846 T1

WHERE T1.ENET_ItemCode IN ('XBR-000158-ENC')

Update V33_SYNNEX_846 Set Quantity = FLOOR(ISNULL(T1.Quantity/10,0)) from V33_SYNNEX_846 T1

WHERE T1.ENET_ItemCode IN ('GLC-SX-MM-10PK-ENC','GLC-T-10PK-ENC','J4859C-10PK-ENC','J4858C-10PK-ENC','GLC-T-10PK-COR','GLC-SX-MM-10PK-COR','J4859C-10PK-COR','J4858C-10PK-COR')

Select V33_SYNNEX_846.* from V33_SYNNEX_846

Join OITM T2 on V33_SYNNEX_846.ENET_ItemCode = T2.ItemCode

WHERE  SellItem = 'Y' and TD_ItemCode > ''

Thank you very much!!

Kind regards,

Larry T.

[Johan_H · Accepted solution]

Hi Larry,

In the final part of your query, in the SELECT clause:

...

Select V33_SYNNEX_846.* from V33_SYNNEX_846

Join OITM T2 on V33_SYNNEX_846.ENET_ItemCode = T2.ItemCode

WHERE  SellItem = 'Y' and TD_ItemCode > ''

you will have to write out all needed fields from the V33_SYNNEX_846 table by name, and add T2.U_TigerDirect where you want it

I cannot do it for you, because I don't know what fields there are in the V33_SYNNEX_846 table.

Regards,

Johan

[larryenet · Accepted solution]

Hi Johan,

Thank you very much!!

All I did was make this change:

Select V33_SYNNEX_846.*,

             T2.U_TigerDirect

from V33_SYNNEX_846

Join OITM T2 on V33_SYNNEX_846.ENET_ItemCode = T2.ItemCode

WHERE  SellItem = 'Y' and TD_ItemCode > ''

Kind regards,

Larry T.