Like said in title, we use oracle 10g as the target DB and DI 11.7.3 is the used version, but the function ‘add_months()’ can not be pushed down to DB, What a pity !
kfyme (BOB member since 2007-07-10)
It is pushed down 
when I do a
select * from al_funcinfo where name = ‘add_months’
I get two lines, one for the SOURCE=ACTA (the engine version) and one for SOURCE=Oracle version. And therefore it will get pushed down or should at least. I did not build a dataflow yet.
Is it possible there are some datatype conversions that actually prohibit the pushdown? e.g. add_months(string_column, 1) might not get pushed down as the implict conversion from string to date is done inside the engine…just guessing.
Werner Daehn 
(BOB member since 2004-12-17)
At first, I found this issue in a complex DF. And then I test the function add_months() in a very simple DF, then the case shows the result that this function can not be pushed down to DB, also there is no conversion conflict…
So perhaps that is a limitation or bug?
kfyme (BOB member since 2007-07-10)
First, check the repo table with my sql statement. Then send the ATL.
Werner Daehn (BOB member since 2004-12-17)
I execute that SQL, but just one record (SOURCE=ACTA) returned.
my DI repo was created successfully. 
kfyme (BOB member since 2007-07-10)
Agree
That table is maintained by importing the rep_funcinfo.atl during the repo creation
Werner Daehn (BOB member since 2004-12-17)
So Werner, my repo is correct or not? I think the function add_months should be pushed down to Oracle.
If there are some problem in my repo, then how can I improve this? to update the table al_funcinfo in the repo? If so, can you send me the SQL for pushed-down to Oracle ? Or can you send me the new file ‘rep_funcinfo.atl’?
kfyme (BOB member since 2007-07-10)
First thing would be to go to C:\program files\bo\di\admin\repo\rep_funcinfo.atl and search for add_months. If it is there but not in the repo, the next question would be to look at any traces of the repo creation, log files whatsoever.
And if we a brave we just import that atl via Designer, restart Designer and see what happens then.
Werner Daehn (BOB member since 2004-12-17)
As a testing, I inserted a records for function Add_months() for Oracle push-down option into the table al_funcinfo by imitating other functions exist in the tables, then it works, the function Add_months() was push-down to DB as I saw it appears in the optimized SQL.
According to your suggestion, I checked the file rep_funcinfo.atl, but I just find the folow info about add_months():
CREATE __AL_REPO_FUNCTION add_months (“Original date” DATETIME IN, “Months to add” INT IN)
SET(database_type = ‘ACTA’,
function_type = ‘Date_Function’,
DB_FunctionName = ‘add_months’,
Description = ‘Adds a given number of months to a date.’,
Parallelizable = ‘yes’,
External_name = ‘add_months’,
return_param_dep = ‘1’,
return_datatype = ‘null’,
return_datatype_size = ‘null’ ,
param0 = ‘Original Date.’,
param1 = ‘Number of months to add to the Original Date.’ )
So, I think the file don’t contains the information about pushing the function down to Oracle DB. But when I check on other functions like floor, then I found many groups of information for pushing down to different DBs like Oracle, DB2, Sybase IQ etc…
kfyme (BOB member since 2007-07-10)
So we have our answer. It is a bug in your version that the add_months function is not pushed down. Your repository got created correctly, it is just that the rep_funcinfo.atl was not complete and that was corrected again in my version at least.
Your patched repo is fine, you won’t have issues with that.
Werner Daehn (BOB member since 2004-12-17)
OK, thanks Werner !
kfyme (BOB member since 2007-07-10)
Hi,
I was expecting a translation of add_months() for SQL Server databases, for instance with DATEADD function.
I didn’t find anything about that in the al_funcinfo table.
Is there any way to push down the add_months function on a SQL Server database?
Thanks and regards,
Jerome
jd.pro 
(BOB member since 2010-11-15)
I’m mainly posting this to see what Werner says in response to the post above mine.
The question raised in the post above mine falls into the category of “Just because you can, doesn’t mean you should.” You can add rows to the repository table to add completely new functionality to Designer. The function in your database that you want to use in a Query transform doesn’t show up in Designer? Add it to the al_funcinfo table. Assuming you know what you’re doing and you get everything right it will work. But then you have to make that same change in every other local repository in development, qa and production. Miss just one repository and eventually someone will spend HOURS trying to figure out why something works in production/qa/dev but not in dev/production/qa.
Plus, when you bring up a new version of DS your tweak may not survive the upgrade (though I suspect upgrades will be OK).
In my opinion, the risk is not worth it. Better to put the screws to SAP to get the functionality formally added than to start hacking around in the repository tables.
eganjp 
(BOB member since 2007-09-12)
Yup. For example, in Teradata, there is no NVL() function. So whenever we just use a NVL() in our Query Transform, it won’t do a Pushdown SQL. So we had to add it to AL_FUNCINFO for COALESCE
ganeshxp (BOB member since 2008-07-17)
Here’s an idea to keep the repository straight…
At the beginning of your job check to see if the manually added function is in the repository. If it isn’t then add it. This will work for a production situation but not so well for dev. Then again, the job may validate before it runs and if the function isn’t there then you are SOL. In that case it would have to be a standalone job that adds the function.
eganjp (BOB member since 2007-09-12)
Hi,
Instead of pushing the add_months function, I pushed the calculation outside the DF in a script and used the result as a parameter.
But I still don’t have my calculation pushed down to the DB (SQL Server 2008 R2) 
My mapping code is :
min(
ifthenelse((W_POLICE_DWH.DT_FIN_ACTIVITE >= $p_dt_24mois),
0,
1))
DT_FIN_ACTIVITE has datetime type.
$p_dt_24mois has date type.
This code is not pushed down even if I cast the parameter to datetime type or is I set its type to datetime.
If I try using the sysdate() function instead of my parameter, that works fine…
Any idea?
Am I using the parameter in a wrong way?
jd.pro (BOB member since 2010-11-15)
If all else fails then use pushdown_sql() to push the expression down to the database.
eganjp (BOB member since 2007-09-12)
Yes, but the function pushdown_sql() is limited to the WHERE clause, and I need to use my parameters in an aggregate calculation…
jd.pro (BOB member since 2010-11-15)
Ah, I see. I glossed over that you were using that expression in a mapping.
One thing to try would be to create a function in the database, import it to the Datastore and then use the function in the mapping.
eganjp (BOB member since 2007-09-12)
Why is sysdate() pushed down but a global variable is not???
Are you 100% sure that when the column and the global variable is of type datetime, even then it is not pushed down??
Werner Daehn (BOB member since 2004-12-17)