Skip to Content

use values from scalar functions in HANA select

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Oct 22, 2019 at 09:59 AM

    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


    capture.jpg (28.1 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • Posted on Oct 23, 2019 at 12:50 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 18, 2019 at 08:04 PM
    -1
    select mandt,
           matnr,
           'abc' as constant1,
           '123' as constant2
    from MARA.   
      
         
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.