Incompatible data types

[cognos4321]

Hi All,

I am trying to create 2 Column with these conditions and it keeps giving me errors when the report is run. The report validates fine.
I understand that we can't compare 'String' with 'Date' or 'Numeric' data types which is why Iam trying to convert them.
Please let me know this is achievable by any means. Datasource: SQL Server

1st Column:
If ( cast ([Total], varchar(10)) = cast(100, varchar(10) ))

then ('Invalid')

else ( [Total])

2nd Column
If ( Cast ([FinalDate],varchar(10)) =  Cast (1900-01-01, varchar(10)) )

then ('Invalid')

else ( [FinalDate])

Thanks in advance

[adam_mc]

You need to convert the outputs to the same data type too.

For Column 1:

'Invalid' and [Total] must both be strings (as 'Invalid' is a String) or both be numeric (assuming that [Total] is numeric.

For Column 2:

'Invalid' and [FinalDate] must both be strings (as 'Invalid' is a String) or both be dates (assuming that [FinalDate] is a date.

Hope this helps.
Thanks,
Adam.

[cognos4321]

Hi Adam,

Thanks a lot for the quick response.

Column1

I tried to convert [Total] from numeric to string using varchar()

Column2

I tried to convert [FinalDatel] from Date to string using varchar()

Would you suggest any other way to convert them to String.

Thank you.

[adam_mc]

Can you post your entire calculations along with the error message you are getting.

Thanks,
Adam.

[cognos4321]

Basically I just want it to show 'Invalid' if the value in the Total Column is 100.
It is completely understandable that we can't compare 'Invalid' and 100 within an If statement because of differnet data types.
That's why I tried to make both of them as String using a varchar function.
If ( cast ([Total], varchar(10)) = cast(100, varchar(10) ))
then ('Invalid')
else ( [Total])
The error which I get after the 1st column added with the condition above is attached.

Thank you for your time.

[bus_pass_man]

1. 
i.  OK so let's go back to restating your requirement instead of getting into the weeds of the expression.

You want to test a column and if the value for a record is 100, you want the IF function to return whatever you've specified to be returned in the then clause of the expression.   If the value is not 100 then you want the value to be returned. 

ii.
Your expression will produce a string result if the test condition is true and a numeric result if it is false.  These results are incompatible.   

This will produce a result which is a string data type.

then ('Invalid')

This will produce a result which is a numeric data type.

else ( [Total])

Even if whatever you tried in the IF function's test section would work you would end up with an incompatible data type error.  I think this is what adam_mc was trying to point out to you.

iii.
You are fixating on test section of the IF function and missing that, which is a shame as you seem to be aware of the concepts of data types.

iv.  You need to understand what the business need is and what the trade-offs are for whatever approach you take to the problem.  The column you want to create would only work if the data types are compatible.   If your business need is to have that 'Invalid' string in it then you need to accept that the query item you are creating will need to be a string and that it subsequently cannot be a measure but only an attribute.   This might be perfectly acceptable for the business purpose.  The business need might require an entirely different approach to the problem.

v. 
I think something like this will work.  It will produce strings.
If ( Total] = 100)
then ('Invalid')
else (  cast ([Total], varchar(10))

vi.  Casting can be tricky.  You can end up with overflow errors or, far worse, it will work but, because you have not allocated enough space, the results will be truncated.   You really need to be aware of the size and precision of your data and what it could be in the future.   Plus you need to thing about signs if you're dealing those numeric data types which support that.

vii. Oh yeah, forgot this.   When you're dealing with strings and the equal operator you need to enclose any value in single quotes.

cast ([Total], varchar(10)) = '100'

2.  This is Anno Domini 2019.  Just out of curiosity, why are you still using compatible mode?

[the6campbells]

IF-THEN-ELSE is an alternative way of expressing CASE.

CASE
  WHEN condition then <return-expression>
  ...
  WHEN condition then <return-expression>
  ELSE  <return expression>
END

The requirement is that the data types of all the <return-expressions> be of compatible types. In your case, you are mixing non-compatible types i.e. strings and numbers.

Keep in mind, CAST is a data type conversion where there is no concept of user controlled formatting. The character representation of the source value (i.e .date, integral, decimal, approximate numeric) will be in the form defined by the ISO-SQL standard. Some database vendors offer their own type conversion functions which some may offer a means to format. You should always clarify what local awar formatting they may support etc.

If you are producing a report, then potentially you should be looking at how to conditionally render the display of the data.

Keep in mind, once the data is type cast, subsequent operations using that type-cast value will reflect a string value and not a numeric value (i.e. sorting, comparions etc).

So in summary, CAST to VARCHAR(n) may provide you a "variant" data type but intrinsically it is a character type with the cavaets noted.

[cognos4321]

Thanks a lot bus_pass_man and the6campbells for your response.
Sorry for my delayed reply since I was sick for last week.

I completely understand that we can't compare 2 different data types using an IF or CASE statement.
It's just that I was trying to confirm if there is a way to achieve this.

The business just needs a string saying 'Invalid' wherever the value is '100' in the column and they are reluctant in making any changes on the database side which is SQL server.


Thanks again for your time.

[adam_mc]

In your calculation, instead of returning the value 'Invalid' when the value is 100, return a Null value.
This will make the calculation valid.

Now you need to change the output from Null to 'Invalid'.
So, for that column in your list, in the Properties pane, go to the Data > Data Format.
Set the Format Type to Number and then set the the "Missing Value Characters" to 'Invalid'.
This should give you what you want.

Hope this helps.
Adam

[cognos4321]

Thanks you adam_mc.

I tried that in the past before posting the issue here.
But it gave me 'Invalid' for the already blank fields also.
Meaning in the new column the cell comes blank if the value is 100 or if the value was blank in the column it's pulling from.
Now if we change the property to number and update the value for 'Missing Value characters' as 'Invalid', it would show invalid from both blanks and 100s while we want it only where value is 100.

Seems confusing, not sure if I was able to explain that.

Thanks a lot, anyways

[adam_mc]

I don't know what you final calculation looks like now, but change it to be something like:

CASE
WHEN [Total] is null then ' '
WHEN [Total] = 100 then null
ELSE cast([Total as <varchar as appropriate length>)
END

Then do the same formatting as I suggested in previous post including the missing characters piece.
Now, the output of that column should be a text field rather than a number, but it should work in the same way.

Now, your Totals of 100 should come up as 'Invalid', your previously "blank" (actually null) values should show as a space, and your wanted Totals should have values.

Hope this helps.
Thanks,
Adam. 

[cognos4321]

Thanks adam_mc.

In this statement when we say then ' ' it treats it as string and we get the same error for incompatible data types.

CASE
WHEN [Total] is null then ' '
WHEN [Total] = 100 then null
ELSE cast([Total as <varchar as appropriate length>)
END

[bus_pass_man]

Try this

CASE
WHEN [Total] is null then NULL
WHEN [Total] = 100 then NULL
ELSE cast([Total as <varchar as appropriate length>)
END

or this

CASE
WHEN [Total] is null then ''
WHEN [Total] = 100 then null
ELSE cast([Total as <varchar as appropriate length>)
END

I suspect that '' will still be a problem.

[adam_mc]

Yes, you will need to have all outcomes from the calculation be strings.

' ' is a string by default
Null should be OK.
Then, you will need to cast your [Total] as a string too - Something like cast([Total], varchar(10)) - I don't know your database so I can't help you with the exact syntax.

If this doesn't work, look at the other options in the Data Format properties.
There may be some other choice other than "Missing Value Characters" that you can use.
For example, it your data contains no zero values (only values <> 0 and nulls) you can force a zero into the calculation where [Total] = 100 and then set the "Zero Value Characters" to 'Invalid'.
There are a number of options hopefully one can satisfy for need.

Hope this helps,
Adam.