Skip to Content
avatar image
Former Member

Unable to use date parameter in a query with a sub-query - query generator - sap b1

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers