• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2994
  • Last Modified:

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

0
SAW56
Asked:
SAW56
2 Solutions
 
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
SAW56Author Commented:
This worked. Thank you so much for your help.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now