Skip to main content
Solved

Invalid offset column name

  • March 18, 2022
  • 1 reply
  • 331 views
Pradeep
StreamSets Employee
Forum|alt.badge.img+1
  • PradeepStreamSets Employee
  • StreamSets Employee
  • 48 replies

Let us say we have a dataset like below. Requirement is to use JDBC Query Consumer Origin to load id, title, ratings based on whatever is the latest from created_date or updated_date. It means we want to do incremental load but not on primary key ‘id’.

 

popular_movies

Whenever the pipeline runs it should pull in the updated ratings with other fields. In order to incrementally load data based on our requirement we have written query like below.

SQL Query

Query is failing by saying Invalid column name ‘offset_date’ though it is valid per SQL when we do validation. 

Best answer by Pradeep

Solution: We can derive the offset column by writing it in inner query and projecting the needed columns from the inner query’s alias.

select * from
(SELECT id, title, ratings, COALESCE(created_date, updated_date) as offset_date
FROM popular_movies ) as pm 
WHERE pm.offset_date > ${OFFSET} ORDER BY pm.offset_date

This kind of solution is also helpful in cases where we do not have a primary key and need to derive a key using multiple column combinations(composite key) for incremental loads.

Note: Invalid column name error can also occur if incorrect or non-existent column is referred in the query.

View original
bonthunagireddy03
Mel
Pablo
3 people like this
  • bonthunagireddy03
    bonthunagireddy03
  • Mel
    Mel
  • Pablo
    Pablo
Did this topic help you find an answer to your question?

1 reply

Pradeep
StreamSets Employee
Forum|alt.badge.img+1
  • PradeepStreamSets Employee
  • Author
  • StreamSets Employee
  • 48 replies
  • Answer
  • March 24, 2022

Solution: We can derive the offset column by writing it in inner query and projecting the needed columns from the inner query’s alias.

select * from
(SELECT id, title, ratings, COALESCE(created_date, updated_date) as offset_date
FROM popular_movies ) as pm 
WHERE pm.offset_date > ${OFFSET} ORDER BY pm.offset_date

This kind of solution is also helpful in cases where we do not have a primary key and need to derive a key using multiple column combinations(composite key) for incremental loads.

Note: Invalid column name error can also occur if incorrect or non-existent column is referred in the query.

Reply


Terms & Conditions