>>Would you try this:
>>IIF(ISNULL(Member.NoSchool),0,Member.NoSchool) AS NoSchool
>>replaced with:
>>nvl(Member.NoSchool,0) AS NoSchool
>
>Yes, that would resolve the issue. The same would go with:
>
>
>IIF(ISNULL(Member.NoSchool),000000,Member.NoSchool) AS NoSchool
>
>
>But, I am looking to understand why removing a where clause causes the data to be different. This simply doesn't make any sense to me.
>
>As for the updated SQL, such as how I did it or considering your approach, I am looking for the best approach that would allow me less changes when the data would be migrated to SQL Server. Do you know if anyone of those approaches are compliant with SQL Server:
>
>
>IIF(ISNULL(Member.NoSchool),000000,Member.NoSchool) AS NoSchool
>NVL(Member.NoSchool,0) AS NoSchool
>
AFAIK both are incompatible. SQL server one:
ISNULL( Member.NoSchool , 0 ) as NoSchool
I think having first version if first value is null it's specifying the width of the field.
Cetin