Skip to Main Content
Forums

Java EE (Java Enterprise Edition) General Discussion

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Design time support

843842Oct 3 2003 — edited Oct 21 2003
Swing has good design time support and standardization how to build visual designers (and an example Bean Builder application), what is the situation for JSF ?

Comments

94799

I would be looking at analytic functions in the first instance, e.g.

Oracle Database 10g Release 10.2.0.2.0 - Production

SQL> SELECT id_v, csn_v,
  2         CASE
  3            WHEN id_csn_all = id_csn_pass THEN 'PASS (ALL)'
  4            WHEN id_csn_all = id_csn_fail THEN 'FAIL (ALL)'
  5            WHEN id_csn_all = id_csn_unsuccess THEN 'UNSUCCESSFUL (ALL)'
  6            ELSE 'FAIL (MIXED)'
  7         END status_v
  8  FROM   (SELECT id_v, csn_v, status_v, COUNT (*) OVER (PARTITION BY id_v, csn_v) id_csn_all,
  9                 COUNT (CASE status_v
 10                           WHEN 'PASS' THEN 1
 11                        END) OVER (PARTITION BY id_v, csn_v) id_csn_pass,
 12                 COUNT (CASE status_v
 13                           WHEN 'FAIL' THEN 1
 14                        END) OVER (PARTITION BY id_v, csn_v) id_csn_fail,
 15                 COUNT (CASE status_v
 16                           WHEN 'UNSUCCESSFUL' THEN 1
 17                        END) OVER (PARTITION BY id_v, csn_v) id_csn_unsuccess
 18          FROM   table_name);

      ID_V      CSN_V STATUS_V
---------- ---------- ------------------
       183    1795752 PASS (ALL)
       183    1795752 PASS (ALL)
       210    1701476 FAIL (MIXED)
       210    1701476 FAIL (MIXED)
       210    1701476 FAIL (MIXED)
       210    1705535 UNSUCCESSFUL (ALL)
       212    1701478 FAIL (ALL)
       212    1701478 FAIL (ALL)
       214    1234566 FAIL (MIXED)
       214    1234566 FAIL (MIXED)
       217    1234569 FAIL (MIXED)
       217    1234569 FAIL (MIXED)

12 rows selected.

SQL>
Sentinel
In one statement:
SELECT ID_V,
  CSN_V,
  DECODE(COUNT(DISTINCT STATUS_V) 
         OVER (PARTITION BY ID_V, CSN_V),   1,   STATUS_V,   'FAIL') STATUS
FROM C;
94799
Good suggestion - I agree that it could be written more concisely, I was trying to write a solution that was self-explanatory.
Sentinel
If you find case statements more self explanetory, then you could go with this version ;)
SELECT ID_V,
  CSN_V,
  CASE COUNT(DISTINCT STATUS_V) OVER(PARTITION BY ID_V,   CSN_V)
    WHEN 1 THEN STATUS_V
    ELSE 'FAIL'
  END STATUS
FROM C;
All three examples thus far would satisfy the requirements.
3096
... a solution that was self-explanatory
Be careful of sacrificing a concise and performant solution on the altar of "self-documenting code". Better to supply comments -- which you ought to supply anyway
Aketi Jyuuzou

This is an interesting question.

create table StatusTable(ID_V,CSN_V,STATUS_V) as
select 183,1795752,'PASS' from dual union all
select 183,1795752,'PASS' from dual union all
select 210,1701476,'FAIL' from dual union all
select 210,1701476,'PASS' from dual union all
select 210,1701476,'PASS' from dual union all
select 212,1701478,'FAIL' from dual union all
select 212,1701478,'FAIL' from dual union all
select 210,1705535,'UNSUCCESSFUL' from dual union all
select 214,1234566,'UNSUCCESSFUL' from dual union all
select 214,1234566,'PASS' from dual union all
select 217,1234569,'FAIL' from dual union all
select 217,1234569,'UNSUCCESSFUL' from dual;

