HANA Select from SAP BW Hierarchie with CalculationView

[Former Member]

Hello,

I want to select a Hierarchie from a SAP BW Hierarchie in a table with an CalculationView.

When I select only one Item in my first select (saleseng) everything works fine.

but when I want to fetch all Items I receive the failure "Error: SAP DBTech JDBC: [305]: single-row query returns more than one row".

In ABAP I would fix this issue with an loop, but how can I fix this error in SQL?

/********* Begin Procedure Script ************/ 

 BEGIN 

 saleseng = select "/BIC/PSD_FA" || "/BIC/SALESENG" as PSDFASALESENG 
 	from "SAPEBH"."/BIC/PSALESENG"
 	where "/BIC/PSD_FA" = '00001';


 var_out = select 
  nodename as SALESENG,

  right( 	 	
  (select nodename from "SAPEBH"."/BIC/HSALESENG" where NODEID = 
   (select parentid from "SAPEBH"."/BIC/HSALESENG" where NODEID =         (select parentid from "SAPEBH"."/BIC/HSALESENG" where NODEID = 
     (select parentid from "SAPEBH"."/BIC/HSALESENG" where nodename =        se.psdfasaleseng 		                                     and hieid = '0005PF0NDLY7G20J8L1R2OIBV') 
      and hieid = '0005PF0NDLY7G20J8L1R2OIBV') 
     and hieid = '0005PF0NDLY7G20J8L1R2OIBV') 
   and hieid = '0005PF0NDLY7G20J8L1R2OIBV'),5) as PSD_FA,

 right(			                                              
 (select nodename from "SAPEBH"."/BIC/HSALESENG" where NODEID = 
  (select parentid from "SAPEBH"."/BIC/HSALESENG" where NODEID = 
   (select parentid from "SAPEBH"."/BIC/HSALESENG" where nodename =        se.psdfasaleseng 
     and hieid = '0005PF0NDLY7G20J8L1R2OIBV') 
    and hieid = '0005PF0NDLY7G20J8L1R2OIBV') 
   and hieid = '0005PF0NDLY7G20J8L1R2OIBV'),5) as REGION,

 right(
 (select nodename from "SAPEBH"."/BIC/HSALESENG" where NODEID = 
  (select parentid from "SAPEBH"."/BIC/HSALESENG" where nodename = 
   se.psdfasaleseng
   and hieid = '0005PF0NDLY7G20J8L1R2OIBV') 
  and hieid = '0005PF0NDLY7G20J8L1R2OIBV'),5) as BRANCH,

  Count(NODENAME) as COUNT

 from "SAPEBH"."/BIC/HSALESENG" as h
 inner join :saleseng as se on
    psdfasaleseng = nodename
 where nodename = se.psdfasaleseng and 
       hieid = '0005PF0NDLY7G20J8L1R2OIBV'

 group by nodename, se.psdfasaleseng;


END /********* End Procedure Script ************/

[lbreddemann · Accepted solution]

The directly corresponding concept to "looping over internal tables" in ABAP would be "Cursor-For-Loop" in SQL script.

Looking at the approach above to resolve the hierarchy, I'd say, you will probably be better off by implementing a function that returns the necessary strings.

The "single-row..." error is due to the fact, that your subqueries don't simply return the first (and only the first) matching record from a join, but all matching records. So the WHERE condition with "hied = ..." is failing as it can only compare to single values.