Feb 19, 2021 at 05:33 AM
Must specify table to select from

74 Views Last edit Feb 20, 2021 at 03:29 AM 2 rev
hi can anyone find a workaround of my code?
I'm trying to set a parameter but it gives me this error:
1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Must specify table to select from. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement '' (SWEI) (s) could not be prepared.
BTW, I'm using query generator of SAP B1 9.2.. Tnx :)
DECLARE @asDate AS DATE, @startDate AS DATE, @adv1Mon AS DATE, @sAdv1Mon AS DATE, @bck1Mon AS DATE, @sBck1Mon AS DATE, @yrMon VARCHAR(100),
@1yrMon AS VARCHAR(100), @1ByrMon AS VARCHAR(100), @lAdv1Mon AS DATE, @lBck1Mon AS DATE, @bck2Mon AS DATE, @sBck2Mon AS DATE, @lBck2Mon AS DATE,
@2ByrMon AS VARCHAR(100)


SET @asDate = '[%0]' --parameter
SET @startDate = (SELECT DATEADD(month, DATEDIFF(month, 0, @asDate), 0)) --First Date of AsOfDate
SET @adv1Mon = (SELECT DATEADD(month, +1, @asDate)) -- Next Month
SET @sAdv1Mon = (SELECT DATEADD(month, DATEDIFF(month, 0, @adv1Mon), 0)) -- First Date of Next Month
SET @lAdv1Mon = (SELECT EOMONTH(@adv1Mon)) -- Last Date of Next Month
SET @bck1Mon = (SELECT DATEADD(month, -1, @asDate)) -- Previous Month
SET @sBck1Mon = (SELECT DATEADD(month, DATEDIFF(month, 0, @bck1Mon), 0)) -- First Date of Previous Month
SET @lBck1Mon = (SELECT EOMONTH(@bck1Mon)) -- Last Date of Prevoius Month
SET @bck2Mon = (SELECT DATEADD(month, -2, @asDate)) -- Previous 2 Months
SET @sBck2Mon = (SELECT DATEADD(month, DATEDIFF(month, 0, @bck2Mon), 0)) -- First Date of Previous 2 Months
SET @lBck2Mon = (SELECT EOMONTH(@bck2Mon)) -- Last Date of Previous 2 Months
SET @yrMon = (SELECT CONVERT(VARCHAR(7), @asDate, 126)) -- Year & Month of AsOfDate
SET @1yrMon = (SELECT CONVERT(VARCHAR(7), @adv1Mon, 126)) -- Year & Month of Next Month
SET @1ByrMon = (SELECT CONVERT(VARCHAR(7), @bck1Mon, 126)) -- Year & Month of Previous Month
SET @2ByrMon = (SELECT CONVERT(VARCHAR(7), @bck2Mon, 126)) -- Year & Month of Previous Month


