0

I have a query which is successfully running in HIVE database. This query have around 6 subqueries. I need to rewrite same query in SFMC SQL, so for the sake of testing purpose I just worked on two subqueries but it seems SFMC SQL does not support this? Or I am making any mistake here...Or is there is any work around in SFMC..

SELECT
       uuid,
       email_address,
       account_id as group_id,
from
(

SELECT
      distinct a.uuid,
        a.email_address,
        b.account_id as group_id,
        a.account_id
    from usca_restaurant_contact_v2 as a
    join usca_restaurant_contact_v2 as b on a.email_address = b.email_address
    ) exp_1

    (
    SELECT

    distinct a.uuid,
        a.group_id,
        a.account_id
    from exp_1 as a
    ) exp_2
  • your query is syntactically incorrect - e.g. there is actually no sub queries these queries are just sitting next to each other (need to use Union or Join, etc. to connect them). Please better explain what it is you want done and we can better help – Gortonington Aug 22 at 13:55
  • Yes the error was "An error occurred while checking the query syntax. Errors: exp_1 is not a known data extension or system data view. You can only query existing data extensions or system data views." – Sohail Aug 22 at 14:51
  • are you trying to combine exp_1 and exp_2 to be used for the 'top' query? – Gortonington Aug 22 at 14:53
  • No, Exp_1 is saving data in memory and then calling it in EXP_2 – Sohail Aug 22 at 14:56
  • 1
    what you have and what he has there is not related. You are running multiple select statements in a single query, which is not allowed. You CAN have nested subqueries for sure, but it can only be inside a single overarching select statement. – Gortonington Aug 22 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.