select ID_V,CSN_V,STATUS_V,
case when AllFail = 1 then 'FAIL (ALL)'
     when HasFail = 1 then 'FAIL (Has)'
     when MaxStatus != MinStatus then 'FAIL (MIXED)'
     else STATUS_V end as newSTATUS_V
from (select ID_V,CSN_V,STATUS_V,
      min(case when STATUS_V = 'FAIL' then 1 else 0 end) over(partition by ID_V,CSN_V) as AllFail,
      max(case when STATUS_V = 'FAIL' then 1 else 0 end) over(partition by ID_V,CSN_V) as HasFail,
      max(STATUS_V) over(partition by ID_V,CSN_V) as MaxStatus,
      min(STATUS_V) over(partition by ID_V,CSN_V) as MinStatus
        from StatusTable)
order by ID_V,CSN_V,STATUS_V;

ID_V    CSN_V  STATUS_V      NEWSTATUS_V
----  -------  ------------  ------------
 183  1795752  PASS          PASS
 183  1795752  PASS          PASS
 210  1701476  FAIL          FAIL (Has)
 210  1701476  PASS          FAIL (Has)
 210  1701476  PASS          FAIL (Has)
 210  1705535  UNSUCCESSFUL  UNSUCCESSFUL
 212  1701478  FAIL          FAIL (ALL)
 212  1701478  FAIL          FAIL (ALL)
 214  1234566  PASS          FAIL (MIXED)
 214  1234566  UNSUCCESSFUL  FAIL (MIXED)
 217  1234569  FAIL          FAIL (Has)
 217  1234569  UNSUCCESSFUL  FAIL (Has)

Furthermore,
We can use below alternative solution.

select ID_V,CSN_V,STATUS_V,
case when AllFail = 1 then 'FAIL (ALL)'
     when HasFail = 1 then 'FAIL (Has)'
     when distinctCount != 1 then 'FAIL (MIXED)'
     else STATUS_V end as newSTATUS_V
from (select ID_V,CSN_V,STATUS_V,
      min(case when STATUS_V = 'FAIL' then 1 else 0 end) over(partition by ID_V,CSN_V) as AllFail,
      max(case when STATUS_V = 'FAIL' then 1 else 0 end) over(partition by ID_V,CSN_V) as HasFail,
      count(distinct STATUS_V) over(partition by ID_V,CSN_V) as distinctCount
        from StatusTable)
order by ID_V,CSN_V,STATUS_V;

I used Similar logic in below thread.
Below thread deals alike question.
2184883

However, I think that we can use below solution.
But below solution loses detail information which is why it is "Fail".

select ID_V,CSN_V,STATUS_V,
case count(distinct STATUS_V) over(partition by ID_V,CSN_V)
     when 1 then STATUS_V else 'FAIL' end as newSTATUS_V
from StatusTable
order by ID_V,CSN_V,STATUS_V;

ID_V    CSN_V  STATUS_V      NEWSTATUS_V
----  -------  ------------  ------------
 183  1795752  PASS          PASS
 183  1795752  PASS          PASS
 210  1701476  FAIL          FAIL
 210  1701476  PASS          FAIL
 210  1701476  PASS          FAIL
 210  1705535  UNSUCCESSFUL  UNSUCCESSFUL
 212  1701478  FAIL          FAIL
 212  1701478  FAIL          FAIL
 214  1234566  PASS          FAIL
 214  1234566  UNSUCCESSFUL  FAIL
 217  1234569  FAIL          FAIL
 217  1234569  UNSUCCESSFUL  FAIL
94799

Be careful of sacrificing a concise and performant solution on the altar of (etc.)

Er...I wasn't advocating that, I was trying to illustrate the approach to the OP, that was all - this is after all supposed to be an educational forum.

Incidentally - say for example that the original query took an hour (anecdotal) to process 160,000 rows and all three of the new solutions do identical LIO and take somewhere just over two seconds (see below), I would say that the developer probably had a teeny-weeny bit of license to consider a more generalised self-documenting version over a single-use commented version (where no-one ever bothers to update the comments).