SELECT
	--@startDate [StartDate], @asDate [AsOfDate],
	A.DocNum [DocNum], A.DocEntry [DocEntry],
	A.NumAtCard, A.CardCode [CardCode], A.CardName [CardName],
	CAST(A.U_TERM AS INT) [Term], CAST(A.U_MA AS NUMERIC(19,2)) [MonAmortz],
	
	ISNULL((SELECT dbo._amortz(A.DocEntry, @1ByrMon)), 0) [PrevMonAmortz],
	ISNULL((SELECT dbo._amortz(A.DocEntry, @1yrMon)), 0) [NxtMonAmortz],
	ISNULL((SELECT dbo._amortz(A.DocEntry, @yrMon)) ,0) [CurAmortz],
	
	CAST(A.DocDate AS DATE) [DocDate], (SELECT MIN(CAST(O.DueDate AS DATE)) FROM dbo.INV6 O WHERE O.DocEntry = A.DocEntry) [FDueDate], 
	CAST(A.DocDueDate AS DATE) [DueDate], 
	CAST(A.DocTotal AS NUMERIC (19,2)) [DocTotal], CAST(C.Balance AS NUMERIC(19,2)) [Balance],
	CAST(D.DocDate AS DATE) [LastPayDate], ISNULL(CAST(D.CashSum AS NUMERIC(19,2)), 0) [LastPayAmnt],


	ISNULL((SELECT CAST(SUM(X.CashSum) AS NUMERIC(19,2)) FROM dbo.ORCT X WHERE X.CardCode = A.CardCode AND X.CounterRef = A.NumAtCard 
		AND X.DocDate >= @sBck1Mon AND X.DocDate <= @lBck1Mon), 0) [PrevMonPayAmnt],
	
	CASE
		WHEN (C.Balance - D.CashSum) < 0 THEN 0
	ELSE ISNULL(CAST((C.Balance - D.CashSum) AS NUMERIC(19,2)), C.Balance) END [NotYetDue],


	ISNULL((SELECT SUM(CAST(X.CashSum AS NUMERIC(19,2))) FROM dbo.ORCT X 
		WHERE X.CardCode = A.CardCode AND X.CounterRef = A.NumAtCard AND X.DocDate >= @startDate AND X.DocDate <= @asDate 
		GROUP BY X.CardCode), 0) [DueThisMonth],
		
		CASE 
			WHEN (SELECT SUM(Y.CashSum) FROM dbo.ORCT Y 
				WHERE Y.CardCode = A.CardCode AND Y.CounterRef = A.NumAtCard AND Y.DocDate >= @startDate AND Y.DocDate <= @asDate) > 
					(SELECT dbo._amortz(A.DocEntry, @yrMon))		
			THEN (SELECT SUM(CAST(B.CashSum AS NUMERIC(19,2))) FROM dbo.ORCT B 
				WHERE B.CardCode = A.CardCode AND B.CounterRef = A.NumAtCard AND B.DocDate >= @startDate AND B.DocDate <= @asDate) - 
					(SELECT dbo._amortz(A.DocEntry, @yrMon))
		ELSE 0 END [AdvPay4NxtMon],


	ISNULL((SELECT dbo._amortz(A.DocEntry, @1yrMon)) - 
			(CASE 
				WHEN (SELECT SUM(Y.CashSum) FROM dbo.ORCT Y 
					WHERE Y.CardCode = A.CardCode AND Y.CounterRef = A.NumAtCard AND Y.DocDate >= @startDate AND Y.DocDate <= @asDate) > 
						(SELECT dbo._amortz(A.DocEntry, @yrMon))		
				THEN (SELECT SUM(CAST(B.CashSum AS NUMERIC(19,2))) FROM dbo.ORCT B 
					WHERE B.CardCode = A.CardCode AND B.CounterRef = A.NumAtCard AND B.DocDate >= @startDate AND B.DocDate <= @asDate) - 
						(SELECT dbo._amortz(A.DocEntry, @yrMon))
			ELSE 0 END), 0) [DueNxtMonth],


	ISNULL(CASE	
				WHEN (SELECT dbo._amortz(A.DocEntry, @1ByrMon)) > (SELECT SUM(CAST(B.CashSum AS NUMERIC(19,2))) FROM dbo.ORCT B 
					WHERE B.CardCode = A.CardCode AND B.CounterRef = A.NumAtCard AND B.DocDate >= @sBck1Mon AND B.DocDate <= @lBck1Mon)
				THEN (SELECT dbo._amortz(A.DocEntry, @1ByrMon)) - (SELECT SUM(CAST(B.CashSum AS NUMERIC(19,2))) FROM dbo.ORCT B 
					WHERE B.CardCode = A.CardCode AND B.CounterRef = A.NumAtCard AND B.DocDate >= @sBck1Mon AND B.DocDate <= @lBck1Mon)
			ELSE 0 END, 0) [OvrDue1Mon],




	ISNULL(CASE
				WHEN (SELECT CAST(SUM(X.InsTotal) AS NUMERIC(19,2)) FROM dbo.INV6 X WHERE X.DocEntry = A.DocEntry 
					AND X.DueDate >= (SELECT MIN(CAST(O.DueDate AS DATE)) FROM dbo.INV6 O WHERE O.DocEntry = A.DocEntry) 
					AND X.DueDate <= @lBck2Mon) > 
				(SELECT CAST(SUM(B.CashSum) AS NUMERIC(19,2)) FROM dbo.ORCT B WHERE B.CounterRef = A.NumAtCard
					AND B.DocDate >= (SELECT MIN(CAST(O.DueDate AS DATE)) FROM dbo.INV6 O WHERE O.DocEntry = A.DocEntry)
					AND B.DocDate <= @lBck2Mon)
		
			THEN (SELECT CAST(SUM(X.InsTotal) AS NUMERIC(19,2)) FROM INV6 X WHERE X.DocEntry = A.DocEntry 
					AND X.DueDate >= (SELECT MIN(CAST(O.DueDate AS DATE)) FROM dbo.INV6 O WHERE O.DocEntry = A.DocEntry) 
					AND X.DueDate <= @lBck2Mon) - 			 
				 (SELECT CAST(SUM(B.CashSum) AS NUMERIC(19,2)) FROM dbo.ORCT B WHERE B.CounterRef = A.NumAtCard
					AND B.DocDate >= (SELECT MIN(CAST(O.DueDate AS DATE)) FROM dbo.INV6 O WHERE O.DocEntry = A.DocEntry)
					AND B.DocDate <= @lBck2Mon) 
			ELSE 0 END, 0) [OvrDue2Mon&Below], 


	ISNULL((SELECT CAST(SUM(X.InsTotal) AS NUMERIC(19,2)) FROM dbo.INV6 X WHERE X.DocEntry = A.DocEntry 
					AND X.DueDate >= (SELECT MIN(CAST(O.DueDate AS DATE)) FROM dbo.INV6 O WHERE O.DocEntry = A.DocEntry) 
					AND X.DueDate <= @lBck2Mon), 0) [TotAmortz2Mon&Below],
					
	ISNULL((SELECT CAST(SUM(B.CashSum) AS NUMERIC(19,2)) FROM dbo.ORCT B WHERE B.CounterRef = A.NumAtCard
					AND B.DocDate >= (SELECT MIN(CAST(O.DueDate AS DATE)) FROM dbo.INV6 O WHERE O.DocEntry = A.DocEntry)
					AND B.DocDate <= @lBck2Mon), 0) [TotPay2Mon&Below],                                                                                                                            


               A.DocNum, A.CardCode, A.NumAtCard


	--ISNULL((SELECT MAX(L.BalDueDeb) FROM JDT1 L where (SELECT CONVERT(VARCHAR(7), L.DueDate, 126)) = @1yrMon AND ShortName = A.CardCode), 0),


	--,A.CardCode, @1yrMon [NxtMonth] 


FROM dbo.OINV A
	INNER JOIN dbo.OCRD C ON A.CardCode = C.CardCode
	LEFT JOIN dbo.ORCT D ON A.CardCode = D.CardCode AND D.DocDate IN (SELECT MAX(DocDate) FROM dbo.ORCT X WHERE X.CardCode = A.CardCode 
		AND X.CounterRef = A.NumAtCard) AND A.NumAtCard = D.CounterRef


WHERE A.CtlAccount = 103012 AND A.DocStatus = 'O'
ORDER BY A.CardName ASC, A.DocEntry ASC