Business Objects missing expressions error

Posted on 2011-10-20
Last Modified: 2013-11-15
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.

  (   SCN.INCIDENT.ASSIGNMENT_GROUP  IN  ( 'Accounting', 'Engineering','Purchasing' )
   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

Question by:SAW56

    Author Comment

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

    Expert Comment

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


    Author Comment

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

    LVL 100

    Expert Comment

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



    Author Comment

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

    Assisted Solution

    by:slightwv (䄆 Netminder)
    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.

    LVL 73

    Accepted Solution

    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

    Author Closing Comment

    This worked. Thank you so much for your help.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now