Skip to Main Content

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Found the solution doesn`t know how to implement it

Gui Rocha-OracleApr 22 2015 — edited Apr 23 2015

Hi there,

In my reports(using oracle obiee 11.1.1.7.0) I need to display chart values in percentage and value at the same time, but I don`t know how. ( for example in a pie chart, i can only display one, either the actual value or the percentage of the slice)

I`ve found this solution  pie chart value and percentage but i don`t know how to implement it.

The solution is to apply this:

FormatString='%_VALUE, %_PERCENT_OF_CATEGORY'


Ok, but where? and how

Anyone care to explain further? Thank you

This post has been answered by Christian Berg-0racle on Apr 23 2015
Jump to Answer

Comments

Frank Kulash
Answer

Hi,

Here's one way:

SELECT DISTINCT

          d.*

,         NVL2 ( o.rail_no

               , 'INVALID'  -- or 'UNVALID', if you really want that

               , 'VALID'

               )    AS check_row

FROM             t_plan  d

LEFT OUTER JOIN  t_plan  o  ON   o.rail_no    =  d.rail_no

                            AND  o.time_from  <= d.time_until

                            AND  o.time_until >= d.time_from

ORDER BY  d.time_from;

If you'd care to post INSERT statements for your sample data, then I could test this.

Two ranges overlap if (and only if) each one begins before the other one ends.  This will be more clear if you consider that two ranges do not overlap if (and only if) one of them begins after the other one ends.

This solution assumes that time_from <= time_until on every row.  Is that true?  For example, if a rail is in use at midnight, could you have (for example) time_from='23:55' and time_intil='00:05' on the same row?

Marked as Answer by Tobias Arnhold · Sep 27 2020
RogerT

and another way...

WITH d AS (SELECT 172 train, to_date('07:06','hh24:mi') AS sd, to_date('15:50','hh24:mi') AS ed, 1 rail FROM dual UNION ALL

           select 120,to_date('07:33','hh24:mi'),to_date('15:41','hh24:mi') as ed, 2 rail from dual union all

           select 229,to_date('07:56','hh24:mi'),to_date('14:52','hh24:mi') as ed, 3 rail from dual union all

           SELECT 217,to_date('15:05','hh24:mi'),to_date('18:47','hh24:mi') as ed, 1 rail from dual union all

           select 189,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 2 rail from dual union all

           SELECT 185,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 3 rail from dual union all

           SELECT 181,to_date('20:10','hh24:mi'),to_date('23:53','hh24:mi') AS ed, 2 rail FROM dual UNION ALL

           SELECT 142,to_date('20:14','hh24:mi'),to_date('23:32','hh24:mi') AS ed, 3 rail FROM dual)

SELECT train, rail, sd, ed, prev_ed, next_sd

     , CASE WHEN (    (prev_ed IS NULL OR sd > prev_ed)

                  and (next_sd is null or ed < next_sd))

            THEN 'VALID'

            ELSE 'INVALID'

       end as validity

  from (SELECT train, rail

            , to_number(to_char(sd,'hh24mi')) AS sd

            , to_number(to_char(ed,'hh24mi')) AS ed

            , to_number(to_char(lag(ed) OVER (PARTITION BY rail ORDER BY sd),'hh24mi')) prev_ed

            , to_number(to_char(lead(sd) OVER (PARTITION BY rail ORDER BY sd),'hh24mi')) next_sd

           from d)          

Frank Kulash

Hi,

LEAD and LAG can't do this job.  LEAD and LAG can only compare a row to 1 neighboring row.  In this problem (if I understand it correctly) we need to compare each row to all rows with the same rail_no, not just the neighboring rows.  For example, if, on the same rail_no we had ranges like this:

01:00   02:00

01:05   01:10

01:15   01:20

01:25   01:30

we would want to say that they were all invalid, since the first row (01:00-02:00) overlaps each of the others.

RogerT

Good point

Nimish Garg

Try this

WITH t AS

