Why is ‘a1’ added at the end of sql query in json config file meant for data extraction from NetSuite? Not adding that can cause error?

I am working on extracting data from Netsuite into Databricks File Storage. I am doing it by scripting a json and executing it through Azure Data Factory . This JSON has a part where you specify the query to use when extracting from Netsuite. It is called “incrementalSql”. I see quite often that here, an ‘a1’ is added at the end.

"incrementalParams": {
                    "checkpointTablePath": "dbfs:/mnt/data/governed/l1/audit/log/checkpoint_log/",
                    "extractId": "NETSUITE_CURRENCY_EXCHANGE_RATE",
                    "incrementalSql": "(select b.NAME as BASE_CURRENCY_CD, c.NAME as CURRENCY_CD, a.EXCHANGE_RATE, a.DATE_EFFECTIVE from Administrator.CURRENCY_EXCHANGE_RATES a left join Administrator.CURRENCIES b on a.BASE_CURRENCY_ID = b.CURRENCY_ID left join Administrator.CURRENCIES c on a.CURRENCY_ID = c.CURRENCY_ID) a1",
                    "maxCheckPoint1": "(select to_char(max(DATE_EFFECTIVE), 'DD-MM-YYYY HH24:MI:SS') from Administrator.CURRENCY_EXCHANGE_RATES where DATE_EFFECTIVE > to_date('%%{CHECKPOINT_VALUE_1}', 'YYYY-MM-DD HH24:MI:SS'))"
                }

Notice the a1 at the end of the incrementalSql field.

Does anyone know why it is added? Is it only added in case of NetSuite or Oracle only? Not adding it can cause error?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

As stated in comment by @MT0, Assumption is true. a1 at the end of the incrementalSql field is an alias to refer to sub-query which is dynamically embedded.

Not adding aliase will cause an error. Subquery is kind of derived table. So to refer to that alias is necessary.

If alias is not added you may get error like Error – Alias Required to Avoid Duplicate Columns

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0