mdcadu
asked on
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.
Thanks.
ASKER
This is the 10g version:
CREATE OR REPLACE VIEW "MYSCHEMA"."VWM_EXPSALPAYE MPLIST" ("Payee",
"SSN","Account","Division" ,"EffDate" ,"PrdEndDa te",
"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_TBLEXPENDITUR E 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_EXPSALPAYE MPLIST" ("Payee",
"SSN","Account","Division" ,"EffDate" ,"PrdEndDa te",
"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_TBLEXPENDITUR E 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.
CREATE OR REPLACE VIEW "MYSCHEMA"."VWM_EXPSALPAYE
"SSN","Account","Division"
"AdjustAmtDesc","AutoSeq")
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_TBLEXPENDITUR
where e.ACCACCOUNT = a.ACCACCOUNT
AND e.EXPFY = a.ACCFY
and e.expPrdEnddate <= Add_Months(trunc(sysdate),
and e.expPrdEndDate >= Add_Months(trunc(sysdate),
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_EXPSALPAYE
"SSN","Account","Division"
"AdjustAmtDesc","AutoSeq")
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_TBLEXPENDITUR
where e.ACCACCOUNT = a.ACCACCOUNT
AND e.EXPFY = a.ACCFY
and e.expPrdEnddate <= Add_Months(trunc(sysdate),
and e.expPrdEndDate >= Add_Months(trunc(sysdate),
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.
>>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.
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.
ASKER
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.
Thanks, anyway.
ASKER
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.
"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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are the datatypes for expprdenddate the same on 8i and 10g?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
So, what do I do about the points for this question?
Thanks.
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.
if none of them were helpful, then I suggest asking the admins to delete the question.
ASKER
Thanks for all of the help.
the function is supported in 10g (and 11g)