SQL> SELECT id_v, csn_v,
  2         CASE COUNT (DISTINCT status_v) OVER (PARTITION BY id_v, csn_v)
  3            WHEN 1 THEN status_v
  4            ELSE 'FAIL'
  5         END status
  6  FROM   table_name;

160000 rows selected.

Elapsed: 00:00:02.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1518 Card=152544 Bytes=5186496)
   1    0   WINDOW (SORT) (Cost=1518 Card=152544 Bytes=5186496)
   2    1     TABLE ACCESS (FULL) OF 'TABLE_NAME' (TABLE) (Cost=109 Card=152544 Bytes=5186496)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        457  consistent gets
          0  physical reads
          0  redo size
     810700  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
         33  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     160000  rows processed

SQL> SELECT id_v, csn_v,
  2         DECODE (COUNT (DISTINCT status_v) OVER (PARTITION BY id_v, csn_v),
  3                 1, status_v,
  4                 'FAIL') status
  5  FROM   table_name;

160000 rows selected.

Elapsed: 00:00:02.06

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1518 Card=152544 Bytes=5186496)
   1    0   WINDOW (SORT) (Cost=1518 Card=152544 Bytes=5186496)
   2    1     TABLE ACCESS (FULL) OF 'TABLE_NAME' (TABLE) (Cost=109 Card=152544 Bytes=5186496)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        457  consistent gets
          0  physical reads
          0  redo size
     810700  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
         33  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     160000  rows processed

SQL> SELECT id_v, csn_v,
  2         DECODE (COUNT (DISTINCT status_v) OVER (PARTITION BY id_v, csn_v),
  3                 1, status_v,
  4                 'FAIL') status
  5  FROM   table_name;

160000 rows selected.

Elapsed: 00:00:02.08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1518 Card=152544 Bytes=5186496)
   1    0   WINDOW (SORT) (Cost=1518 Card=152544 Bytes=5186496)
   2    1     TABLE ACCESS (FULL) OF 'TABLE_NAME' (TABLE) (Cost=109 Card=152544 Bytes=5186496)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        457  consistent gets
          0  physical reads
          0  redo size
     810700  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
         33  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     160000  rows processed

SQL> SELECT id_v, csn_v,
  2         CASE
  3            WHEN id_csn_all = id_csn_pass THEN 'PASS (ALL)'
  4            WHEN id_csn_all = id_csn_fail THEN 'FAIL (ALL)'
  5            WHEN id_csn_all = id_csn_unsuccess THEN 'UNSUCCESSFUL (ALL)'
  6            ELSE 'FAIL (MIXED)'
  7         END status_v
  8  FROM   (SELECT id_v, csn_v, status_v, COUNT (*) OVER (PARTITION BY id_v, csn_v) id_csn_all,
  9                 COUNT (CASE status_v
 10                           WHEN 'PASS' THEN 1
 11                        END) OVER (PARTITION BY id_v, csn_v) id_csn_pass,
 12                 COUNT (CASE status_v
 13                           WHEN 'FAIL' THEN 1
 14                        END) OVER (PARTITION BY id_v, csn_v) id_csn_fail,
 15                 COUNT (CASE status_v
 16                           WHEN 'UNSUCCESSFUL' THEN 1
 17                        END) OVER (PARTITION BY id_v, csn_v) id_csn_unsuccess
 18          FROM   table_name);

160000 rows selected.

Elapsed: 00:00:02.08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1518 Card=152544 Bytes=11898432)
   1    0   VIEW (Cost=1518 Card=152544 Bytes=11898432)
   2    1     WINDOW (SORT) (Cost=1518 Card=152544 Bytes=5186496)
   3    2       TABLE ACCESS (FULL) OF 'TABLE_NAME' (TABLE) (Cost=109 Card=152544 Bytes=5186496)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        457  consistent gets
          0  physical reads
          0  redo size
     810710  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
         33  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     160000  rows processed

SQL>
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 18 2003
Added on Oct 3 2003
#javaserver-faces
2 comments
106 views