SELECT statement not working inside CONCAT-CodePudding

I am trying to run the below query in snowflake

with table1 as 
(
    select
        schema_name,
        schema_owner,
        created,
        last_altered
    from 
        information_schema.schemata
), secondtable as
(
    select 
        schema_name as InternalName 
    from 
        table1
)
select * 
from concat('"ITCAPTURE".'(select * from secondtable),'."VW_SLA_KPI"')

I get an error:

SQL compilation error: syntax error line 8 at position 20 unexpected '('. syntax error line 8 at position 78 unexpected ')'

What is wrong here?

CodePudding user response：

The FROM clause requires table, view, table function or derived view. CONCAT is a scalar function so a proper way to construct a string is as follow:

with table1 as 
(
    select
        schema_name,
        schema_owner,
        created,
        last_altered
    from 
        information_schema.schemata
), secondtable as
(
    select 
        schema_name as InternalName 
    from 
        table1
)
select concat('"ITCAPTURE".',secondTable.InternalName ,'."VW_SLA_KPI"') 
from secondtable;

CodePudding user response：

Syntax:

CONCAT ( string_value1, string_value2 [, string_valueN ] )

Your string_values are not properly delimited with ',' character.