OraFAQ Forum: Business Intelligence » Help with SQL Syntax

Home » Fusion Middleware & Colab Suite » Business Intelligence » Help with SQL Syntax (Oracle SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help with SQL Syntax [message #647876]

Wed, 10 February 2016 09:50

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

Report message to a moderator

Re: Help with SQL Syntax [message #647879 is a reply to message #647876]

Wed, 10 February 2016 09:59

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.

Report message to a moderator

Re: Help with SQL Syntax [message #647880 is a reply to message #647879]

Wed, 10 February 2016 10:04

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

Attachment: obbiee.png
(Size: 55.37KB, Downloaded 3764 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

cookiemonster
Messages: 13964
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.

Report message to a moderator

Re: Help with SQL Syntax [message #647882 is a reply to message #647881]

Wed, 10 February 2016 10:20

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

Littlefoot
Messages: 21823
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

Report message to a moderator

Re: Help with SQL Syntax [message #647957 is a reply to message #647947]

Fri, 12 February 2016 08:07

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

Report message to a moderator

Previous Topic:	validation rule
Next Topic:	Export and Import of User groups(security) in OBIEE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 13 07:07:14 CST 2025