I have a requirement to set an output field in a view to NULL. In SQLScript I would say
Select Null as Ouput_Field From <Table>
Now you may ask why I would need to do this, since what is the point of a view showing a null value.The answer is that this select statement is part of a union view, as such
Select GUID as Output_Field<br>From <TableA><br>Union<br>Select NULL as Output_Field<br>From <TableB>
The first select statement provides a valid value to the output field, but there is no GUID in table B, so the value needs to be set to NULL.
You may now say, why not set the field value to '' (blank)? It is not possible to set the field to blank because doing so creates a different field type, causing a type mismatch between the RAW(16) field created by the first query.
If I could enter the code as shown above, SQL should take the field type following the union to be the same field type as the field preceding the union, however for OpenSQL I find this not to be the case.
I have overcome this problem for myself by forcing in a GUID value in the second union, even though the GUID value has no bearing on the results and has to be ignored. At least doing this it kept the field type in the second half of the query as type RAW(16).This however is not an ideal solution. We should be able to set an output field value to NULL.
I am running HANA 7.4, so wonder if this problem has been dealt with in future versions, or if it is going to be addressed at some point?