cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member

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.

Accepted Solutions (1)

Accepted Solutions (1)

Johan_H
Active Contributor

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]

 

 

Swathy
Explorer
0 Kudos
Thanks

Answers (1)

Answers (1)

Swathy
Explorer
0 Kudos

Hi,

I am facing the same problem. Have you got any solution on this..

Johan_H
Active Contributor
0 Kudos
Please see the provided answer.