(

  SELECT 172 TRAIN_NO, to_date('07:06','hh24:mi') AS TIME_FROM, to_date('15:50','hh24:mi') AS TIME_UNTIL    , 1 RAIL_NO FROM dual UNION ALL

  select 120,to_date('07:33','hh24:mi'),to_date('15:41','hh24:mi') as ed, 2 rail from dual union all

  select 229,to_date('07:56','hh24:mi'),to_date('14:52','hh24:mi') as ed, 3 rail from dual union all

  SELECT 217,to_date('15:05','hh24:mi'),to_date('18:47','hh24:mi') as ed, 1 rail from dual union all

  select 189,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 2 rail from dual union all

  SELECT 185,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 3 rail from dual union all

  SELECT 181,to_date('20:10','hh24:mi'),to_date('23:53','hh24:mi') AS ed, 2 rail FROM dual UNION ALL

  SELECT 142,to_date('20:14','hh24:mi'),to_date('23:32','hh24:mi') AS ed, 3 rail FROM dual

)

select

  t.TRAIN_NO,

  t.TIME_FROM,

  t.TIME_UNTIL,

  t.RAIL_NO,

  decode(t2.TRAIN_NO,NULL,'VALID','INVALID') CHECK_ROW

from

  t,

  (

    select

      t1.TRAIN_NO

    from

      t t1, t t2

    where

      t1.RAIL_NO = t2.RAIL_NO

      and t1.TIME_FROM <= t2.TIME_UNTIL

      and t1.TIME_UNTIL >= t2.TIME_FROM

      and t1.TRAIN_NO <> t2.TRAIN_NO

  ) t2

  where t.TRAIN_NO = t2.TRAIN_NO (+)

  order by TIME_FROM

Partha Sarathy S

WITH T_PLAN(TRAIN_NO  ,  TIME_FROM ,   TIME_UNTIL ,   RAIL_NO) AS (

SELECT 172,'07:06','15:50', 1 FROM DUAL UNION ALL

SELECT 120,'07:33','15:41', 2 FROM DUAL UNION ALL

SELECT 229,'07:56','14:52', 3 FROM DUAL UNION ALL

SELECT 217,'15:05','18:47', 1 FROM DUAL UNION ALL

SELECT 189,'16:10','19:53', 2 FROM DUAL UNION ALL

SELECT 185,'16:10','19:53', 3 FROM DUAL UNION ALL

SELECT 181,'20:10','23:53', 2 FROM DUAL UNION ALL

SELECT 142,'20:14','23:32', 3 FROM DUAL)

SELECT T.TRAIN_NO,

       T.TIME_FROM,

       T.TIME_UNTIL,

       T.RAIL_NO,

       CASE WHEN (T.TRAIN_NO IN (SELECT T1.TRAIN_NO FROM T_PLAN T1,

     T_PLAN T2

WHERE (TO_TIMESTAMP(T1.TIME_FROM,'HH24:MI') BETWEEN TO_TIMESTAMP(T2.TIME_FROM,'HH24:MI') AND TO_TIMESTAMP(T2.TIME_UNTIL,'HH24:MI')

OR    TO_TIMESTAMP(T1.TIME_UNTIL,'HH24:MI') BETWEEN TO_TIMESTAMP(T2.TIME_FROM,'HH24:MI') AND TO_TIMESTAMP(T2.TIME_UNTIL,'HH24:MI'))

AND T1.TRAIN_NO != T2.TRAIN_NO

AND T1.RAIL_NO = T2.RAIL_NO)) THEN 'INVALID' ELSE 'VALID' END AS CHECK_NOW

FROM T_PLAN T;

OUTPUT:

  TRAIN_NO TIME_ TIME_    RAIL_NO CHECK_N
---------- ----- ----- ---------- -------
       172 07:06 15:50          1 INVALID
       120 07:33 15:41          2 VALID
       229 07:56 14:52          3 VALID
       217 15:05 18:47          1 INVALID
       189 16:10 19:53          2 VALID
       185 16:10 19:53          3 VALID
       181 20:10 23:53          2 VALID
       142 20:14 23:32          3 VALID

8 rows selected.

Message was edited by: Parth272025

Stew Ashton

I agree that LAG and LEAD are not enough, but it can be done with other analytical functions:

