TIMESTAMP type is incompatible with DOUBLE type

[former_member457065]

Hello all,

I'm writing some dynamic ORDER BY in SAP HANA Stored Procedure:

lt_sorted_results =
	select * from :lt_results
	order by 
	case
        when IN_ORDER_BY ='created-at'  then 
        CREATED_AT                 --this is timestamp column
        when IN_ORDER_BY ='distance' then 
        DISTANCE                   --this is double column
        when IN_ORDER_BY ='duration'  then 
        DURATION                   --this is double column
    end  ASC;

where IN_ORDER_BY is input parameter of type NVARCHAR.

I just came across this error when try to build this code:

Error: com.sap.hana.di.function: Database error 266: : inconsistent datatype: TIMESTAMP type is incompatible with DOUBLE type

If instead of double colums I put nvarchar columns, it builds without error:

lt_sorted_results =
	select * from :lt_results
	order by 
	case
        when IN_ORDER_BY ='created-at'  then 
        CREATED_AT                 --this is timestamp column
        when IN_ORDER_BY ='participant' then 
        PARTICIPANT                --this is nvarchar column
        when IN_ORDER_BY ='model'  then 
        MODEL                     --this is nvarchar column
    end  ASC;

[SergioG_TX]

so what is your question?

the error is correct... you are trying to order by 2 different data types.... do you get the desired output when you use nvarchar instead?

[KonradZaleski]

I think that this is because for the second case HANA is able to identify common data type and implicitly convert it (TIMESTAMP -> can be converted to VARCHAR).
In first case there is no common data type to which all columns could be converted (both TIMESTAMP -> DOUBLE and DOUBLE -> TIMESTAMP conversion makes no sense).
Try this:

lt_sorted_results =
           (SELECT * FROM :lt_results WHERE :IN_ORDER_BY ='created-at' ORDER BY CREATED_AT ASC)
              UNION
           (SELECT * FROM :lt_results WHERE :IN_ORDER_BY ='distance' ORDER BY DISTANCE ASC)                   
              UNION
           (SELECT * FROM :lt_results WHERE :IN_ORDER_BY ='duration' ORDER BY DURATION ASC) 

[former_member258165]

IN_ORDER_BY ='created-at' where IN_ORDER_BY is input parameter of type NVARCHAR - Please convert NVARCHAR to TO_DATE.