1

In my sql in SFMC i am trying to get table name from another table in SELECT query but it seems to be not working, considering declaring variables in not allowed in SFMC SQL

I have tried this but no luck

SELECT * from (select de_name from ENT.DISTINCT_DE_TABLE) WHERE email = '[email protected]'

I have tried this as well, still no luck

With TableVariable As (
   SELECT DE_NAME FROM ENT.DISTINCT_DE_TABLE
   )

SELECT * FROM from mytable where mytable = (SELECT DE_NAME FROM With TableVariable) WHERE email = '[email protected]'

Another approach - no luck

SELECT x.* 
FROM
 (
    SELECT de_name 
    FROM ENT.DISTINCT_DE_TABLE
 ) AS x
WHERE email = '[email protected]'

Any advise on how to go about it?

NOTE: email field does not exist in ENT.DISTINCT_DE_TABLE, it exists in the table that will be pulled from ENT.DISTINCT_DE_TABLE as DE_NAME.

UPDATE - Adding table schemas here for more clarity on my question

- DISTINCT_DE_TABLE scheme (this table only holds the audience list names, nothing else):

FieldName Data Type
DE_Name text

- Records in DISTINCT_DE_TABLE look like this:

DE_NAME
NewsletterEmail_audienceDE_Table
CouponEmail_audienceDE_Table
OrderEmail_audienceDE_Table

- Example of audience DE schema (picking any random DE):

FieldName Data Type
email emailaddress
c_id int
name text

- Sample data in audience DE

email c_id name
[email protected] 101 John Lee
[email protected] 102 Mike H
[email protected] 103 Lee Jr

1 Answer 1

1

You need to provide an alias if using a subquery. Below I have aliased as "x". It is also recommended to specify the column names instead of using shorthand "*".

SELECT x.[column01], x.[column02], x.[column03] 
FROM
 (
    SELECT [column01], [column02], [column03]
    FROM ENT.[DISTINCT_DE_TABLE]
 ) AS x
WHERE x.[column02] = 'abc'

although the above can simplified to this:

SELECT [column01], [column02], [column03]
FROM ENT.[DISTINCT_DE_TABLE]
WHERE [column02] = 'abc'
6
  • Hi charliefay thank you for the subquery, i missed one important aspect of my query in my question which i just updated, i have a where clause in the query in which the criteria used the field that exist in the table name that is being pulled (NOT in the table ENT.DISTINCT_DE_TABLE). any advise on that?
    – kl2
    Jul 3, 2022 at 16:10
  • In your revised code you are using email column which is undefined since in the subquery you are only selecting a single field called de_name. So only this column is available in the outer query where clause.
    – charliefay
    Jul 3, 2022 at 21:44
  • Hi @charliefay my apologies if I wasn't clear in my question, I have added table schema and sample data of tables I am working with. The field in where clause DOES NOT exist in DISTINCT_DE_TABLE which is my issue. DISTINCT_DE_TABLE only holds the DE names. email field exist in the actual DE. Hope that helps in clarity.
    – kl2
    Jul 5, 2022 at 19:54
  • Hi charliefay - I just wanted to follow up if you got a chance to review my response / update?
    – kl2
    Jul 7, 2022 at 21:10
  • I think I understand now. You want to change the FROM table name based on a lookup to the DISTINCT_DE_TABLE. In this case, it is not valid SQL to use a subquery. You will need to use Server Side Javascript to construct the sql text dynamically.
    – charliefay
    Jul 14, 2022 at 6:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .