How to cast expression in select clause with high performance

Question

I have a table and want to convert a column of type decimal to an integer based on a condition. I need to do this as performant as possible.

My currently working query looks like this: select *, (case when C_CUSTKEY < 20 then 1 else 0 end) as bit from TPCH.CUSTOMER

However I'm trying to improve this. Is it possible to do the conversion on the fly, e.g. something like this: select *, cast((C_CUSTKEY < 20) as integer) as bit from TPCH.CUSTOMER Or even for a simpler version like: select *, (C_CUSTKEY < 20) as bit from TPCH.CUSTOMER

No matter what functionality I use, I always get the following error: sql syntax error: incorrect syntax near "<"

Update 1

So for better understanding an actual usecase of what I want to do is as following:

select ( (case when col1 < x then 1 else 0 end) + (case when col2 > y then 2 else 0 end) ) as bitset from TPCH.CUSTOMER

In general there can be a large number of 'case when' expressions (>100).

Now the first problem is that the expression is very slow and I need to improve performance so I wanted to know if its possible to to the conversion on the fly smth like 2 * to_int(col2 > y) but I cannot find a way to do this.
Second problem is when I have many 'case when' expressions then I get the following error: SQL internal parse tree depth exceeds its maximum: parse tree depth exceeds its maximum:255

score 0 · Answer 1 · answered Mar 14 '17 at 00:15

0

Not quite sure about the use case here. Generally speaking, you cannot have a different data type for the same column at the same time. That's a fundamental constraint of the relational data model.

If however, the requirement merely is to have an indicator for "value in C_CUSTKEY < 20" then using the CASE statement is a straightforward way to do this. In order to avoid this evaluation at query time, you could create a calculated column for this.

alter table TPCH.CUSTOMER add ("BIT" integer 
                                generated always as 
                                case 
                                  when ("C_CUSTKEY" < 20) 
                                  then 1 
                                  else 0 
                                end);

Concerning the syntax errors: well, you're just not using the correct syntax. Check the example above for a correctly working example.

answered Mar 14 '17 at 00:15

Lars Br.

9,949
2
15
29

that's what I'm currently doing but I actually don't want to materialize the BIT column. I'm also aware that this is not the correct syntax because (field < value) is not supported in select clause, that's why I asked if there is a way to do this. – Jan Mar 24 '17 at 09:14
@Jan the example I posted does work on SPS11 just as posted. If you don't want to materialise it, simply use the case condition in the projection part of your select command. – Lars Br. Mar 24 '17 at 09:24
so you mean as I wrote it in the question? That's too slow for my purpose. I need to improve the performance of the 'case when' expression, e.g. if i have ```(case when ("C_CUSTKEY" < 20) then 1 else 0 end) + (case when ("C_CUSTKEY" > 40) then 2 else 0 end) + ...``` with many more 'case when' expressions it'll be very slow. – Jan Mar 24 '17 at 12:20
You might be better off describing your full requirement here instead of coming back with additional conditions and constraints after I spent time and effort on your question. – Lars Br. Mar 24 '17 at 12:31
The question contains my full requirement. It's just the basic usecase of what I need. The core question is still the same but I will extend the question later today. – Jan Mar 27 '17 at 12:08

How to cast expression in select clause with high performance

1 Answers1