Subquery Error (SAP HANA)

[Vitaliy-R]

Hey guys!

I'm having difficulty completing this query, because when I insert the (Subqueries) the query gives an error.

If I execute the query without the date parameters, it executes successfully, however if I put the date parameters and leave the subqueries the error ("SWEI) is presented.

SELECT T1."CardCode" AS "Cod. PN",
T2."DocEntry" AS "Doc. Origem",
'NF ' AS "Tipo de Doc",
T0."DocDueDate" AS "Vencimento",
TJ2."Debit" - TJ2."Credit" AS "Valor Original",

T2."SumApplied" - IFNULL(t2."WtAppld", 0) + (IFNULL(t0."UndOvDiff", 0) / 
    (SELECT COUNT(j."InvoiceId") 
    FROM VPM2 j 
    WHERE j."DocNum" = t2."DocNum")) AS "Valor Pago",

CASE 
        WHEN t0."CashSum" > 0 THEN T0."TaxDate" 
        WHEN t0."CheckSum" > 0 THEN T0."TaxDate" 
        WHEN t0."TrsfrSum" > 0 THEN T0."TrsfrDate" 
        WHEN T0."BoeAbs" IS NOT NULL THEN T6."TaxDate"
ELSE  t0."TrsfrDate"
END AS "Data Pagmto",
CASE 
        WHEN t0."CashSum" > 0 THEN T0."CashAcct"
        WHEN t0."CheckSum" > 0 THEN T0."CheckAcct" 
        WHEN t0."TrsfrSum" > 0 THEN t0."TrsfrAcct"
        WHEN T0."BoeAbs" IS NOT NULL THEN T0."BoeAcc"
        ELSE  t0."TrsfrAcct"
END AS "C.Contabil",

(Select MIN(TT2."AcctName")
FROM OACT TT2
Where T0."CashAcct" = TT2."AcctCode" OR T0."TrsfrAcct" = TT2."AcctCode" OR T0."CheckAcct" = TT2."AcctCode" OR T0."BoeAcc" = TT2."AcctCode") AS "Nome da Conta Contabil",
T0."DocNum" AS "Doc. Baixa",

T0."DocNum" AS "Doc. Baixa",
CASE 
        WHEN T0."BoeAbs" IS NOT NULL THEN 'Boleto' 
        ELSE 
        CASE 
            WHEN t0."CheckSum" > 0 THEN 'Cheque' 
            WHEN t0."TrsfrSum" > 0 THEN 'Transferencia' 
            WHEN t0."CashSum" > 0 THEN 'Dinheiro' 
            ELSE 'Cartao Credito' 
        END 
    END AS "Forma Pagmto",
T0."BPLId", 
CASE
        WHEN T0."BPLId" = '1' THEN 'CE'
        WHEN T0."BPLId" = '2' THEN 'RN'
        WHEN T0."BPLId" = '3' THEN 'BA'
        WHEN T0."BPLId" = '4' THEN 'PB'        
END AS "Unidade"
 
FROM OVPM T0 
    INNER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode" 
    INNER JOIN VPM2 T2 ON T2."DocNum" = T0."DocNum" AND T2."InvType" = '18' 
    LEFT OUTER JOIN OPCH T3 ON T3."DocEntry" = T2."DocEntry" 
    LEFT OUTER JOIN OBOE T4 ON T4."BoeKey" = T0."BoeAbs" 
    LEFT OUTER JOIN BOT1 T5 ON T5."BOENumber" = T4."BoeNum" 
    LEFT OUTER JOIN OBOT T6 ON T6."AbsEntry" = T5."AbsEntry" 
    INNER JOIN OJDT TJ1 ON TJ1."CreatedBy" = T3."DocEntry" AND TJ1."TransType" = '18' 
    INNER JOIN JDT1 TJ2 ON TJ2."TransId" = TJ1."TransId" AND TJ2."ShortName" = T1."CardCode"  AND TJ2."TransType" = '18' 
   
WHERE T0."Canceled" = 'N' AND (T4."BoeStatus" = 'P' OR T0."BoeAbs" IS NULL) AND 
    CASE 
        WHEN T5."BoeType" IS NOT NULL THEN T5."BoeType" 
        ELSE 'O' 
    END = 'O' AND 
    CASE 
        WHEN T6."StatusTo" IS NOT NULL THEN T6."StatusTo" 
        ELSE 'P' 
    END = 'P' 
    AND T0."BoeAbs" IS NULL    
    
                AND
    T0."DocDueDate" >= [%0] AND  
    T0."DocDueDate" <= [%1]

I left the subconstants and dates separate, to make the visualization better.

In doing some research on the forum, I saw some comments that SAP HANA has difficulties in interpreting date searches with multiple subqueries. And what they recommended in some cases was declaring variables, however when trying to always give an error in SAP HANA.

/*SELECT * FROM OINV T0*/
declare @FromDate date
declare @ToDate date
/* WHERE */
set @FromDate = /* T0.DocDate */‘[%0]’
set @ToDate =/* T0.DocDate */ ‘[%1]’

OR

Declare FromDate date;
Declare ToDate date;
/*SELECT * FROM OINV T0*/
/* WHERE */
set @FromDate = /* T0.DocDate */ '[%0]'
set @ToDate = /* T0.DocDate */ '[%1]'

I crashed and I don't know what else to do!
I ask the experts for help, pointing out that my database is SAP HANA.

Regards!

[Vitaliy-R]

Hi vando. I cannot recognize this syntax as HANA SQL. It seams more like MSSQL syntax for variables?

What are you trying to achieve with the parameters?

[Former Member]

Hi witalij

I want to put a filter by dates.
For example

t0. "DocDueDate"> = [% 0] and t0. "DocDueDate"  <= [%1]

If I put a predefined date, the query is executed successfully.
For example:

AND T0."DocDueDate" >= (TO_TIMESTAMP('20210505', 'YYYYMMDD') )  AND  
    T0."DocDueDate" <= (TO_TIMESTAMP('20210510', 'YYYYMMDD') )

However, when I put [% 0] the query gives an error.

The main table is the OVPM (T0).

Regards,

[Former Member]

Hey guys!
Any more tips?

[Former Member]

A better tag will be "SAP Business One" even if it is running on HANA.

[Former Member]

Thanks for your help Former Member!

This is really the principle and making an adjustment in the consultation to execute at Base Hana worked.
I will leave the result assertive so that everyone who comes to analyze can have a right path to follow.

Base Query HANA:

/*SELECT * FROM "OVPM" T33*/

Declare FromDate timestamp;
Declare ToDate timestamp;

FromDate := /* T33."DocDueDate" */ '[%0]';
ToDate := /* T33."DocDueDate" */ '[%1]';

SELECT "QUERY"...
.
.
.
AND t0."DocDueDate" BETWEEN FromDate AND ToDate;

Regards;