APEX

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!

Show in Dynamic action

SrinivashFeb 28 2013 — edited Feb 28 2013

I have created a region which is a dynamic report based on a item value(P1_SHOW).when the page loads this region is hidden using a DA (on page load).at some point I have a super lov item which wen changes to any value folowing DA works
IF NOT NULL
sets the item value P1_SV
Refreshes the region containing the report.
Show the region report

IF NULL
Hides the region report

Here my problem is when i select a value it first shows No data found and then changes according to the item set.
or
If we are changing a value it shows the report then we clear the super lov then the region is hidden and when we give another value for few seconds it shows the previous data then shows the refreshed data.

I tried debugging it is all happening because of the last show action.

I don't want that previous data to be shown for few seconds pls help

This post has been answered by dmcghan on Feb 28 2013

Jump to Answer

465815

try -

select person, id_state from project where id_state in (2, 3, 4) order by  1, 2;

Amiel
oppes -misedd the part that syas all state...

Message was edited by:
Amiel D.

569964

select person, id_state from project where id_state in (2, 3, 4) order by 1, 2;

Nah, this returns all of the records, not just the ones with 2, 3 and 4.

I can't really think of a simpler way of doing it yet lol

Isn't it annoying when you have a simple thing you want to do, but you can't seem to do it?

Oracle is like that.

-EDIT-

hah, found a nifty way of doing it... it's a little bit dodgy, but it works!! :D

select person
from project
where id_state in (2, 3, 4)
group by person
having count(*) = 3

-EDIT 2-

Hah, nice work Volder!

I think you beat me =)

572471

SQL> with t as (select 'Carl' PERSON, 3  ID_STATE from dual union all
  2             select 'Carl' PERSON, 4  ID_STATE from dual union all
  3             select 'Joe' PERSON, 3  ID_STATE from dual union all
  4             select 'Mark' PERSON, 2  ID_STATE from dual union all
  5             select 'Mark' PERSON, 3  ID_STATE from dual union all
  6             select 'Mark' PERSON, 4  ID_STATE from dual union all
  7             select 'Paul' PERSON, 1  ID_STATE from dual union all
  8             select 'Paul' PERSON, 2  ID_STATE from dual union all
  9             select 'Sam' PERSON, 2  ID_STATE from dual union all
 10             select 'Sam' PERSON, 3  ID_STATE from dual)
 11             --
 12             select person from t
 13             where id_state in (2,3,4)
 14             group by person
 15             having count(distinct id_state)=3
 16  /

PERSON
------
Mark

SQL>

Aketi Jyuuzou

with t as (select 'Carl' as PERSON,3 as ID_STATE from dual
union all select 'Carl',4 from dual
union all select 'Joe' ,3 from dual
union all select 'Mark',2 from dual
union all select 'Mark',3 from dual
union all select 'Mark',4 from dual
union all select 'Paul',1 from dual
union all select 'Paul',2 from dual
union all select 'Sam' ,2 from dual
union all select 'Sam' ,3 from dual
union all select 'Wendy' ,2 from dual
union all select 'Wendy' ,2 from dual
union all select 'Wendy' ,3 from dual
union all select 'Wendy' ,3 from dual
union all select 'Wendy' ,4 from dual
union all select 'John' ,1 from dual
union all select 'John' ,2 from dual
union all select 'John' ,2 from dual
union all select 'John' ,3 from dual
union all select 'Tom' ,1 from dual
union all select 'Tom' ,2 from dual
union all select 'Tom' ,3 from dual
union all select 'Tom' ,4 from dual)
select person
  from t
group by person
having max(case when ID_STATE = 2 then 1 else 0 end)
     * max(case when ID_STATE = 3 then 1 else 0 end)
     * max(case when ID_STATE = 4 then 1 else 0 end)
     * min(case when ID_STATE in(2,3,4) then 1 else 0 end) = 1;

