Toad World® Forums

Script not running in Toad but in HANA Studio


#1

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


#2

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.


#3

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


#4

hi,

please post error message.


#5

there is no error message, only:

in Message Column : SAP Hana Database Error:

and this script runs in Hana Studio.


#6

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]


#7

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


#8

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


#9

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


#10

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