cancel
Showing results for 
Search instead for 
Did you mean: 

Query for dispatch details for BP SO

Former Member
0 Kudos

Hi experts,

I am new to the forum and would appreciate your help. I am trying to make a report about the pending and dispatched material for BP. The qurey is -

SELECT T0.[DocNum], T0.[CardName], T0.[NumAtCard], T0.[TaxDate], T0.[DocDueDate], T1.[ItemCode], T1.[Quantity], T1.[OpenQty], T2.[DocNum], T2.[DocDate], T2.[ShipToCode], T2.[TrackNo] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry, OINV T2 INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry, OPOR T4 INNER JOIN POR1 T5 ON T4.DocEntry = T5.DocEntry, ODLN T6 INNER JOIN DLN1 T7 ON T6.DocEntry = T7.DocEntry WHERE T0.[CardName] = [%0] AND T3.[BaseRef] = T6.[DocNum] AND T7.[BaseRef] = T0.[DocNum] AND T1.[ItemCode] = T3.[ItemCode]

But i get an error message -

1). [Microsoft][SQL Native Client][SQL Server]The conversion of the nvarchar value '3700081929' overflowed an int column. Maximum integer value exceeded. 'User-Defined Values' (CSHS)

Pl help. Thanking you in advance.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi..........

Welcome to SAP Forum.......

Please try this....

SELECT Distinct T0.DocNum, T0.CardName, T0.NumAtCard, T0.TaxDate, T0.DocDueDate, T1.ItemCode, T1.Quantity, T1.OpenQty, T2.DocNum, T2.DocDate, T2.ShipToCode, T2.TrackNo FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry, OINV T2 INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry, OPOR T4 INNER JOIN POR1 T5 ON T4.DocEntry = T5.DocEntry, ODLN T6 INNER JOIN DLN1 T7 ON T6.DocEntry = T7.DocEntry WHERE T0.CardName = '[%0]' AND T3.BaseRef = T6.DocNum AND T7.BaseRef = T0.DocNum AND T1.ItemCode = T3.ItemCode

Above query wont give any error.

But from your Subject the query report seems give wrong result......

Regards,

Rahul

Former Member
0 Kudos

Thanks for your prompt reply. But the query is still giving the same error -

1). [Microsoft][SQL Native Client][SQL Server]The conversion of the nvarchar value '3700081929' overflowed an int column. Maximum integer value exceeded. 'Service Contracts' (OCTR)

Pl help as i am new to query writing and have no formal training of SQL.

Former Member
0 Kudos

Hi.....

Try this one whether it works or not.....

SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[NumAtCard], 
T1.[ItemCode], T1.[Dscription], T1.[Quantity], Sum(T2.Quantity)  As 'Total Rcvd', T1.[OpenQty] FROM ORDR T0  
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry LEFT Join DLN1 t2 On T1.DocEntry=T2.BaseEntry and T1.LineNum=T2.BaseLine 
Group By T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[NumAtCard], 
T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty]
Having (T1.[Quantity]>Sum(T2.Quantity)) or (T1.[Quantity]<Sum(T2.Quantity))

Regards,

Rahul

Former Member
0 Kudos

Thanks Rahul, your query gives a different result but i have solved the query by reducing the date range. Thanks again.