PERSON
------
Mark
Wendy

I have used this Logic.
http://en.wikipedia.org/wiki/First-order_logic

"max(case when P(X) then 1 else 0 end) = 1"  is for some X:P(X)
"min(case when P(X) then 1 else 0 end) = 1"  is for all  X:P(X)
"max(case when P(X) then 0 else 1 end) = 1"  is for some X:not(P(X))
"min(case when P(X) then 0 else 1 end) = 1"  is for all  X:not(P(X))

therefore, in having clause I have used Boolean arithmetic (http://www.allaboutcircuits.com/vol_4/chpt_7/2.html)
which "a And b And c And D" is "a * b * c * d = 1"

similar threads
551061
550921
526715

569964

Hello Aketi

Unfortunately your query does not return the correct results...

eg. John has 2, 3 and 4, yet he is not included in your results.

Interesting though :)

Knock out the last line and you'll get the right answer though.

select person
from project
group by person
having max(case when ID_STATE = 2 then 1 else 0 end)
     * max(case when ID_STATE = 3 then 1 else 0 end)
     * max(case when ID_STATE = 4 then 1 else 0 end) = 1

or similarly:

select person
from project
group by person
having max(case when ID_STATE = 2 then 1 else 0 end) = 1
   and max(case when ID_STATE = 3 then 1 else 0 end) = 1
   and max(case when ID_STATE = 4 then 1 else 0 end) = 1

576659

select person from project where id_state in(2,3,4) group by person having count(person)=3

thanks
hari

null

569964

select person from project where id_state in(2,3,4) group by person having count=3

Hehe, 3rd time that solution has been posted ^^

Aketi Jyuuzou

Unfortunately your query does not return the correct results...

Wow!
My English is poor.
I misunderstood request.
"min(case when ID_STATE in(2,3,4) then 1 else 0 end)" was extra.

a
max(case when ID_STATE = 2 then 1 else 0 end) = 1
This means that "ID_STATE = 2" exists.

b
max(case when ID_STATE = 3 then 1 else 0 end) = 1
This means that "ID_STATE = 3" exists.

c
max(case when ID_STATE = 4 then 1 else 0 end) = 1
This means that "ID_STATE = 4" exists.

d
min(case when ID_STATE in(2,3,4) then 1 else 0 end) = 1
This means that all rows are "ID_STATE in (2,3,4)".

I understood that request was "a And b And c And d".
But correct request was "a And b And c".

576659

i didnt see u posted there.

Message was edited by:
Hariharan M K

RAMJANE

Try this one

select person,count(id_state) from project group by person having count(id_state)=3;

This will give Mark as o/p.

569964

select person,count(id_state) from project group by person having count(id_state)=3;

Problem is that this will select any people with exactly 3 rows, so someone with 1, 2, 6 would also be in.

RAMJANE

ya sorry for my mistake.

This is the latest query for doing this

select person,count(case when id_state=2 then id_state when id_state=3 then id_state when id_state=4 then id_state end) as count1 from project group by person having count1=3;

Try this one.

569964

This is probably the shortest query though:

select person from project
where id_state in (2, 3, 4)
group by person
having count(*) = 3

RAMJANE

you are right. What i was thinking that in case of IN it will consider all others also. Now it's time to recall all the stuff for me.

572471

This is probably the shortest query though:

but would give wrong result in case duplicate records exist in the table!

RAMJANE

This is probably the shortest query though:

but would give wrong result in case duplicate records
exist in the table!

but would give wrong result in case duplicate records

exist in the table!

What do you mean by duplicate records in table?
any example.

578885

Hi,
lets suppose we have data as

SQL> select * from PROJECT6030 order by 2;

