Add_Months function in PL/SQL fails in 10g

I have a view that utilizes the native Oracle Add_Months function in the criteria portion of the select statement.  This view works well in our 8i environment, however we are migrating to 10g.  There, the view fails to return any data if the Add_Months function is not remarked out.  If it is remarked out, then the view returns appropriate data.  Does anyone have any idea why?

Thanks.
mdcaduAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
please post the view, maybe there is something else going on with the usage.

the function is supported in 10g (and 11g)
0
mdcaduAuthor Commented:
This is the 10g version:
CREATE OR REPLACE VIEW "MYSCHEMA"."VWM_EXPSALPAYEMPLIST" ("Payee",
    "SSN","Account","Division","EffDate","PrdEndDate",
    "AdjustAmtDesc","AutoSeq") AS
    SELECT e.EXPPAYEE AS "Payee"
, e.EMPSSN AS "SSN"
, e.ACCACCOUNT AS "Account"
, a.ACCBUDESC AS "Division"
, e.EFFDATE as "EffDate"
, e.EXPPRDENDDATE AS "PrdEndDate"
, e.EXPSALADJDESC AS "AdjustAmtDesc"
, e.EXPAUTOSEQ AS "AutoSeq"
FROM MYSCHEMA.TBLACCOUNTS a, MYSCHEMA.vwm_TBLEXPENDITURE e
where e.ACCACCOUNT = a.ACCACCOUNT
AND e.EXPFY = a.ACCFY  
and e.expPrdEnddate <= Add_Months(trunc(sysdate), 1)
and e.expPrdEndDate >= Add_Months(trunc(sysdate), -1)
and e.EXPRECID = 'SP'
AND e.EXPGOODREC = 'Y'
ORDER BY e.EXPPRDENDDATE DESC, e.EXPPAYEE asc

Here is the 8i version:
CREATE OR REPLACE VIEW "MYSCHEMA"."VWM_EXPSALPAYEMPLIST" ("Payee",
    "SSN","Account","Division","EffDate","PrdEndDate",
    "AdjustAmtDesc","AutoSeq") AS
    SELECT e.EXPPAYEE AS "Payee"
, e.EMPSSN AS "SSN"
, e.ACCACCOUNT AS "Account"
, a.ACCBUDESC AS "Division"
, e.EFFDATE as "EffDate"
, e.EXPPRDENDDATE AS "PrdEndDate"
, e.EXPSALADJDESC AS "AdjustAmtDesc"
, e.EXPAUTOSEQ AS "AutoSeq"
FROM MYSCHEMA.TBLACCOUNTS a, MYSCHEMA.vwm_TBLEXPENDITURE e
where e.ACCACCOUNT = a.ACCACCOUNT
AND e.EXPFY = a.ACCFY  
and e.expPrdEnddate <= Add_Months(trunc(sysdate), 1)
and e.expPrdEndDate >= Add_Months(trunc(sysdate), -1)
and e.EXPRECID = 'SP'
AND e.EXPGOODREC = 'Y'
ORDER BY e.EXPPRDENDDATE DESC, e.EXPPAYEE asc

The view vwm_TBLEXPENDITURE which is used in both versions will return data if you query it outside of the problem view.
0
gatorvipCommented:
>>The view vwm_TBLEXPENDITURE which is used in both versions will return data if you query it outside of the problem view.

But, do they return the *same* data?

In this case, the same DDL for your view should work for both Oracle 8 and 10g so it's more likely the underlying data is not the same.

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mdcaduAuthor Commented:
All the data is the same, I double and triple checked that since there has been a lag in the data between 8i production and 10g test.  I have built a work around to this issue within the client application which uses this view.

Thanks, anyway.
0
mdcaduAuthor Commented:
Let me clarify that last post
"All the data is the same, I double and triple checked that since there has been a lag in the data between 8i production and 10g test."  

I have queried both 8i and 10g using criteria which will retunr data which does exist in both databases.  So, I am not asking 10g to return data which exists only in 8i.
0
sdstuberCommented:
if you thnk the problem is in the add_months, remove it from the problem for a second...

what does this return?  same thing as 8i?

SELECT e.EXPPAYEE AS "Payee"
, e.EMPSSN AS "SSN"
, e.ACCACCOUNT AS "Account"
, a.ACCBUDESC AS "Division"
, e.EFFDATE as "EffDate"
, e.EXPPRDENDDATE AS "PrdEndDate"
, e.EXPSALADJDESC AS "AdjustAmtDesc"
, e.EXPAUTOSEQ AS "AutoSeq"
FROM MYSCHEMA.TBLACCOUNTS a, MYSCHEMA.vwm_TBLEXPENDITURE e
where e.ACCACCOUNT = a.ACCACCOUNT
AND e.EXPFY = a.ACCFY  
and e.expPrdEnddate <= to_date('2009/06/07','yyyy/mm/dd')
and e.expPrdEndDate >= to_date('2009/04/07','yyyy/mm/dd')
and e.EXPRECID = 'SP'
AND e.EXPGOODREC = 'Y'
ORDER BY e.EXPPRDENDDATE DESC, e.EXPPAYEE asc;
0
greyhumanCommented:
If it compiled means something is wrong in the date syntax or format.
try the to_date and eventually after seeing data switch on to add_months.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
Are the datatypes for expprdenddate the same on 8i and 10g?
0
P2OCommented:
If data is fetching in one instance and not in another and if it's a date field...
Please check NLS_DATE format is the same in the both instance.
This will impact on how sysdate is working...
quick check of default nls_date format is
select sysdate form dual;
OR Run following query on both instances.
select * from v$parameter
where name like '%nls%date%format%'

0
mdcaduAuthor Commented:
Our DBA re-imported the view into the 10g environment and it worked just fine after that.  Maybe it was corrupted during the first import. Dunno.

So, what do I do about the points for this question?

Thanks.
0
sdstuberCommented:
up to you.  if anything posted above was helpful at all, even if just to prove your case to your dba to get it recreated, then select all that helped and split the points.

if none of them were helpful, then I suggest asking the admins to delete the question.
0
mdcaduAuthor Commented:
Thanks for all of the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.