must specify table to select from error (works in MSSM but not in SAP B1)

[Former Member]

Hi all, below is a query that I managed to execute in MSSM but not SQL. Once i executed the query it said must specify table to select from

SELECT day(T2.DocDate) as [day] ,CASE MONTH(T2.DocDate) WHEN 1 THEN 'January' WHEN 2 THEN 'February' WHEN 3 THEN 'March' WHEN 4 THEN 'April' WHEN 5 THEN 'May' WHEN 6 THEN 'June' WHEN 7 THEN 'July' WHEN 8 THEN 'August' WHEN 9 THEN 'September' WHEN 10 THEN 'October' WHEN 11 THEN 'November' WHEN 12 THEN 'December' END as [Month], Year(T2.DocDate) as [Year],T2.DocDate,T0.DocNum,T3.U_BaseRef as PO_No, (select DocDueDate from OPOR where DocNum = T3.U_BaseRef), T0.CardName,T3.U_mill, T2.ItemCode, T4.U_commodity, T4.U_spec, T4.U_thichness, T4.U_width, T1.Dscription,T2.BatchNum,t3.u_coilno as CoilID,T2.Quantity as Weight, T3.U_quantity, T3.U_UoM, ROUND(T3.U_price,2) AS Costing, (T2.Quantity*T3.U_price) as Amount, T3.U_ActualWgt, T3.U_customer,T3.U_GRADE,T3.Located,T1.WhsCode, T5.DocEntry as ReturnedRef, T6.Quantity as ReturnedQty, str(T2.Quantity-T6.Quantity,12,5) as RecQtyAfterReturned, (T2.Quantity-T6.Quantity)*T3.U_price as ActAmount FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN IBT1 T2 ON T2.WhsCode = T1.WhsCode and T2.ItemCode = T1.ItemCode and T2.DocDate = T0.DocDate and T2.BaseNum = T0.DocNum INNER JOIN OIBT T3 ON T2.BatchNum = T3.BatchNum and T3.ItemCode = T2.ItemCode and T3.WhsCode = T2.WhsCode INNER JOIN OITM T4 ON T4.ItemCode = T3.ItemCode LEFT JOIN RPD1 T5 ON T5.BaseRef=T0.Docnum and T5.BaseLine=T1.LineNum LEFT JOIN IBT1 T6 ON T6.BaseEntry=T5.DocEntry AND T6.BatchNum=T2.BatchNum AND T6.BaseLinNum=T5.LineNum AND T6.BaseType=21 --left join OPOR T7 on T7.Docentry = t1.BaseEntry WHERE T2.BsDocType ='22' and (T0.DocNum >= [%0] and T0.DocNum <=[%1] or [%0] ='' and [%1] = '') and (T0.DocDate >=[%2] and T0.DocDate <= [%3] or [%2] = '' and [%3] = '') and (T3.BatchNum = [%11] or [%11] = '' ) and (T3.U_mill = [%4] or [%4] = '') and (T4.U_commodity = [%5] or [%5] = '') and (T4.U_spec = [%6] or [%6] = '') and (T4.U_thichness =[%8] or [%8] = '0' ) and (T4.U_width = [%7] or [%7] = '0') and (T4.U_length = [%9] or [%9] = '0') and (T3.Located = [%10] or [%10] = '') and (T1.WhsCode = [%12] or [%12] = '' ) and (T3.U_GRADE = [%13] or [%13] = '' ) and T0.Comments not like '%Cancel%' GROUP BY T2.ItemCode,T4.U_commodity, T4.U_spec, T4.U_thichness, T4.U_width, T2.BatchNum, T2.Quantity, T1.Dscription,T3.U_quantity, T0.DocNum,T3.U_UoM,T0.DocNum,T0.DocDate, T2.DocDate, T3.U_ActualWgt,T0.CardName, T3.U_Mill,T3.U_GRADE,T3.U_customer,T3.U_BaseRef,T3.Located,T1.WhsCode, T3.U_price, T5.DocEntry, T6.Quantity,t3.u_coilno

Did somebody know the error? or it's just SAP B1 limitation for sub-query?

[msundararaja_perumal]

Hello,

It's limitation of B1, declare the variables separately and pass them into the query.

Thanks.