asked on

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

SAW56

ASKER

Also, I selected only MS SQL Server for the Zone in this question and Busines Objects and Crystal Reports were automatically included. Sorry.

Mike McCracken

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

SAW56

ASKER

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))

Mike McCracken

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

SAW56

ASKER

I tried it and am not getting the following Web Intelligence database error: ORA-00904: "DATEADD": invalid identifier. (WIS 10901)

SOLUTION