Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Add_Months function in PL/SQL fails in 10g

Posted on 2009-05-07
12
Medium Priority
?
899 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:mdcadu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 24329017
please post the view, maybe there is something else going on with the usage.

the function is supported in 10g (and 11g)
0
 

Author Comment

by:mdcadu
ID: 24329144
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
 
LVL 20

Expert Comment

by:gatorvip
ID: 24329834
>>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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:mdcadu
ID: 24329962
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
 

Author Comment

by:mdcadu
ID: 24330011
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 498 total points
ID: 24330131
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
 

Accepted Solution

by:
greyhuman earned 501 total points
ID: 24337709
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
 
LVL 32

Expert Comment

by:awking00
ID: 24339253
Are the datatypes for expprdenddate the same on 8i and 10g?
0
 
LVL 3

Assisted Solution

by:P2O
P2O earned 501 total points
ID: 24341754
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
 

Author Comment

by:mdcadu
ID: 24354375
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 24354421
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
 

Author Closing Comment

by:mdcadu
ID: 31579141
Thanks for all of the help.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question