on 02-19-2021 5:33 AM
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
Hi Emmanuel,
Please add this at the top:
/* select * from OINV x */
then change this:
SET @asDate ='[%0]'--parameter
to this:
SET @asDate = /* x.DocDate */ [%0]
Regards,
Johan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Please also change all DATE variables to DATETIME.
If that still does not work, you can try declaring all variables more explicitly, and leaving -- comments out:
DECLARE @asDate AS DATETIME,
DECLARE @startDate AS DATETIME,
DECLARE @adv1Mon AS DATETIME,
etcetera
so you query looks something like this:
/* select * from OINV x */
DECLARE @asDate AS DATETIME,
DECLARE @startDate AS DATETIME,
DECLARE @adv1Mon AS DATETIME,
etcetera
SET @asDate = /* x.DocDate */ [%0]
SET @startDate = (SELECT DATEADD(month, DATEDIFF(month, 0, @asDate), 0))
etcetera
SELECT A.DocNum [DocNum], A.DocEntry [DocEntry],
etcetera
Regards,
Johan
User | Count |
---|---|
110 | |
12 | |
11 | |
6 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.