Business Objects missing expressions error

I need assistance modifying the following SQL so it pulls the incidents that were created in the previous month (using the Open Time field). For some reason when I attempt to validate the SQL I am getting a Web Intelligence error "A database error occured. The database error text is: ORA-00936: missing expression .(WIS 10901). I have found the error online and it appears to be caused by any number of issues so I was wondering if you could look at my SQL and see if it is correct.

SELECT
  SCN.INCIDENT.INCIDENT_ID,
  SCN.INCIDENT.ASSIGNMENT_GROUP,
  SCN.INCIDENT.OPEN_TIME,
  SCN.INCIDENT.UPDATE_TIME,
  SCN.INCIDENT.STATUS
FROM
  SCN.INCIDENT
WHERE
  (   SCN.INCIDENT.ASSIGNMENT_GROUP  IN  ( 'Accounting', 'Engineering','Purchasing' )
   AND
   SCN.INCIDENT.STATUS  IN  ( 'Pending Vendor','Open','Pending Other','Completed','Pending Customer','Work In Progress','Accepted','Waiting for Part','Referred','Part on Order','Pending Change'  )
AND SCN.INCIDENT.Open_Time between 
  select dateadd(d, -datepart(d,getdate()) +1, DATEADD(m, -1, cast(getdate() as date)))
   and  select dateadd(d, -datepart(d,getdate()) ,cast(getdate() as date))

Open in new window

SAW56Asked:
Who is Participating?
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.

SAW56Author Commented:
Also, I selected only MS SQL Server for the Zone in this question and Busines Objects and Crystal Reports were automatically included. Sorry.
0
mlmccCommented:
What is this supposed to do?

  select dateadd(d, -datepart(d,getdate()) +1, DATEADD(m, -1, cast(getdate() as date)))
   and  select dateadd(d, -datepart(d,getdate()) ,cast(getdate() as date))

mlmcc
0
SAW56Author Commented:
I was using the formula to pull information from the previous month.  I am creating reports in SAP Business Objects. Is this formula not correct?
select dateadd(d, -datepart(d,getdate()) +1, DATEADD(m, -1, cast(getdate() as date)))
   and  select dateadd(d, -datepart(d,getdate()) ,cast(getdate() as date))

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mlmccCommented:
Try it this way
dateadd(d, -datepart(d,getdate()) +1, DATEADD(m, -1, getdate()))
   and  dateadd(d, -datepart(d,getdate()) , getdate())

You might also need ' ' or " "
dateadd("d", -datepart("d",getdate()) +1, DATEADD("m", -1, getdate()))
   and  dateadd("d", -datepart("d",getdate()) , getdate())

mlmcc

0
SAW56Author Commented:
I tried it and am not getting the following Web Intelligence database error: ORA-00904: "DATEADD": invalid identifier. (WIS 10901)
0
slightwv (䄆 Netminder) Commented:
Getdate and dateadd are not Oracle but the error message is Oracle.

I'm on mobile and cannot get a good look at the code so I'm not following the between code you posted so here are the basics:

sysdate is current dbserver date/time down to the second:  select sysdate from dual;

Trunc(sysdate) zeros out the time, thus midnight:
Select teunc(sysdate) from dual;

Adding 1 to sysdate is 24 hours: select sysdate+1 from dual;

There is an add_months sql function:  select add_months(sysdate,1) from dual;


The online docs have all you need.

Hope this helps.

0
sdstuberCommented:
Oracle doesn't have the DATEADD, GETDATE or DATEPART functions,  that's T-SQL


try this...

AND SCN.INCIDENT.Open_Time  >= add_months(trunc(sysdate,'mm'),-1)
AND SCN.INCIDENT.Open_Time < trunc(sysdate,'mm')


I don't recommend using BETWEEN for this because it's end-point inclusive,  meaning you'd include midnight (00:00:00) of the current month
so, instead,  use >=  the first of the previous month  and <  the first of the current month
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
SAW56Author Commented:
This worked. Thank you so much for your 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
DB Reporting Tools

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.