I have a sample command like this:
SELECT DISTINCT
CASE
WHEN {table}.[A] = 0 THEN 'A'
else {table}.[A]
END AS office
FROM
{table}
Structures' attributes are all text, but there will be an error Conversion failed when converting numeric from character string.
The same error will occur if column A has the data type Date, but the office I want to retrieve is of text type
If I put a space after the THEN keyword like this WHEN {table}.[A] = 0 THEN ' ' then the result will automatically be converted to 0 which is not a space
I need it to display properly according to the value after the THEN keyword
Hello,
It shouldn't be any error happened for case {Table}.[A] = 0 as result will be 'A' which will implicitly converted to text. The issue will be for else result which as you mentioned its date value but you can use SQL convert function as Convert(NVARCHAR,{table}.[A] ) to convert date to text.
But I have a question why you comparing {table}.[A] of data type date to 0 into when clause?
If you can share screenshot of error message this will help also
because I want if the result is 0, it will be displayed on the screen as " " or some text value, not 0.
I tried the convert function, and also the cast function, but I got the error "Conversion failed when converting numeric from character string."
What I can understand from error message that either column A is string or attribute of your structure is integer?
Please check data types of your structure and column of table again
Hi @tuyenhx ,
Based on the information you provided, you are having the error "Conversion failed when converting numeric from character string" and the output of ... THEN ' ' seems to be converted to 0. This means that your Output structure may be integer.
Please have a look at your Output Structure and make sure it the structure is really set to Text (string).
Regards,Bryan
no, like i said earlier, all my structure's attribute type is text, the problem is my entity's A field is a integer, so even my structure's attribute is text then it still convert the ' ' value to 0
Sorry but I don't think I get the whole picture of your issue. Are you using your entity in the Output Entities/Structures in the SQL Query?
Can you please share your OML? Or if that's not possible, please share some screenshots?
Thanks.
If I understand what your requirement is correctly, please have a look at the attached OML.
Have a look and see what you are missing or what you may have done in a different way.
Just offering a completely different perspective here, I would keep the data retrieval side simple, just query the data and return what is there, if it is a 0, then fine.
You have a requirement about how things are presented to the user in the UI, so solve it in the UI. The place where you show the value on the screen is probably an expression, so solve it there by doing giving it a value of
If(GetSomeData.NumericValue = 0, "", GetSomeData.NumericValue)
This is much easier, you can do it with a simple aggregate, but I also really think you should not solve display requirements in the data layer.
Dorine