on 10-18-2012 3:27 PM
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 ?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.