Skip to Content
0
Former Member
Mar 01, 2016 at 03:09 PM

SQL and IFNULL in script based view challenge

119 Views

The following SQL statement (in the SQL console) works as expected:

SELECT

T1."AGESELLSC" AS "COMPANY",

T1."APGESELLS" AS "PARTNERCOMPANY",

T1."0CURRENCY" AS "DEBITCURRENCY",

T1."_ERP_AMOUNT" AS "DEBIT",

(SELECT IFNULL (

(SELECT T2."0CURRENCY" FROM "_SYS_BIC"."ProjectXYZ/A7CO_R001" T2

WHERE T1."AGESELLSC" = T2."APGESELLS" AND T2."AGESELLSC" = T1."APGESELLS"),'') FROM DUMMY) as "CREDITCURRENCY",

(SELECT IFNULL (

(SELECT T3."_ERP_AMOUNT" FROM "_SYS_BIC"."ProjectXYZ/A7CO_R001" T3

WHERE T1."AGESELLSC" = T3."APGESELLS" AND T3."AGESELLSC" = T1."APGESELLS"),'0') FROM DUMMY) as "CREDIT"

FROM "_SYS_BIC"."ProjectXYZ/A7CO_R001" T1

JOIN "_SYS_BIC"."ProjectXYZ/A7CO_R001" T2 ON T2."APGESELLS" = T1."AGESELLSC"

WHERE

T1."_ERP_CURTYPE" = '00'

ORDER BY

T1."AGESELLSC";

Upon attempting to activate a script based calculation view with the same statement, the system reports the following error:

Repository: Encountered an error in repository runtime extension;Model inconsistency.

Deploy Calculation View: SQL: transaction rolled back by an internal error:

SqlScript: Could not derive table type for variable "VAR_OUT" (SQL error: fatal error: ColDicVal (1000029,-1) not found. See error trace for details): line 4 col 4 (at pos 274)

The following two 'half' variants are, however, accepted (and delivers the correspondingly incomplete results):

SELECT

T1."AGESELLSC" AS "COMPANY",

T1."APGESELLS" AS "PARTNERCOMPANY",

T1."0CURRENCY" AS "DEBITCURRENCY",

T1."_ERP_AMOUNT" AS "DEBIT",

(SELECT IFNULL (

(SELECT T2."0CURRENCY" FROM "_SYS_BIC"."ProjectXYZ/A7CO_R001" T2

WHERE T1."AGESELLSC" = T2."APGESELLS" AND T2."AGESELLSC" = T1."APGESELLS"),'') FROM DUMMY) as "CREDITCURRENCY",

(SELECT T3."_ERP_AMOUNT" FROM "_SYS_BIC"."ProjectXYZ/A7CO_R001" T3

WHERE T1."AGESELLSC" = T3."APGESELLS" AND T3."AGESELLSC" = T1."APGESELLS") as "CREDIT"

FROM "_SYS_BIC"."ProjectXYZ/A7CO_R001" T1

JOIN "_SYS_BIC"."ProjectXYZ/A7CO_R001" T2 ON T2."APGESELLS" = T1."AGESELLSC"

WHERE

T1."_ERP_CURTYPE" = '00'

ORDER BY

T1."AGESELLSC";

and:

SELECT

T1."AGESELLSC" AS "COMPANY",

T1."APGESELLS" AS "PARTNERCOMPANY",

T1."0CURRENCY" AS "DEBITCURRENCY",

T1."_ERP_AMOUNT" AS "DEBIT",

(SELECT T2."0CURRENCY" FROM "_SYS_BIC"."ProjectXYZ/A7CO_R001" T2

WHERE T1."AGESELLSC" = T2."APGESELLS" AND T2."AGESELLSC" = T1."APGESELLS") as "CREDITCURRENCY",

(SELECT IFNULL (

(SELECT T3."_ERP_AMOUNT" FROM "_SYS_BIC"."ProjectXYZ/A7CO_R001" T3

WHERE T1."AGESELLSC" = T3."APGESELLS" AND T3."AGESELLSC" = T1."APGESELLS"),'0') FROM DUMMY) as "CREDIT"

FROM "_SYS_BIC"."ProjectXYZ/A7CO_R001" T1

JOIN "_SYS_BIC"."ProjectXYZ/A7CO_R001" T2 ON T2."APGESELLS" = T1."AGESELLSC"

WHERE

T1."_ERP_CURTYPE" = '00'

ORDER BY

T1."AGESELLSC";

Can somebody explain what is going on? ... and hopefully how this could be fixed 🤣