ID_PROJECT PERSON       ID_STATE LAST_UPDA
---------- ---------- ---------- ---------
         6 Carl                4 22-JAN-05
         5 Carl                3 08-DEC-04
         1 Joe                 3 08-AUG-07
         2 Mark                2 05-JUL-07
         4 Mark                4 14-MAR-07
         3 Mark 3 27-JUN-07
         8 Mark                3 10-MAY-07
         8 Paul                1 30-JUL-07
         9 Paul                2 02-JUN-06
         7 Sam                 3 11-JUL-06
         7 Sam                 2 15-JUN-06

11 rows selected.

In this case our quiery should select Mark as Mark has all three Id states.

Now consider the quiery.

SQL> ed
Wrote file afiedt.buf

  1  select person from project6030
  2  where id_state in (2, 3, 4)
  3  group by person
  4* having count(*) = 3
  5  /

no rows selected

when you group by count(*) we see that no person is having 3 rows of data.

now lets see another quiery which Volder had posted in the begining.

SQL> ed
Wrote file afiedt.buf

  1  select person from project6030
  2  where id_state in (2, 3, 4)
  3  group by person
  4* having count(distinct id_state) = 3
SQL> /

PERSON
----------
Mark

Thank you all for your responses!!

RAMJANE

Thnks User575882 to clear my doubt.
You got the solutioon of your problem?

William Robertson

More discussion and suggestions for relational division:
groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/576ea61b1a93469b/74a1a03238b6d97b?lnk=gst

@NightCabbage:
> Oracle is like that.

Perhaps, but relational division has historically always been tricky in the SQL language. I don't know whether other vendors make this type of query any easier.

William Robertson

...so using COLLECT and SUBMULTISET as in Maxim Demenko's post on c.d.o.m, we get this:

WITH project AS
      ( select 'Carl' person, 3 id_state from dual union all
        select 'Carl', 4 from dual union all
        select 'Joe',  3 from dual union all
        select 'Mark', 2 from dual union all
        select 'Mark', 3 from dual union all
        select 'Mark', 4 from dual union all
        select 'Paul', 1 from dual union all
        select 'Paul', 2 from dual union all
        select 'Sam',  2 from dual union all
        select 'Sam',  3 from dual )
--
SELECT person
FROM   project
GROUP  BY person
HAVING integer_tt(2,3,4) SUBMULTISET OF CAST(COLLECT(id_state) AS integer_tt) ;

"INTEGER_TT" is my nested table collection type, defined as

CREATE TYPE integer_tt AS TABLE OF INTEGER

569964

Volder

but would give wrong result in case duplicate records exist in the table!

hehe, yup! But I was trying to make it as small as possible.

You're dead right though, distinct id_state should be used instead of *

Also, I do wish Oracle had better set capabilities, etc.

There are always some things (not just sets) that seem really easy to do... until you actually try to implement them in SQL...

578885

Wouldn't it be nice if we had a set operator
say 'INALL' which would do the job!!

Actually I was looking for an answer like this as you can see from the subject line!

William Robertson

> Also, I do wish Oracle had better set capabilities, etc.

As I'm sure you know, relational division is a notoriously tricky challenge in SQL and many people have said that it highlights the weakness of SQL itself (although opinion is divided on whether it is a very common requirement or a rare and exotic one). I'd certainly be interested if Oracle added some new set operator to help with this but I doubt that it will ever be straightforward.

What else is missing, out of interest?

Vadim Tropashko-Oracle

This is rather innovative technique! However, the queries with explicit set operations are much more intuitive

select person from t where id_state = 2
intersect
select person from t where id_state = 3
intersect
select person from t where id_state = 4

which returns all the persons who's set of id_states contains {2,3,4}, and

select person from t where id_state = 2
intersect
select person from t where id_state = 3
intersect
select person from t where id_state = 4
minus
select person from t where id_state not in (2,3,4)

which returns all the persons who's set of id_states is exactly {2,3,4}.

Vadim Tropashko-Oracle

Pretending that we cast votes here, I would prefer extending the IN operator to handle subqueries for both operands, e.g.

