Skip to Content
0

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

Dec 11, 2017 at 08:27 AM

126

avatar image
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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Reimer Pods Dec 11, 2017 at 11:01 AM
1

You might want to try specifying the date using the format 'yyyy-mm-dd', e.g. '2017-06-01'. Your results may vary depending on the setting the database options. Here's a SQL snippet to check the option values:

select * from sys.sysoptions 
where "option" in ('date_order', 'sr_date_format') 

Example für the returned values:


dateoptions.png (3.2 kB)
Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

The problem that I have is setting the parameter into the query. With every date-value (format) the query is working.

I’m sorry, but I’m using Hana and the SQL snippet that you send me doesn’t work.

0

Since you are using HANA, I have removed the tag for SAP SQL Anywhere, which is a different RDBMS with different syntax and system tables. I have added the tag for SAP HANA.

Mike Loop
Senior Product Support Engineer
SAP Product Support

1