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
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
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’
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