Skip to Content
0
Jun 13 at 07:38 AM

Need help to execute the Query in Hana

52 Views

Dear Experts

am getting some error when i run this query in HANA

Pls help to solve this issue

Regards

Vinod

SELECT    
	     ovpm."DocEntry"
	    ,OVPM."DOCNUM"   AS  "Voucher No."
	    ,OVPM."DOCDATE"  AS  "Voucher Date"
		,OVPM."CardCode" AS  "Vendor Code"
		,OVPM."CardName" AS  "Vendor Name"
		,(SELECT CASE WHEN VPM2."INVTYPE"=18 THEN OPCH."DocNum"
					  WHEN VPM2."INVTYPE"=19 THEN ORPC."DocNum" END)As "Invoice No"
		,(SELECT CASE WHEN VPM2."INVTYPE"=18 THEN OPCH."NumAtCard"
					  WHEN VPM2."INVTYPE"=19 THEN ORPC."NumAtCard" END) AS "Vendor Invoice Reference"
		,(SELECT CASE WHEN VPM2."INVTYPE"=18 THEN OPCH."DocDate"
					  WHEN VPM2."INVTYPE"=19 THEN ORPC."DocDate" END)   AS "Vendor Invoice Date"
		,(SELECT CASE WHEN VPM2."INVTYPE"=18 THEN OPCH."DocTotal"
					  WHEN VPM2."INVTYPE"=19 THEN ORPC."DocTotal" END)  AS "Bill Amount"
		,(SELECT CASE WHEN VPM2."INVTYPE"<>'' OR VPM2."INVTYPE"<>'NULL' THEN  
		( SELECT CASE WHEN VPM2."INVTYPE"=19   THEN VPM2."SUMAPPLIED"
						WHEN VPM2."INVTYPE"<>19 THEN VPM2."SUMAPPLIED"
						WHEN VPM4."AcctCode"<>'' OR VPM4."AcctCode"<>'NULL' THEN VPM4."SUMAPPLIED"
						ELSE OVPM."DocTotal" END) END) AS "Line Total Payment Amt"
		,(SELECT CASE WHEN VPM2."INVTYPE"=18 THEN opch."DocTotal"
										WHEN VPM2."INVTYPE"=19 THEN (-ORPC."DocTotal" ) END) "Invoice Doc Total"
		,(OPCH."DOCTOTAL"- vpm2."SumApplied") "Due Amt"
		 ,OVPM."DOCTOTAL" AS "Outgoing payment Doc total"
		,(SELECT CASE WHEN VPM2."INVTYPE"=18 THEN 'A/P Invoice'
					  WHEN VPM2."INVTYPE"=19 THEN 'Credit Note'
					  WHEN vpm2."InvType"=46 THEN 'Outgoing Payment'
				      WHEN VPM2."InvType"=23 THEN 'Incoming Payment' 
					  WHEN VPM2."InvType"=30 THEN 'Journal Entry'
					  WHEN VPM2."InvType"=-2 THEN 'Opening Balance' ELSE 'Payment on Account' END) "Base Document Type"
		 ,VPM1."CheckNum" "Cheque No" 
		 ,(CASE WHEN (ISNULL(OVPM."CashSum",0) <> 0 ) THEN 'Cash Transfer'
			WHEN (ISNULL(OVPM."CheckSum",0) <> 0 ) THEN 'Cheque Transfer'
			WHEN (ISNULL(OVPM."TrsfrSum",0) <> 0 ) THEN 'NEFT' 
			WHEN OJDT."TransType" = '30' THEN 'JEs' 
			ELSE ''
			 END) AS "Transaction Type"
		
                      
FROM         ODSC (NOLOCK) RIGHT OUTER JOIN
                      OVPM (NOLOCK) LEFT OUTER JOIN
                      VPM4 (NOLOCK) ON OVPM."DOCENTRY" = VPM4."DOCNUM" LEFT OUTER JOIN
                      VPM1 (NOLOCK) ON OVPM."DOCENTRY" = VPM1."DOCNUM" LEFT OUTER JOIN
                      OACT (NOLOCK) ON OVPM."TRSFRACCT" = OACT."ACCTCODE" ON ODSC."BANKCODE" = VPM1."BANKCODE" LEFT OUTER JOIN
                      VPM2 (NOLOCK) ON OVPM."DOCENTRY" = VPM2."DOCNUM"
                      LEFT OUTER JOIN OPCH (NOLOCK) ON OPCH."DOCENTRY"=VPM2."DOCENTRY" AND VPM2."INVTYPE"=18
                      LEFT OUTER JOIN ORPC (NOLOCK) ON ORPC."DocEntry"=VPM2."DocEntry" AND VPM2."INVTYPE"=19
                      LEFT OUTER JOIN OJDT (NOLOCK) ON OJDT."TRANSID"=OPCH."TRANSID"
                      LEFT OUTER JOIN OJDT as X (NOLOCK) ON x."TRANSID"=ORPC."TRANSID"
                      LEFT OUTER JOIN OJDT AS Y (NOLOCK) ON Y."TransId"=VPM2."DocEntry" AND VPM2."InvType" IN (23,46,30)
                     
                      LEFT JOIN OCRD (NOLOCK) ON OCRD."CARDCODE"=OVPM."CARDCODE"
--WHERE     OVPM.DocDate =(SELECT CONVERT(date, getdate()))
WHERE     OVPM."DocNum" = '10418'
GROUP BY    OVPM."DOCNUM", OVPM."DOCDATE", OVPM."CARDNAME",OVPM."CARDCODE",  OVPM."DOCTOTAL", 
 VPM1."CHECKNUM",  OPCH."DOCTOTAL",VPM2."SUMAPPLIED",OPCH."DOCNUM",OPCH."NumAtCard",ORPC."DocTotal"
,OJDT."Number",X."Number",Y."Number",ORPC."DocNum",ORPC."NumAtCard" ,OPCH."DocDate",ORPC."DocDate",VPM2."INVTYPE",VPM4."AcctCode",VPM4."SUMAPPLIED"
,VPM1."CHECKSUM",ovpm."DocEntry",OVPM."CashSum",OVPM."CheckSum",OVPM."TrsfrSum",OJDT."TransType"
order by
ovpm."DocEntry" asc