Skip to Content
author's profile photo Aleksandrs Frolovs
0
Aleksandrs Frolovs

use values from scalar functions in HANA select

Posted on Oct 18, 2019 at 03:42 PM 180 Views

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
    author's profile photo Kedar Acharya
    Kedar Acharya
    Posted on Oct 22, 2019 at 09:59 AM
    0

    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

    • Lars Breddemann
      Oct 22, 2019 at 10:50 PM

      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.

    Comment on This Answer
  • author's profile photo Aleksandrs Frolovs
    Aleksandrs Frolovs
    Posted on Oct 23, 2019 at 12:50 PM
    0

    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

  • author's profile photo Shanthi Bhaskar
    Shanthi Bhaskar
    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.