cancel
Showing results for 
Search instead for 
Did you mean: 

Issue with using select in column list

Former Member
0 Kudos

Hi,

I wanted to know weather HANA has a limit on the maximum number of nested select statements in the column list.

SELECT

"ASSOCID",

(select count(*) from (select IRECID from "_SYS_BIC"."TEST/AV_RESPONSE" as sub where sub.ASSOCID=main.ASSOCID group by IRECID)) As Counters,

--(select count(*) from (select IRECID from "_SYS_BIC"."TEST/AV_RESPONSE" as sub where sub.ASSOCID=main.ASSOCID group by IRECID)) As

Counters2,

(select case sum("IDENOMINATOR") WHEN 0 then NULL ELSE round(sum("INUMERATOR") /TO_double(sum("IDENOMINATOR")),1) end AS "SCORE"

from "_SYS_BIC"."TEST/AV_RESPONSE" as sub

where sub.ASSOCID=main.ASSOCID and "VCHANNELCD"='PHN'  and VSHORTNAME='Q1' and ATR_DIMCALLDATE_MONTHNUMBEROFYEAR= 10  and CALENDARYEAR= 2011) As Q1,

(select case sum("IDENOMINATOR") WHEN 0 then NULL ELSE round(sum("INUMERATOR") /TO_double(sum("IDENOMINATOR")),1) end AS "SCORE"

from "_SYS_BIC"."TEST/AV_RESPONSE" as sub

where sub.ASSOCID=main.ASSOCID and "VCHANNELCD"='PHN'  and VSHORTNAME='Q2' and ATR_DIMCALLDATE_MONTHNUMBEROFYEAR= 10  and CALENDARYEAR= 2011) As Q2,

(select case sum("IDENOMINATOR") WHEN 0 then NULL ELSE round(sum("INUMERATOR") /TO_double(sum("IDENOMINATOR")),1) end AS "SCORE"

from "_SYS_BIC"."TEST/AV_RESPONSE" as sub

where sub.ASSOCID=main.ASSOCID and "VCHANNELCD"='PHN'  and VSHORTNAME='Q3' and ATR_DIMCALLDATE_MONTHNUMBEROFYEAR= 10  and CALENDARYEAR= 2011) As Q3,

(select case sum("IDENOMINATOR") WHEN 0 then NULL ELSE round(sum("INUMERATOR") /TO_double(sum("IDENOMINATOR")),1) end AS "SCORE"

from "_SYS_BIC"."TEST/AV_RESPONSE" as sub

where sub.ASSOCID=main.ASSOCID and "VCHANNELCD"='PHN'  and VSHORTNAME='Q4' and ATR_DIMCALLDATE_MONTHNUMBEROFYEAR= 10  and CALENDARYEAR= 2011) As Q4,

(select case sum("IDENOMINATOR") WHEN 0 then NULL ELSE round(sum("INUMERATOR") /TO_double(sum("IDENOMINATOR")),1) end AS "SCORE"

from "_SYS_BIC"."TEST/AV_RESPONSE" as sub

where sub.ASSOCID=main.ASSOCID and "VCHANNELCD"='PHN'  and VSHORTNAME='Q5' and ATR_DIMCALLDATE_MONTHNUMBEROFYEAR= 10  and CALENDARYEAR= 2011) As Q5,

(select case sum("IDENOMINATOR") WHEN 0 then NULL ELSE round(sum("INUMERATOR") /TO_double(sum("IDENOMINATOR")),1) end AS "SCORE"

from "_SYS_BIC"."TEST/AV_RESPONSE" as sub

where sub.ASSOCID=main.ASSOCID and "VCHANNELCD"='PHN'  and VSHORTNAME='Q6' and ATR_DIMCALLDATE_MONTHNUMBEROFYEAR= 10  and CALENDARYEAR= 2011) As Q6,

(select case sum("IDENOMINATOR") WHEN 0 then NULL ELSE round(sum("INUMERATOR") /TO_double(sum("IDENOMINATOR")),1) end AS "SCORE"

from "_SYS_BIC"."TEST/AV_RESPONSE" as sub

where sub.ASSOCID=main.ASSOCID and "VCHANNELCD"='PHN'  and VSHORTNAME='Q7' and ATR_DIMCALLDATE_MONTHNUMBEROFYEAR= 10  and CALENDARYEAR= 2011) As Q7,

(select case sum("IDENOMINATOR") WHEN 0 then NULL ELSE round(sum("INUMERATOR") /TO_double(sum("IDENOMINATOR")),1) end AS "SCORE"

from "_SYS_BIC"."TEST/AV_RESPONSE" as sub

where sub.ASSOCID=main.ASSOCID and "VCHANNELCD"='PHN'  and VSHORTNAME='Q8' and ATR_DIMCALLDATE_MONTHNUMBEROFYEAR= 10  and CALENDARYEAR= 2011) As Q8

--,

--(select case sum("IDENOMINATOR") WHEN 0 then NULL ELSE round(sum("INUMERATOR") /TO_double(sum("IDENOMINATOR")),1) end AS "SCORE"

--from "_SYS_BIC"."TEST/AV_RESPONSE" as sub

--where sub.ASSOCID=main.ASSOCID and "VCHANNELCD"='PHN'  and VSHORTNAME='Q9' and ATR_DIMCALLDATE_MONTHNUMBEROFYEAR= 10  -----and CALENDARYEAR= 2011) As Q9

FROM "_SYS_BIC"."TEST/AV_RESPONSE" as main

This would work fine but if uncomment the part in red the query fails saying "SAP DBTech JDBC: [7]: feature not supported: not allowed over OLAP VIEW : search without aggregation or grouping by expression other than view column". If I remove one of the column queries an then execute it works. Now I guessed that there must be some maximum number of nested select statements in HANA but that is not the case, As if I uncomment the part in  green and have the part in red commented it works. I cannot understand this behaviour ?

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member383678
Discoverer
0 Kudos

Hi Muhammad,

Please follow this thread which has the same error as the one that you experience

http://scn.sap.com/message/13569081

Thanks,

dani

Former Member
0 Kudos

Hi Yordan,

I am afraid but I don't think that I am facing the same issue. I am using the group by clause and aggregate functions but some how when I have more than a certain number of columns (by nested selects) the query throws exceptions. If I run the same query in parts it work perfectly. But thanks for your efforts.

Regards

Moaz

former_member383678
Discoverer
0 Kudos

Hi Moaz,

Not that this will solve your problem but at least you will have the answer of your question about the restrictions. Check this : http://help.sap.com/hana/html/_isql_restrictions_for_sql_statements.html

Regards,

Yordan