I have a loop I've created using a system calendar to output start and end dates for a query that is set up using the import wizard. The loop dataset variable name is Period and the query result columns are named startdate and enddate. In the import wizard predicate I have specified :Period.startdate and :Period.enddate but TDP isn't recognizing the parameter and is returning: ORA-01858: a non-numeric character was found where a numeric was expected.
Are either of these syntactically correct and should the import wizard object be able accept these values?
between to_date(':Period.startdate','MM-DD-YYYY') AND to_date(':Period.enddate','MM-DD-YYYY') - Returns ORA-01858: a non-numeric character was found where a numeric was expected
OR
between to_date('#Period.startdate#','MM-DD-YYYY') AND to_date('#Period.enddate#','MM-DD-YYYY') - Returns ORA-00907: missing right parenthesis
First thing that comes to mind... try getting rid of the quotes around column names or variables.
e.g.
BETWEEN to_date(Period.startdate,'MM-DD-YYYY') AND to_date(Period.enddate,'MM-DD-YYYY')
should give you what you need.
Also, bind variables should work in this scenario too, in case that's what you had in mind.
e.g.
BETWEEN to_date(:InputStartDate,'MM-DD-YYYY') AND to_date(:InputEndDate, 'MM-DD-YYYY')
Thank you I will try both. I believe I have tried it without quotes, but with dates it seems to create some kind of conversion. I've even seen TO_DATE appear in the physical output which was not added by myself.
With the colon the following occurred: ORA-01008: not all variables bound
The dataset variable is Period and the query is returning a start and end date from Teradata. The loop object binds them so I'm not sure why the error code.
select min(calendar_date) as startdate, max(calendar_date) as enddate
from SYS_CALENDAR.CALENDAR
where year_of_calendar = 2019
group by month_of_year
order by startdate
There is no problem in the overall process execution including initiating the import template until those date values are referenced which is being passed to Oracle.
Using the pound signs I receive the following: ORA-01810: format code appears twice
Originally the BETWEEN was written as follows and worked fine:
BETWEEN to_date('01-01-2019','MM-DD-YYYY') AND to_date('01-31-2019','MM-DD-YYYY')
I'm currently using TDP v5.0.7.106 (64bit).
Just upgraded to v5.1.2.166 (64bit).
Same thing occurred after upgrading.
Hmmm, I wonder if Dev can chime in, and we may need more details on your import definition... something going on with how the import processes variables?
BTW, is this Oracle to Oracle? or Teradata to Oracle? Wondering if the date format needs to be masked consistently.
In this scenario TD is providing the date values which are then being passed to a import template that is querying Oracle. I presume the data type is consistent, but there's no watch on variables in TDP that I'm aware of. Might be a useful feature however.