All,


We're running into an interesting case. We are making a synchronisation tool for synching old and new datamodel / environment (same DB, different schema). One of the things to do is comparing addresses to see if they already exist. What we find is that if one of the parameters is an emtpty string in the one table, the aggregate to find that record can not find it in the other (where we are inserting). The code generated is:


SELECT *FROM (SELECT ENCAF_ADRESSEN.[ADS_ID] o0, ENCAF_ADRESSEN.[CREATEDBY] o1, ENCAF_ADRESSEN.[CREATEDDATE] o2, ENCAF_ADRESSEN.[MODIFIEDBY] o3, ENCAF_ADRESSEN.[MODIFIEDDATE] o4, ENCAF_ADRESSEN.[BDF_ID] o5, ENCAF_ADRESSEN.[STRAAT] o6, ENCAF_ADRESSEN.[HUISNR] o7, ENCAF_ADRESSEN.[HUISNR_TOEV] o8, ENCAF_ADRESSEN.[POSTCODE_N] o9, ENCAF_ADRESSEN.[POSTCODE_A] o10, ENCAF_ADRESSEN.[PLAATS] o11, ENCAF_ADRESSEN.[MVS_ACCOUNTVOLGNR] o12FROM "CAFDAT"."CAF_ADRESSEN" ENCAF_ADRESSENWHERE (ENCAF_ADRESSEN.[STRAAT] = NVL(@STRAAT, ' ')) AND (ENCAF_ADRESSEN.[HUISNR] = @HUISNR) AND (ENCAF_ADRESSEN.[HUISNR_TOEV] = NVL(@HUISNR_TOEV, ' ')) AND (ENCAF_ADRESSEN.[POSTCODE_N] = @POSTCODE_N) AND (ENCAF_ADRESSEN.[POSTCODE_A] = NVL(@POSTCODE_A, ' ')) AND (ENCAF_ADRESSEN.[PLAATS] = NVL(@PLAATS, ' '))ORDER BY ENCAF_ADRESSEN.[CREATEDBY] ASC )WHERE ROWNUM <= 25


Attached also the filters that are set in the aggregate. 


What you see is that all the NVL's contain ' ' for if the value is empty..... However, there is a single space between the quotes. I would really like it to be just an empty string (quotes WITHOUT the single space). Anybody an idea of what to do?


Kind regards,

Alexander Lange


Hi Alexander,

In OutSystems there's no NULL value, in SQL Server it's an empty string and in Oracle, it's with space.

This is the normal platform behaviour, not sure if you can override it.

Understood. Again the nullvalue issues. In stead of trying to get this working we just used a very dirty dfix. For whom is interested:


CAF_ADRESSEN.HUISNR_TOEV+"NullFix"=CafAdressenRec.HUISNR_TOEV+"NullFix"


Is very dirty but works like a charm in case of null values where the WHERE-clause should treat them as empty strings. The single space can still be found in the resulting SQL code but less harmfull for the purpose we were using it for:


((ENCAF_ADRESSEN.[HUISNR_TOEV] || NVL('NullFix', ' ')) = (@HUISNR_TOEV || 'NullFix'))

Tx for the explanation


Kidn regards,

Alexander