How to Use Common table expression in Table functions

[former_member184624]

Dear HANA SQL Experts,

I want to use with table expressions in Table functions. For example,

FUNCTION FUNC_NAME(IN INPUT1, IN INPUT2)

RETURNS TABLE

(

FIELD 1

FIELD 2

FIELD 3

)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER AS

BEGIN

-- With Table Expression ---

TAB1 AS (

SELECT * FROM TABLE1

),

TAB2 AS (

SELECT * FROM TABLE2

)

SELECT * from TAB2

RETURN

How to pass TAB2 to table function Output ?

END;

[venkateswaran_k]

Hi

Can you give some example, Not able to understand fully your requirement

[CharlesFeng · Accepted solution]

Hi Jalina,

Could you please refer to refer to the following guide to find more details?
https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d42e77751910149f0ff6b... to find

Here is an example:

CREATE COLUMN TABLE "I311040"."CHARLES_TBL1" ("ID" INTEGER CS_INT NOT NULL ,

"NAME" VARCHAR(30),

"AGE" INTEGER CS_INT,

PRIMARY KEY ("ID")) UNLOAD PRIORITY 5 AUTO MERGE

CREATE FUNCTION FUNC RETURNS TABLE (ID INT, NAME VARCHAR, AGE INT) AS

BEGIN

RETURN SELECT * FROM CHARLES_TBL1;

END

Best regards,
Charles