CODEWITHSUNDEEP
sqlamazon-redshift
sam
sam

Reputation: 873

column does not exist OR schema does not exist error in AWS Redshift

A working query needs changing to include a colunm from another table. Upon LEFT JOINing I get a 'column does not exist' error. Using the column instead of the column alias in the ON statement the first error turns into a 'schema does not exist' error.

This yields the 'column "a.audience_id" does not exist error'

        select
            criteria,
            trim(split_part(criteria, ':', 3))  as audience_id,
            t.category as audience_name,
            row_number() OVER (PARTITION BY trim(split_part(criteria, ':', 3)) ORDER BY date desc) as rownumber
        from
            client.adwords_insights_audience a
            left join core.googleads_inmarketaudience_xref x
            on a.audience_id = x.criterion_id
        group by
        criteria,
           trim(split_part(criteria, ':', 3)),
            t.category

And a change in the ON clause to the below yields the 'schema "a" does not exist' error

        select
            criteria,
            trim(split_part(criteria, ':', 3))  as audience_id,
            t.category as audience_name,
            row_number() OVER (PARTITION BY trim(split_part(criteria, ':', 3)) ORDER BY date desc) as rownumber
        from
            client.adwords_insights_audience a
            left join core.googleads_inmarketaudience_xref x
            on a.trim(split_part(criteria, ':', 3)) = x.criterion_id
        group by
        criteria,
           trim(split_part(criteria, ':', 3)),
            t.category

The original query (below) works perfectly. It's only when I LEFT JOIN that the errors arise.

        select
            criteria,
            trim(split_part(criteria, ':', 3))  as audience_id,
            row_number() OVER (PARTITION BY trim(split_part(criteria, ':', 3)) ORDER BY date desc) as rownumber
        from
            client.adwords_insights_audience a
        group by
        criteria,
           trim(split_part(criteria, ':', 3))

The 'date' column is ok as well, I removed it from this post along with several others for clarity. I'm hoping for the t.category column in the final output.

Upvotes: 0

Views: 6836

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I suspect that if you fix the table aliases, your query might work. The following is my best guess on where the columns come from:

select x.criteria,
       trim(split_part(x.criteria, ':', 3))  as audience_id,
       a.category as audience_name,
       row_number() over (partition by trim(split_part(x.criteria, ':', 3)) order by  ?.date desc) as rownumber
from client.adwords_insights_audience a left join 
     core.googleads_inmarketaudience_xref x
     on a.audience_id = x.criterion_id
group by x.criteria,
         trim(split_part(x.criteria, ':', 3)),
         a.category;

The ? is because I have no idea where date comes from.

Upvotes: 1

Related Questions