WITH d AS (

  SELECT 172 train, to_date('07:06','hh24:mi') AS sd, to_date('15:50','hh24:mi') AS ed, 1 rail FROM dual UNION ALL

  select 120,to_date('07:33','hh24:mi'),to_date('15:41','hh24:mi') as ed, 2 rail from dual union all

  select 229,to_date('07:56','hh24:mi'),to_date('14:52','hh24:mi') as ed, 3 rail from dual union all

  SELECT 217,to_date('15:05','hh24:mi'),to_date('18:47','hh24:mi') as ed, 1 rail from dual union all

  select 189,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 2 rail from dual union all

  SELECT 185,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 3 rail from dual union all

  SELECT 181,to_date('20:10','hh24:mi'),to_date('23:53','hh24:mi') AS ed, 2 rail FROM dual UNION ALL

  SELECT 142,to_date('20:14','hh24:mi'),to_date('23:32','hh24:mi') AS ed, 3 rail FROM dual

)

select train, sd, ed, rail,

case when ed > next_sd or sd < prev_ed then 'INVALID'

     else 'VALID'

end check_row

from (

  select d.*,

  min(sd) over(partition by rail order by sd rows between 1 following and unbounded following) next_sd,

  max(ed) over(partition by rail order by ed rows between unbounded preceding and 1 preceding) prev_ed

  from d

)

order by sd;

TRAINSDEDRAILCHECK_ROW
1722014-02-01 07:06:002014-02-01 15:50:001INVALID
1202014-02-01 07:33:002014-02-01 15:41:002VALID
2292014-02-01 07:56:002014-02-01 14:52:003VALID
2172014-02-01 15:05:002014-02-01 18:47:001INVALID
1892014-02-01 16:10:002014-02-01 19:53:002VALID
1852014-02-01 16:10:002014-02-01 19:53:003VALID
1812014-02-01 20:10:002014-02-01 23:53:002VALID
1422014-02-01 20:14:002014-02-01 23:32:003VALID
Tobias Arnhold

Thanks. Exactly what I was looking for.

Stew Ashton

Here's an alternative for when we all have 12c. I added a line to create multiple overlaps.

WITH d AS (

  SELECT 172 train, to_date('07:06','hh24:mi') AS sd, to_date('15:50','hh24:mi') AS ed, 1 rail FROM dual UNION ALL

  select 120,to_date('07:33','hh24:mi'),to_date('15:41','hh24:mi') as ed, 2 rail from dual union all

  select 229,to_date('07:56','hh24:mi'),to_date('14:52','hh24:mi') as ed, 3 rail from dual union all

  SELECT 217,to_date('15:05','hh24:mi'),to_date('18:47','hh24:mi') as ed, 1 rail from dual union all

  SELECT 218,to_date('15:10','hh24:mi'),to_date('19:47','hh24:mi') as ed, 1 rail from dual union all

  select 189,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 2 rail from dual union all

  SELECT 185,to_date('16:10','hh24:mi'),to_date('19:53','hh24:mi') as ed, 3 rail from dual union all

  SELECT 181,to_date('20:10','hh24:mi'),to_date('23:53','hh24:mi') AS ed, 2 rail FROM dual UNION ALL

  SELECT 142,to_date('20:14','hh24:mi'),to_date('23:32','hh24:mi') AS ed, 3 rail FROM dual

)

select train, sd, ed, rail,

nvl2(mn, 'INVALID', 'VALID') check_row

from d

match_recognize(

  partition by rail order by sd

  measures match_number() mn

  all rows per match with unmatched rows

  pattern (a b+)

  define b as b.sd < a.ed

)

order by sd;

TRAINSDEDRAILCHECK_ROW
1722014-02-01 07:06:002014-02-01 15:50:001INVALID
1202014-02-01 07:33:002014-02-01 15:41:002VALID
2292014-02-01 07:56:002014-02-01 14:52:003VALID
2172014-02-01 15:05:002014-02-01 18:47:001INVALID
2182014-02-01 15:10:002014-02-01 19:47:001INVALID
1892014-02-01 16:10:002014-02-01 19:53:002VALID
1852014-02-01 16:10:002014-02-01 19:53:003VALID
1812014-02-01 20:10:002014-02-01 23:53:002VALID
1422014-02-01 20:14:002014-02-01 23:32:003VALID
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details