Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Variable in Oracle Database Quey

I am new to this product as am still in the trial mode. But want to use the product as it suites our needs and price point.


I have a variable LastHistoryDate which gets its value from a table iteration component.

Just to try to get something working, before using it on the bigger transformation job, I wanted to write the output to a field in a table so I just created a simple POC job.

so using the database query component and entering select '${LastHistoryDate}' from dual in SQL I ran the job

This did create a field but with the value ${LastHistoryDate} and not the date.

I finally managed to get the entry into the table by

Creating a JobVariable assigning ${LastHistoryDate} to a variable called HistoryDate

using select '${HistoryDate}' from dual I get the time stamp entry. I now want to convert this to DDMMYY do that I can use it in a where clause in a full piece of SQL.

The issue is that it does not matter what I try

to_char, to_date even '${HistoryDate.format("yyyy-mm-dd"}'

either does not work and the job errors or you just end up with the literal $ string and not the variable that has been found.


4 Community Answers

Matillion Agent  

Paul Johnson —

Hi Alistair,

I’m not totally sure on what you are trying to do, perhaps you can help me to understand.

Is it the case you want to select a value in a source table (using database query component) and pass this into a job variable to be used in a subsequent database query component?

However when referencing a job variable in the SQL statements you don’t quote them. Have you tried TO_CHAR(${HistoryDate}, ‘DDMMYY’)

Regards,
Paul


Alistair Henderson —

Hi Paul

I want to do partial loading i.e. only yesterdays records. I therefore will get the last date of the records in my main table and pass this to the variable.

I then wish to use this variable in my SQL statement. I started an failed, so I just created a test job to work out the fundamentals of how to get the date, manipulate it so I can use the same method in the larger job.


changing the SQL to select to_char(${HistoryDate},'DDMMYY') var from dual

errors with ORA-00907 missing right parenthesis


for you info select ${HistoryDate} var from dual does not work

errors with ORA 00923 FROM keyword not found where expected

but

select '${HistoryDate}' var from dual

works and writes the datestamp to the table








Matillion Agent  

Paul Johnson —

Hi Alistair,

I think in this rather than try to unpick the syntax over email it might be better to have a call?

I have done something very similar to what you are trying to achieve by adding a load_date column on my initial load, then grabbing this field and using it as a filter on subsequent loads to only grab records since the last load. Happy to show you this if that will help.

We also have a tutorial on building an incremental load job.

Let me know your availablity

Regards,
Paul


Matillion Agent  

Paul Johnson —

Hi Al,

Thanks for the screen share, we determined the issue is the datetime format in redshift is not a valid value to use in the Oracle query.

I suggest getting this into the format you want in a transformation job so the value in redshift can be used in the variable.
As an alternative to using the iterator with a view to get the high water mark you could use a python script if you needed to manipulate the date in the orchestration job rather than in the transformation job.

To do that you would use the following code
cursor = context.cursor()
cursor.execute(‘select max(date_loaded) from table’)
last_load_date = cursor.fetchone()0

Regards,
Paul

Post Your Community Answer

To add an answer please login