0

I have the query like this..

SELECT    
sum(wms_trx."trans_net_amt") as "TRANS_AMT_FCY",
    wms_trx."trans_type_cd",
    CASE WHEN "trans_type_cd" IN (SELECT "ref_value" FROM "B_VIRTUAL"."man_upload_ref")
        THEN '1' ELSE '0' END as "IS_FIN_TRANS_FLAG"
    FROM "B_VIRTUAL"."sor_wms_trx" AS wms_trx
    WHERE wms_trx."batch_date" = '20190501'
    GROUP BY wms_trx."trans_type_cd", 'IS_FIN_TRANS_FLAG'

But when I executed it, I got error like this

Could not execute 'SELECT sum(wms_trx."trans_net_amt") as "TRANS_AMT_FCY", wms_trx."trans_type_cd", CASE WHEN ...' in 535 ms 609 µs .
SAP DBTech JDBC: [403]: internal error: Error opening the cursor for the remote database Error while compiling statement: FAILED: ParseException line 1:159 missing ) at 'V1' near 'trans_type_cd' in table source
line 1:196 cannot recognize input near 'V2' 'FROM' 'sor' in table source for query "SELECT "sor_man_upload_ref1"."V1", "sor_man_upload_ref1"."V2", "sor_man_upload_ref2"."V1" FROM ( (SELECT SUM("WMS_TRX"."trans_net_amt") AS "V1", "WMS_TRX"."trans_type_cd" AS "V2" FROM """sor"".""sor_wms_trx""" "WMS_TRX" WHERE "WMS_TRX"."batch_date" = '20190501' GROUP BY "WMS_TRX"."trans_type_cd" ) "sor_man_upload_ref1" LEFT OUTER JOIN (SELECT "sor_man_upload_ref"."ref_value" AS "V1" FROM """sor"".""sor_man_upload_ref""" "sor_man_upload_ref" GROUP BY "sor_man_upload_ref"."ref_value" ) "sor_man_upload_ref2" ON ("sor_man_upload_ref1"."V2" = "sor_man_upload_ref2"."V1") ) "

Please help me... thanks

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Jackson Conrad
  • 1
  • 1
  • Did you try to execute the query in error log at remote site? This question has very little details and requires debugging at your local system, so should be asked at SAP Support. Also subquery without correlation filter in `case` statement can cause performance problems. It is better to use `left join` with distinct `ref_value` for this – astentx Apr 10 '21 at 17:14

0 Answers0