on 12-11-2017 8:28 AM
I have a query for which I have to use a date as a parameter. The query has a subquery to generate the report. Without parameter the query give me the right result, but I become an error when I use the. I am using SAP Business One 9.1 (9.10.210) PL: 11 (64-bit). The coding is as follows:
SELECT T1."TransId", T1."RefDate", T1."Account", T1."ContraAct",
(SELECT T00."CardName" FROM OCRD T00 WHERE T00."CardCode" = CASE WHEN LENGTH(T1."ContraAct") = 4 THEN T1."Ref3Line" ELSE T1."ContraAct" END) AS "Customer",
(SELECT T00."Country" FROM OCRD T00 WHERE T00."CardCode" = CASE WHEN LENGTH(T1."ContraAct") = 4 THEN T1."Ref3Line" ELSE T1."ContraAct" END) || CASE WHEN LEFT(T1."ContraAct",3) = 995 THEN ' - IC' ELSE '' END AS "Land",
T1."ProfitCode", T1."Project", T1."Credit"-T1."Debit" AS "Betrag" , T1."LineMemo", T0."BaseRef" FROM OJDT T0 INNER JOIN JDT1 T1 ON T0."TransId" = T1."TransId" WHERE LEFT(T1."Account",1) = '4' AND T1."RefDate" Between '01.06.2017' AND '30.06.2017' AND T1."Credit"-T1."Debit" <> 0
When I change : Between '01.06.2017' AND '30.06.2017' toBetween [%1] AND [%2] or Between ‘[%1]’ AND ‘[%2]’ the query is works no more.
Thanks for your help.
When using subqueries, the normal parameter syntax of [%0] may not work.
For these cases you may use the following workaround:
/* select * from ORDR x */
DECLARE @FROM AS DATETIME
DECLARE @TO AS DATETIME
SET @FROM = /* x.DocDate */ [%0]
SET @TO = /* x.DocDate */ [%1]
--query here using @FROM and @TO instead of [%0] and [%1]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
I am facing the same problem. Have you got any solution on this..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
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.