select distinct Name from ApplicantSkills i
where (select Language from JobRequirements ii 
           where ii.Name = i.Name) 
   IN 
          (select Language from ApplicantSkills)

where IN operator is interpreted as a "subset of"

Without an explicit subset relation available we have to express it as emptiness of the difference between the two sets. Formally
A ⊆ B
is equivalent to
A \ B = ∅
Applied to our case it allows us to transform our rough first attempt to a legitimate SQL query

 
select distinct Name from ApplicantSkills i
where not exists ( 
    select Language from ApplicantSkills
    minus
    select Language from JobRequirements ii 
    where ii.Name = i.Name
)

So if the the IN or SUBSET OF operator would rewrite to the above "not exists" predicate, then the feature is minuscule.

Message was edited by:
Vadim Tropashko

William Robertson

That would make sense.

Or, perhaps SUBSET OF would be easier to read (not that the inventors of MODEL and partition outer joins seem to care about syntax being easy to read).

45736

Aketi's solution and COLLECT - SUBMULTISET seems to be two general solutions.

However, between these two solutions which will bring more "strain" on the database? From previous discussion on COLLECT in this forum it was shown that COLLECT generates more recursive calls. Does more recursive call means more "strain" on the database?

Thanks,

Raman

45736

My apologies...three solutions...

the third one is:

SQL> ed
Wrote file afiedt.buf

1 select person from project6030
2 where id_state in (2, 3, 4)
3 group by person
4* having count(distinct id_state) = 3
SQL> /

Raman

William Robertson

So in our people/states example with an explicitly specified set of states, that becomes:

WITH projects AS
      ( select 'Carl' person, 3 id_state from dual union all
        select 'Carl', 4 from dual union all
        select 'Joe',  3 from dual union all
        select 'Mark', 2 from dual union all
        select 'Mark', 3 from dual union all
        select 'Mark', 4 from dual union all
        select 'Paul', 1 from dual union all
        select 'Paul', 2 from dual union all
        select 'Sam',  2 from dual union all
        select 'Sam',  3 from dual )
   , people AS
      ( SELECT DISTINCT person FROM projects )
--
SELECT person
FROM   people p
WHERE  NOT EXISTS
       ( SELECT column_value FROM TABLE(INTEGER_TT(2,3,4))
         MINUS
         SELECT id_state FROM projects WHERE person = p.person );

Nice.

Satyaki_De

William i've tried this solution and it shows --

satyaki>
satyaki>CREATE TYPE integer_tt AS TABLE OF INTEGER;
  2  /

Type created.

satyaki>
satyaki>
satyaki>WITH project AS
  2        ( select 'Carl' person, 3 id_state from dual union all
  3          select 'Carl', 4 from dual union all
  4          select 'Joe',  3 from dual union all
  5          select 'Mark', 2 from dual union all
  6          select 'Mark', 3 from dual union all
  7          select 'Mark', 4 from dual union all
  8          select 'Paul', 1 from dual union all
  9          select 'Paul', 2 from dual union all
 10          select 'Sam',  2 from dual union all
 11          select 'Sam',  3 from dual )
 12  SELECT person
 13  FROM   project
 14  GROUP  BY person
 15  HAVING integer_tt(2,3,4) SUBMULTISET OF CAST(COLLECT(id_state) AS integer_tt) ;
HAVING integer_tt(2,3,4) SUBMULTISET OF CAST(COLLECT(id_state) AS integer_tt)
                         *
ERROR at line 15:
ORA-00920: invalid relational operator


satyaki>

Is it because i'm using Oracle 9i, so it is giving me an error?

Regards.

Satyaki De.

William Robertson

Yes, the multiset operators are a 10g feature.

1 - 31

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 28 2013

Added on Feb 28 2013

#apex, #apex-discussions, #image, #javascript, #report

10 comments

319 views

APEX

Show in Dynamic action

Comments

Post Details