SubQuery in Calculation View for modeling

[saikarthi]

Hi Experts,

How to write the Sub-query for the Calculation View -using SQL Script.

Below mentioned Sub-query is not working in Calculation View. Is there any alternative method Exist for this Query:

((SELECT SUM((case when upper(t4."staType") =-100 then T4."TaxSum" else 0 end) FROM PCH4 T4 WHERE T4."DocEntry"=T0."DocEntry" ),0)) "CGST_Amt"

Thanks in Advance..

[pfefferf]

What is the error you get?

[venkateswaran_k]

Make sure, you added group clause - since you added aggrigation function.

Also please provide your full Query statement and Error message as well.

[saikarthi]

Hi Mr Venkat/Florian,

Pls Find the Query for CV. where I want To include the Subquery after x.LineNum..

Var_out = SELECT Distinct T5."BPLName", T0."DocNum", T0."DocEntry" , T0."NumAtCard", T0."TaxDate",T0."U_FinNo" ,T0."DocDate", T0."CardCode" , T0."CardName" ,T8."BpGSTN", T3."LineTotal" , T3."VatSum", T0."DocTotal" , T0."Comments", x."TaxSum", x."TaxRate", x."staType", x."LineNum"

FROM "DB".OINV T0 INNER JOIN "DB".INV1 T3 on T3."DocEntry" = T0."DocEntry" LEFT OUTER JOIN "DB".INV3 T2 ON T0."DocEntry" = T2."DocEntry" LEFT OUTER JOIN "DB".OITM l on l."ItemCode" = T3."ItemCode" LEFT OUTER JOIN "DB".OCHP T15 ON T15."AbsEntry" = l."ChapterID" LEFT OUTER JOIN "DB".NNM1 T16 ON T0."Series" = T16."Series" LEFT OUTER JOIN "DB".OITB T17 ON l."ItmsGrpCod" =T17."ItmsGrpCod" LEFT OUTER JOIN "DB".SRI1 S1 on S1."ItemCode" = T3."ItemCode" and T0."DocEntry"=S1."BaseEntry" and T3."LineNum"=S1."BaseLinNum" LEFT OUTER JOIN "DB".OSRI S2 ON S2."SysSerial" = S1."SysSerial" and S1."ItemCode" = S2."ItemCode" LEFT OUTER JOIN "DB".OSLP S3 ON S3."SlpCode" = T0."SlpCode" LEFT OUTER JOIN "DB".OBPL T5 ON T0."BPLId" = T5."BPLId" LEFT OUTER JOIN "DB".INV4 x ON x."DocEntry" = T0."DocEntry" and x."LineNum" =T3."LineNum" and x."RelateType" = 1 LEFT OUTER JOIN "DB"."@CW_MVARIT" A ON A."Code" = l."U_MVariant" LEFT OUTER JOIN "DB".INV12 T8 ON T0."DocEntry"= T8."DocEntry" LEFT OUTER JOIN "DB".OCRD T9 ON T0."CardCode" = T9."CardCode" --LEFT OUTER JOIN "DB".CRD1 T10 ON T9."CardCode" = T10."CardCode" Where T0."CANCELED" = 'N

Group By T0."U_IsWizard", T0."U_TransType", T5."BPLName", T0."DocNum", T0."DocEntry" , T0."NumAtCard", T0."TaxDate", T0."U_FinNo" , T0."DocDate", T0."CardCode" , T0."CardName" , T0."GSTTranTyp", T8."BpGSTN", T8."State", T3."LineTotal" , T3."VatSum", T0."DocTotal" , T0."Comments", x."TaxSum", x."TaxRate", x."staType", x."LineNum" ;

If i Include the above mentioned Subquery then Error Syntax Occured : (Repository: Encountered an error in repository runtime extension;Model inconsistency. Deploy Calculation View: SQL: invalid table name: Could not find table/view PCH4 in schema SYSTEM: line 31 col 32 (at pos 1117))

[pfefferf · Accepted solution]

I think the message you receive ("... could not find table/view PCH4 in schema SYSTEM ...") is quite clear. You have not mentioned a schema for that table/view and therefore SYSTEM is assumed as schema. You have to mention the schema name in which the view/table exists (e.g. MYSCHEMA.PCH4).