If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Cast a data item in a filter

Started by cbyrne, 12 Sep 2014 10:43:39 AM

Previous topic - Next topic

cbyrne

Hi all

I have a problem where i am getting an error when filtering on a data item.  Below is my data item, so i want ot show in my list report an entered date for adjustments that are greater than 0 basically else 2000-01-01.


My filter is below this case statement where i want to only show this the below case statement which is lesser or greater than '2000-01-01'

I have also included my error and i have tried casting using many different casting functions.

 


My case statement called KMS Maintenance Date
CASE WHEN ([E-code]= 'C072') THEN ([ENTERED DATE])
WHEN ([Item Code]not in('ABS','HBS','HDS','MH','ML','NWS','ORM','RAR20','SLB','SSH','TLC','WRS','CJ','EW','SLC','HJ','BL','MRB','SLC','CTL','HSW','MRB','SLC')and[Item Code]not starts with 'F' and [Item Code]not in('FLT','VAN','CAR','AR','TRAIL')  and [Adjustment 1] <> 0 and [DATE] <=current_date) THEN ([ENTERED DATE])
WHEN ([Item Code]not in('ABS','HBS','HDS','MH','ML','NWS','ORM','RAR20','SLB','SSH','TLC','WRS','CJ','EW','SLC','HJ','BL','MRB','SLC','CTL','HSW','MRB','SLC')and[Item Code]not starts with 'F' and [Item Code]not in('FLT','VAN','CAR','AR','TRAIL')  and [Adjustment 5] <> 0) THEN ([ENTERED DATE]) ELSE ('2000-01-01') END



My Filter
[KMS Maintenance Date]<> '2000-01-01'


My Error
UDA-SQL-0460 A general exception has occurred during local processing.
UDA-EE-0094 The operation "less_equal" is invalid for the following combination of data types: "varchar" and "date2"


Thanks in Advance

Chris

squish88

Sounds like the "[DATE]" in your "and [DATE] <=current_date" part of the case statement needs to be cast-ed to a date so that its data type matches what's being returned by "current_date"....  or vice versa I s'pose.