Home » Fusion Middleware & Colab Suite » Business Intelligence » Help with SQL Syntax (Oracle SQL)
icon4.gif  Help with SQL Syntax [message #647876] Wed, 10 February 2016 09:50 Go to next message
gchiham
Messages: 4
Registered: February 2016
Location: Honduras
Junior Member
Hello I have the following code. It works OK but i need to setup the date manually not to get CURRENT DATE.
The date I want to setup is Oct-11-2015
Can you please help me with the Syntax.
What this does Is to make Buckets:
0-30 days
31-60
61-90
91-120
121+ days

so that calculation is made CURRENT DAY (minus) ABAY Start Date
I want to do it from October-11-2015 (minus) ABAY Start Date

CASE 
WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" , 
        CAST ( Evaluate (  'TO_DATE(%1,%2)' AS CHAR , 
                   CAST ( YEAR ( CURRENT_DATE ) AS VARCHAR ( 4 ) ) 
                   || RIGHT ( '0' || CAST  ( MONTH ( CURRENT_DATE ) AS VARCHAR ( 2 ) ) , 2 ) 
                   || '11' , 'YYYYMMDD' ) AS DATE ) ) > 120 
THEN  '121+ Days' 
WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" , 
        CAST ( Evaluate (  'TO_DATE(%1,%2)' AS CHAR , 
                   CAST ( YEAR ( CURRENT_DATE ) AS VARCHAR ( 4 ) ) 
                   || RIGHT ( '0' || CAST  ( MONTH ( CURRENT_DATE ) AS VARCHAR ( 2 ) ) , 2 ) 
                   || '11' , 'YYYYMMDD' ) AS DATE ) ) BETWEEN 91  AND 120 
THEN  '91-120 Days' 
WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" , 
        CAST ( Evaluate (  'TO_DATE(%1,%2)' AS CHAR , 
                   CAST ( YEAR ( CURRENT_DATE ) AS VARCHAR ( 4 ) ) 
                   || RIGHT ( '0' || CAST  ( MONTH ( CURRENT_DATE ) AS VARCHAR ( 2 ) ) , 2 ) 
                   || '11' , 'YYYYMMDD' ) AS DATE ) ) BETWEEN 61  AND 90 
THEN  '61-90 Days' 
WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" , 
        CAST ( Evaluate (  'TO_DATE(%1,%2)' AS CHAR , 
                   CAST ( YEAR ( CURRENT_DATE ) AS VARCHAR ( 4 ) ) 
                   || RIGHT ( '0' || CAST  ( MONTH ( CURRENT_DATE ) AS VARCHAR ( 2 ) ) , 2 ) 
                   || '11' , 'YYYYMMDD' ) AS DATE ) ) BETWEEN 31  AND 60 
THEN  '31-60 Days' 
WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" , 
        CAST ( Evaluate (  'TO_DATE(%1,%2)' AS CHAR , 
                   CAST ( YEAR ( CURRENT_DATE ) AS VARCHAR ( 4 ) ) 
                   || RIGHT ( '0' || CAST  ( MONTH ( CURRENT_DATE ) AS VARCHAR ( 2 ) ) , 2 ) 
                   || '11' , 'YYYYMMDD' ) AS DATE ) ) < 31 
