use values from scalar functions in HANA select

[former_member622118]

Hi,

I need to show several constant in multiple SQL selects. These constants are derived from a table that contains one record only.

How to pick up these constants from HANA procedures or functions if scalar function doesn't allow any SQL operations.

SELECT MANDT, MATNR , Costant01, constant02

FROM "SAPSR3"."MARA"

Thanks

[former_member631142 · Accepted solution]

Hi Aleksandrs,

Please try with below statement using cross join (1=1 condition). It will help you to take constant from setup table.

DO BEGIN
IT_MARA = SELECT 100 AS MANDT, '10-10' AS MATNR FROM DUMMY
UNION ALL
SELECT 100 AS MANDT, '10-20' AS MATNR FROM DUMMY
;

IT_SETUP = SELECT 'AA' AS Costant01, 'BB' AS constant02 FROM DUMMY;

SELECT MARA.*, SETUP.* 

FROM :IT_MARA AS MARA
JOIN :IT_SETUP AS SETUP
ON 1 = 1
;
END

Output will be as below in the attached image:

capture.jpg

[former_member622118 · Accepted solution]

Thanks a lot

If there is a chance to perform this action (derive single record from setup table) via procedure or any kind of function ?

[former_member631142 · Accepted solution]

Hi Aleksandrs,

Yes, that is possible through SQL statement. If you can let me know sample dummy setup data, I can help you more to provide details.

Regards

Kedar

[lbreddemann · Accepted solution]

Instead of the 1=1 "trick" one could also just use the correct SQL join for this:

CROSS JOIN

SELECT /* SELECT * really shouldn't be used */
       MARA.<col_1>, MARA.<col_2>, MARA.<col_3, MARA.<col_...>
     , SETUP.<col_1>, SETUP.<col_2>, SETUP.<col_3>, SETUP.<col_...> 
FROM
           :IT_MARA AS MARA
CROSS JOIN :IT_SETUP AS SETUP;

Note that the cross join will create a result tuple for every combination of tuples in both tables. That may not be what the OP intended.

[former_member622118]

Thanks Lars

The parameters from setup table are client dependent. I would like to pick them up via function/procedure to avoid join

Table MARA client dependent

Select MANDT, MATNR, procedure01() AS CLIENT_ID, procedure02 as CO_AREA

Parameters from table param - client dependent

MANDT CLIENT_ID CO_AREA

[lbreddemann]

The join would be the faster option - not sure why you want to avoid it.

[shanthi_bhaskar]

select mandt,
       matnr,
       'abc' as constant1,
       '123' as constant2
from MARA.   
  
     

[former_member622118]

Thanks, unfortunately , constants should be taken from setup table as they can be changed and are used across multiple places.

Hardcoding is nota good option.