Script not running in Toad but in HANA Studio

Hi,

this script will not run in Toad but in HANA Studio:

select
x.“Payback period in year”,
case
when x.“Payback period in year” > 0 and x.“Payback period in year” <= 1.5 then ‘1’
when x.“Payback period in year” > 1.5 and x.“Payback period in year” <= 2.5 then ‘2’
else ‘x’
end
from TOP_INVESTMENTS x

And I get not error message / number in Toad from HANA

Holger

Hi,

There are two things which you can check.

  1. Is TOP_INVESTMENTS.Payback period in year numric? if yes then remove the ’ ’ from the then result, so the then result is numeric too.

  2. else ‘x’ is wrong. x represent a table and not a field. so change it to x.“Payback period in year” or a numeric value that rapresent others.

Hi, thx for your answer.

Payback is numeric.

I changed the script to:

select
x.“Payback period in year”,
case
when x.“Payback period in year” > 0 and x.“Payback period in year” <= 1.5 then 1
when x.“Payback period in year” > 1.5 and x.“Payback period in year” <= 2.5 then 2
else 0
end
from TOP_INVESTMENTS x

same error

hi,

please post error message.

there is no error message, only:

in Message Column : SAP Hana Database Error:

and this script runs in Hana Studio.

Hi,

Does a simple SQL statement work? such as …

select x.“Payback period in year”

from TOP_INVESTMENTS x

Try to rebuild table and any indexes associated with it.

then try to run SQL again.

Side Note: I’ve noticed that the field name contain spaces. Either replace spaces with underscores or make sure that the double quotes are valid to reference a space separated named fields. For example such field in SAP IQ database are enclosed by square brackets x.[Payback period in year]

select x.“Payback period in year” from TOP_INVESTMENTS x

works.

There is no error in HANA table,

it is an “error” from Toad.

THE FIRST SCRIPT RUNS IN HANA STUDIO, SAME DATABASE, SAME TABLE, SAME USERNAME.

I changed the script now into:

select
case
when ( x.“Payback period in year” between 0.01 and 1.5 ) then ‘1’
when ( x.“Payback period in year” between 1.51 and 2.5 ) then ‘2’
when ( x.“Payback period in year” between 2.51 and 3.5 ) then ‘3’
when ( x.“Payback period in year” between 3.51 and 4.5 ) then ‘4’
when ( x.“Payback period in year” between 4.51 and 5.5 ) then ‘5’
when ( x.“Payback period in year” between 5.51 and 6.5 ) then ‘6’
else ‘x’
end as “Payback (y)”,
x.“CAPEX (in kEUR)” / 1000 as “Project costs (Mio. € )”
from TOP_INVESTMENTS x
where
case
when ( x.“Payback period in year” between 0.01 and 1.5 ) then ‘1’
when ( x.“Payback period in year” between 1.51 and 2.5 ) then ‘2’
when ( x.“Payback period in year” between 2.51 and 3.5 ) then ‘3’
when ( x.“Payback period in year” between 3.51 and 4.5 ) then ‘4’
when ( x.“Payback period in year” between 4.51 and 5.5 ) then ‘5’
when ( x.“Payback period in year” between 5.51 and 6.5 ) then ‘6’
else ‘x’ end != ‘x’

and everything is fine.

Holger

select x.“Payback period in year” from TOP_INVESTMENTS x works.

This is an error from Toad.

THE FIRST SCRIPT RUNS IN HANA STUDIO, ON SAME DATABASE; SAME TABLE AND SAME USERNAME.

I changed the script into:

select
case
when ( x.“Payback period in year” between 0.01 and 1.5 ) then ‘1’
when ( x.“Payback period in year” between 1.51 and 2.5 ) then ‘2’
when ( x.“Payback period in year” between 2.51 and 3.5 ) then ‘3’
when ( x.“Payback period in year” between 3.51 and 4.5 ) then ‘4’
when ( x.“Payback period in year” between 4.51 and 5.5 ) then ‘5’
when ( x.“Payback period in year” between 5.51 and 6.5 ) then ‘6’
else ‘x’
end,
count(*) as “Number of projects”,
sum(x.“CAPEX (in kEUR)”) / 1000 as “Project costs (Mio. €)”,
avg(x.“CAPEX (in kEUR)”) / 1000 as “Average Project costs (Mio. €)”,
STDDEV_POP(x.“CAPEX (in kEUR)”) / 1000
from TOP_INVESTMENTS x
group by
case
when ( x.“Payback period in year” between 0.01 and 1.5 ) then ‘1’
when ( x.“Payback period in year” between 1.51 and 2.5 ) then ‘2’
when ( x.“Payback period in year” between 2.51 and 3.5 ) then ‘3’
when ( x.“Payback period in year” between 3.51 and 4.5 ) then ‘4’
when ( x.“Payback period in year” between 4.51 and 5.5 ) then ‘5’
when ( x.“Payback period in year” between 5.51 and 6.5 ) then ‘6’
else ‘x’ end

and everything is fine.

Holger

Hello,

We already have task QAT-9346 to fix the problem.

This seems to be caused by bug in SAP HANA client, which cannot process certain select statements containing longer case blocks.

Libor

Hello,

The problem is really caused by bug in the SAP HANA client. The issue appears if you have more than 6 case statements.

SAP is working on preparing of the fix.

As a workaround you can try to add "as " after the select statement, i.e.:

select
case

end as column_xyz
from …

Libor