Question Re: How to Use Common table expression in Table functions in Technology Q&A

How to Use Common table expression in Table functions

former_member184624 — Fri, 10 Jun 2022 11:34:13 GMT

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 — Fri, 10 Jun 2022 11:51:49 GMT

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

CharlesFeng — Tue, 21 Jun 2022 09:13:13 GMT

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