Skip to Main Content
Forums

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!

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.

Why the Drop-down List is invisibility when click the button in Calendar?

738030Dec 8 2009 — edited Dec 8 2009
Hi all,
I create Dashboard Prompt with one Calendar control and two Drop-down List controls. I use the Dashboard Prompt in one Dashboard. When I click the button in Calendar to open Calendar Dialog, the two Drop-down List controls are invisibility. How can I fix this issue?

Regards,
Beavey

Comments

Nicolas Gasparotto

Hi,

Try this :

SQL> select * from tflag;

        ID       FLAG
---------- ----------
         1          1
         1          1
         1          0
         2          1
         2          1

SQL> select id
  2  from (select id, count(*) num_id, sum(flag) sum_flag from tflag group by id)
  3  where num_id=sum_flag
  4  /

        ID
----------
         2

SQL>

Nicolas.

146850
Nicolas' query is not correct if flag has 3 or more values.
So, more general method is this.
SELECT   ID
    FROM tflag
GROUP BY ID
  HAVING MIN (flag) = 1 AND MIN (flag) = MAX (flag)
for more SQL problems & questions, please visit
http://cafe.daum.net/oraclesqltuning
Nicolas Gasparotto

theaos,

Nicolas' query is not correct if flag has 3 or more values.

That's Ok, but the requirement of flag column sound to me like a flag with only two values : 0 or 1.

Even if your sql plain is very nice, if you permit me to complete my first answer, I can modify my query with adding decode :

SQL> select * from tflag;

        ID       FLAG
---------- ----------
         1          1
         1          1
         1          0
         2          1
         2          1
         3          3
         3          0
         3          0

8 rows selected.

SQL> select id
  2  from (select id, count(*) num_id, sum(flag) sum_flag from tflag group by id)
  3  where num_id=sum_flag
  4  /

        ID
----------
         2
         3 <-- This is false, as theaos said

SQL> ed
Wrote file afiedt.buf

  1  select id
  2  from (select id,
  3               count(*) num_id,
  4               sum(decode(flag,1,1,0)) sum_flag from tflag group by id)
  5* where num_id=sum_flag
SQL> /

        ID
----------
         2

SQL>

Nicolas.

Anyway, the OP can keep the theaos' query...
Message was edited by:
N. Gasparotto

146850
As you know, the last argument 0 of decode function is unnecessary in your modified query.
Thanks.

for more SQL problems & questions, please visit
http://cafe.daum.net/oraclesqltuning
shishu
Please try to use this query.

select id from temp
group by id
having min(flag)=max(flag)


Thanks

Shishu Paul
478769
Please try to use this query.

select id from temp
group by id
having min(flag)=max(flag)
The above query will also select the id that has all the flag values as 0.
So it can be modified as

select id from temp
group by id
having min(flag)=max(flag)
and max(flag)=1;
Nicolas Gasparotto
As you know, the last argument 0 of decode function is unnecessary in your modified query.
Yes theaos ;-) but I think it's more readable with 0.

Best regards,

Nicolas.
Laurent Schneider
SQL> select id from t having count(decode(flag,1,null,1))=0 group by id;
ID
----------
2

will return all rows where all id whose all flags are 1
Laurent Schneider
Please try to use this query.

select id from temp
group by id
having min(flag)=max(flag)
The above query will also select the id that has all
the flag values as 0.
So it can be modified as

select id from temp
group by id
having min(flag)=max(flag)
and max(flag)=1;
well, min(flag)=1 and max(flag)=1 will not work for nulls, but maybe this column is not nullable..
William Robertson
This is fun:
SELECT * FROM t t1
WHERE  1 = ALL
       ( SELECT flag FROM t
         WHERE  id = t1.id );
Laurent Schneider
very nice indeed !
John Spencer

Can I play too?

SELECT * FROM t o
WHERE NOT EXISTS (SELECT 1 FROM t i
                  WHERE o.id = o.id and
                        i.flag = 0);

or, to accomodate more than two flag values:

SELECT * FROM t o
WHERE NOT EXISTS (SELECT 1 FROM t i
                  WHERE o.id = t.id and
                        DECODE(i.flag, 1, 1, 0) = 0);

TIMTOWTDI
John

245482
Don't tell me that the good Mr. Spencer is a Perl afficionado. Oh the horror.
Laurent Schneider
select id from t minus select id from t where lnnvl(flag=1);

Message was edited by:
Laurent Schneider
of course the condition is flag=1 not id=1...
474126
SELECT ID FROM (
SELECT ID,COUNT(1) CNT FROM (SELECT ID,FLAG
FROM SAMPLE
GROUP BY ID,FLAG)
GROUP BY ID)
WHERE CNT=1
245482
It seems that you just want to count the number of flags that are not 1. This way you only hit the table once -- which is nice if there's a bit more data.
create table t (
  id  number,
  flag  number );

insert into t values(1,1);         
insert into t values(1,1);       
insert into t values(1,0);       
insert into t values(2,1);       
insert into t values(2,1);       
insert into t values(3,3);       
insert into t values(3,0);       
insert into t values(3,0);
commit;

SQL> SELECT   id
  2      FROM t
  3  GROUP BY id
  4    HAVING COUNT (DECODE (flag, 1, NULL, 'x')) = 0;

        ID
----------
         2

SQL>
245482
'Course I'm just re-stating what Nicolas meant to say. Theaos' solution has trouble with NULLs.
SQL> insert into t values(4,1);

1 row created.

SQL> insert into t values(4,NULL);

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> SELECT   ID
  2      FROM t
  3  GROUP BY ID
  4    HAVING MIN (flag) = 1 AND MIN (flag) = MAX (flag);

        ID
----------
         2
         4

SQL>
Nicolas Gasparotto

I have to say that the most funny query is the William's query, and work for null value.

SQL> create table t (
  2    id  number,
  3    flag  number );

Table created.

SQL>  
SQL> insert into t values(1,1);       

1 row created.

SQL> insert into t values(1,1);       

1 row created.

SQL> insert into t values(1,0);       

1 row created.

SQL> insert into t values(2,1);       

1 row created.

SQL> insert into t values(2,1);       

1 row created.

SQL> insert into t values(3,3);       

1 row created.

SQL> insert into t values(3,0);       

1 row created.

SQL> insert into t values(3,0);

1 row created.

SQL> insert into t values(4,1);

1 row created.

SQL> insert into t values(4,NULL);

1 row created.

SQL>  SELECT distinct id FROM t t1
    WHERE  1 = ALL
           ( SELECT flag FROM t
            WHERE  id = t1.id )
SQL> /

        ID
----------
         2

SQL>

Nicolas.

146850
Using group functions only,
SELECT   ID
    FROM t
GROUP BY ID
  HAVING SUM (flag) / COUNT (*) = 1 AND MAX (flag) = 1
for more SQL problems & questions, please visit
http://cafe.daum.net/oraclesqltuning
Laurent Schneider
still 3 aggregations when one is enough. check examples above
1 - 20
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 5 2010
Added on Dec 8 2009
#bi, #business-intelligence-applications, #business-intelligence-foundation, #business-intelligence-suite-enterprise-edition-obiee, #dashboard, #obiee, #prompt
2 comments
317 views