THEN  '0-30 Days' 
ELSE  '0-30 Days' 
END   
Re: Help with SQL Syntax [message #647879 is a reply to message #647876] Wed, 10 February 2016 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

We don't have your tables or data.
We don't know what the expected/desired results should be.
Re: Help with SQL Syntax [message #647880 is a reply to message #647879] Wed, 10 February 2016 10:04 Go to previous messageGo to next message
gchiham
Messages: 4
Registered: February 2016
Location: Honduras
Junior Member
Its a report from Oracle Obiee 11 g
All I need is a little help setting up a Static DATE in this code.
I am sorry I dont have the tables.

WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" , 
        CAST ( Evaluate (  'TO_DATE(%1,%2)' AS CHAR , 
                   CAST ( YEAR ( CURRENT_DATE ) AS VARCHAR ( 4 ) ) 
                   || RIGHT ( '0' || CAST  ( MONTH ( CURRENT_DATE ) AS VARCHAR ( 2 ) ) , 2 ) 
                   || '11' , 'YYYYMMDD' ) AS DATE ) ) > 120 


/forum/fa/13021/0/
  • Attachment: obbiee.png
    (Size: 55.37KB, Downloaded 2826 times)

[Updated on: Wed, 10 February 2016 10:06]

Report message to a moderator

Re: Help with SQL Syntax [message #647881 is a reply to message #647880] Wed, 10 February 2016 10:18 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
This doesn't look like oracle to me. The SQL doesn't appear to be oracle and the error message doesn't appear to be oracle either.
Re: Help with SQL Syntax [message #647882 is a reply to message #647881] Wed, 10 February 2016 10:20 Go to previous messageGo to next message
gchiham
Messages: 4
Registered: February 2016
Location: Honduras
Junior Member
Its Oracle Business Intelligence formula for filter.

[Updated on: Wed, 10 February 2016 10:22]

Report message to a moderator

Re: Help with SQL Syntax [message #647947 is a reply to message #647882] Fri, 12 February 2016 06:47 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Google says that TIMESTAMPDIFF belongs to, say, MySQL or DB2 or MariaDB, but not Oracle. So, are you sure that this is a valid syntax? (Your OIBEE says not).

Anyway: in Oracle, you just have to subtract two dates in order to get number of days between them. Here's an example:
SQL> with test as
  2    (select date '2016-02-01' datum from dual union
  3     select date '2016-02-10' datum from dual union
  4     select date '2016-01-20' datum from dual
  5    )
  6  select trunc(sysdate) today,
  7         datum,
  8         trunc(sysdate) - datum number_of_days
  9  from test;

TODAY      DATUM      NUMBER_OF_DAYS
---------- ---------- --------------
12.02.2016 20.01.2016             23
12.02.2016 01.02.2016             11
12.02.2016 10.02.2016              2

SQL>


Applied to your situation, it *might* be as follows:
case
  when employee.abay_start_date - date '2015-10-11' > 120              then '121+ days'
  when employee.abay_start_date - date '2015-10-11' between 91 and 120 then '91 - 120 days'
  ...
end
Re: Help with SQL Syntax [message #647957 is a reply to message #647947] Fri, 12 February 2016 08:07 Go to previous message
gchiham
Messages: 4
Registered: February 2016
Location: Honduras
Junior Member
I found the answer. Using a Presentation Variable


CASE    
  WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,    
    CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,    
    CAST ( YEAR ( @{PV_DATE} ) AS VARCHAR ( 4 ) )    
    || RIGHT ( '0' || CAST ( MONTH ( @{PV_DATE} ) AS VARCHAR ( 2 ) ) , 2 )    
    || '11' , 'YYYYMMDD' ) AS DATE ) ) > 120    
  THEN '121+ Days'    
  WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,    
    CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,    
    CAST ( YEAR ( @{PV_DATE} ) AS VARCHAR ( 4 ) )    
    || RIGHT ( '0' || CAST ( MONTH ( @{PV_DATE} ) AS VARCHAR ( 2 ) ) , 2 )    
    || '11' , 'YYYYMMDD' ) AS DATE ) ) BETWEEN 91 AND 120    
  THEN '91-120 Days'    
  WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,    
    CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,    
    CAST ( YEAR ( @{PV_DATE} ) AS VARCHAR ( 4 ) )    
    || RIGHT ( '0' || CAST ( MONTH ( @{PV_DATE} ) AS VARCHAR ( 2 ) ) , 2 )    
    || '11' , 'YYYYMMDD' ) AS DATE ) ) BETWEEN 61 AND 90    
  THEN '61-90 Days'    
  WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,    
    CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,    
    CAST ( YEAR ( @{PV_DATE} ) AS VARCHAR ( 4 ) )    
    || RIGHT ( '0' || CAST ( MONTH ( @{PV_DATE} ) AS VARCHAR ( 2 ) ) , 2 )    
    || '11' , 'YYYYMMDD' ) AS DATE ) ) BETWEEN 31 AND 60    
  THEN '31-60 Days'    
  WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,    
    CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,    
    CAST ( YEAR ( @{PV_DATE} ) AS VARCHAR ( 4 ) )    
    || RIGHT ( '0' || CAST ( MONTH ( @{PV_DATE} ) AS VARCHAR ( 2 ) ) , 2 )    
    || '11' , 'YYYYMMDD' ) AS DATE ) ) < 31    
  THEN '0-30 Days'    
  ELSE '0-30 Days'    
END    
 


And this is place in Formula Field in Oracle Business Intellegence OBIEE 11g
Previous Topic: validation rule
Next Topic: Export and Import of User groups(security) in OBIEE
Goto Forum:
  


Current Time: Fri Mar 29 02:23:39 CDT 2024