Skip to Content
0
Former Member
Aug 04, 2010 at 06:41 AM

Sales Query

15 Views

Hello Experts,

I have missing lines from INV1 in this qeury...

why?..

 SELECT	AA.[DocType] AS N'u05E1u05D5u05D2 u05DEu05E1u05DEu05DA', 
	AA.[DocNum] AS N'u05DEu05E1u05E4u05E8 u05DEu05E1u05DEu05DA', 
	AA.[DocDate] AS N'u05EAu05D0u05E8u05D9u05DA u05D0u05E1u05DEu05DBu05EAu05D0', 
	AA.[CardCode] AS N'u05E7u05D5u05D3 u05DCu05E7u05D5u05D7', 
	AA.[CardName] AS N'u05E9u05DD u05DCu05E7u05D5u05D7', 
	AA.GroupName AS 'u05E7u05D1u05D5u05E6u05EA u05D4u05E9u05EAu05D9u05D9u05DBu05D5u05EA',
	AA.MailAddres, AA.MailBlock, AA.MailCity, AA.MailZipCod, AA.MailCounty, AA.State2,AA.MailCountr,
	--T5.TaxCode AS 'u05E7u05D5u05D3 u05DEu05E2"u05DE u05D1u05DBu05E8u05D8u05D9u05E1',
	AA.Rate AS 'u05D0u05D7u05D5u05D6 u05DEu05E2"u05DE',
	AA.[ItemCode] AS N'u05E7u05D5u05D3 u05E4u05E8u05D9u05D8', 
	AA.[Dscription]AS N'u05EAu05D9u05D0u05D5u05E8 u05D4u05E4u05E8u05D9u05D8', 
	AA.[Quantity] AS N'u05DBu05DEu05D5u05EA', 
	AA.[Price] AS N'u05DEu05D7u05D9u05E8 u05DCu05D0u05D7u05E8 u05D4u05E0u05D7u05D4', 
	AA.[Currency] AS N'u05DEu05D8u05D1u05E2 u05D4u05DEu05D7u05D9u05E8', 	
	--T1.TaxCode AS 'u05E7u05D5u05D3 u05DEu05E2"u05DE u05D1u05DEu05E1u05DEu05DA',
	AA.VatPrcnt AS 'u05D0u05D7u05D5u05D6 u05DEu05E2"u05DE u05D1u05DEu05E1u05DEu05DA',
	AA.[LineTotal] AS N'u05E1u05D4"u05DB u05E9u05D5u05E8u05D4', 
	AA.[TotalSumSy] AS N'u05E1u05D4"u05DB u05E9u05D5u05E8u05D4 (u05DEu05E2u05E8u05DBu05EA)'

FROM (
	SELECT	N'u05D7u05E9u05D1u05D5u05E0u05D9u05EA' AS DocType, 
		T0.[DocNum], 
		T0.[DocDate], 
		T0.[CardCode], 
		T0.[CardName], 
		T4.GroupName,
		T3.MailAddres, T3.MailBlock, T3.MailCity, T3.MailZipCod, T3.MailCounty, T3.State2,T3.MailCountr,
		T6.Rate,
		T1.[ItemCode], 
		T1.[Dscription], 
		T1.[Quantity], 
		T1.[PriceBefDi], 
		T1.[Price], 
		T1.[Currency], 
		T1.VatPrcnt,
		T1.[LineTotal], 
		T1.[TotalSumSy]
	FROM OINV T0 
		INNER JOIN inv1 T1 ON T0.DocEntry = T1.DocEntry 
		INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
		INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode
		INNER JOIN OCRG T4 ON T3.GroupCode = T4.GroupCode
		INNER JOIN CRD1 T5 ON T3.CardCode = T5.CardCode
		INNER JOIN OSTC T6 ON T5.TaxCode = T6.Code
	WHERE T0.[DocDate] >= '[%1]' AND  T0.[DocDate] <= '[%2]' 
	UNION ALL
	SELECT	N'u05D6u05D9u05DBu05D5u05D9'AS DocType, 
		T0.[DocNum], 
		T0.[DocDate], 
		T0.[CardCode], 
		T0.[CardName], 
		T4.GroupName,
		T3.MailAddres, T3.MailBlock, T3.MailCity, T3.MailZipCod, T3.MailCounty, T3.State2,T3.MailCountr,
		T6.Rate,
		T1.[ItemCode], 
		T1.[Dscription], 
		-1*T1.[Quantity], 
		T1.[PriceBefDi], 
		T1.[Price], 
		T1.[Currency], 
		T1.VatPrcnt,
		-1*T1.[LineTotal], 
		-1*T1.[TotalSumSy]
	FROM ORIN T0 
		INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry 
		INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
		INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode
		INNER JOIN OCRG T4 ON T3.GroupCode = T4.GroupCode
		INNER JOIN CRD1 T5 ON T3.CardCode = T5.CardCode
		INNER JOIN OSTC T6 ON T5.TaxCode = T6.Code
	WHERE T0.[DocDate] >= '[%1]' AND  T0.[DocDate] <= '[%2]' ) AA

		--INNER JOIN CRD1 T5 ON AA.CardCode = T5.CardCode 
		--INNER JOIN INV1 T1 ON T3.CardCode = T1.CardCode

Thank You for your help.

Meital