Question Re: Using client addition in SQL select in Technology Q&A

Using client addition in SQL select

Former Member — Wed, 16 Jan 2019 17:31:44 GMT

Hello,

I am trying to use USING CLIENT addition for specifing client for view but it return me this error. Please could you help me?

select * from SAPABAP1.IMATERIALSTOCK using client '200'

Error:

Could not execute 'select * from SAPABAP1.IMATERIALSTOCK using client '200''
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "using": line 1 col 39 (at pos 39)

I need to do it because this CDS view is @ClientHandling.algorithm: #SESSION_VARIABLE

It returns empty table when client is not specified in select.

Re: Using client addition in SQL select

pfefferf — Wed, 16 Jan 2019 18:46:48 GMT

As you are using the schema name in your select I assume that you execute it directly on DB level and not on the ABAP level. The "using client" addition is only available on the ABAP layer. On DB layer you just have to add a where condition (... where client = '200') if you wanna restrict the data set to a specific client.

Re: Using client addition in SQL select

Former Member — Thu, 17 Jan 2019 16:36:53 GMT

Unfortunately "where" is not working in case @ClientHandling.algorithm: #SESSION_VARIABLE

Point is that I would like to use existing CDS views prepared by SAP, but it expect client to be specified in session variable, what I don't know how to do it calling this CDS view directly from database or from Microsoft PowerBI for example.

Maybe another possibility is to copy existing CDS view and specify client this new custom view so it will not take it from session variables... do you have any advice?

For example this is very nice movement view: I_GoodsMovementCube that I would like to access directly from database or MS Power BI.

Re: Using client addition in SQL select

pfefferf — Thu, 17 Jan 2019 18:09:19 GMT

Please would it be possible to clarify if you are working on the ABAP Layer or directly on DB layer or something else. In your question it sounded you try it from DB layer directly (mixed with ABAP), now you saying you wanna do it with PowerBI. What is your final goal?

Re: Using client addition in SQL select

Former Member — Fri, 18 Jan 2019 22:00:43 GMT

florian.pfeffer

I am using connection from Power BI application but the same problem I have when trying to connected to view from our SQL server.

Example for Power BI:

Here are I am calling predefine CDS view for storage location list. This view has annotation @ClientHandling.algorithm #SESSION_VARIABLE. So it expected client to be specified for connection session. For all such a CDS views i get empty table in Power BI or calling them from SQL server. (My own views working perfectly). Yes, I can write my one CDS views, but there is so many nice views already predefine in HANA database, so I really would be happy to find way to use them. Thank you for any advice.