Thanks for all the response.
But now.. I am asked to use CUBE analytical function to do this.
is this doable?
select
case when MAX_ORD = MAX_ACTION_TAKEN then MIN_ACTION_NOT_TAKEN
when MAX_ORD <> MAX_ACTION_TAKEN then MAX_ACTION_TAKEN + 1
when MAX_ACTION_TAKEN is null and NOT_TAKEN_ACTION > 0 then
MIN_ACTION_NOT_TAKEN end as next_ord
from (
select
max(ORDER) max_ord,
max(case when ACTION_flag = 'Y' then ORDER end) MAX_ACTION_TAKEN,
SUM(case when ACTION_flag is null then 1 else 0 end)
NOT_TAKEN_ACTION,
min(case when ACTION_flag is null then ORDER end)
MIN_ACTION_NOT_TAKEN
FROM participants
order by ORDER);
On Fri, Feb 18, 2011 at 6:51 AM, Trail <andrew.tr...@gnb.ca> wrote:
> Thought I'd throw in my $.02
>
> Created a table test_sql (ord number, flg varchar2(1)); inserted
> test records as below. This seems to work (though perhaps I am over
> simplifying):
>
> SELECT CASE WHEN max_flg = 'N'
> THEN (select min(ord)
> from test_sql
> where flg = 'N'
> and ord > NVL((select max(ord)
> from test_sql
> where flg = 'Y'),0) )
> ELSE
> (select min(ord)
> from test_sql
> where flg = 'N')
> END selected_ord
> FROM (select flg max_flg
> from test_sql
> where ord = (select max(ord) from test_sql))
>
> On Feb 17, 7:43 pm, gayathri Dev <gd0...@gmail.com> wrote:
> > sorry Mike..
> > didnt realised the new message.. going thr your response now.
> >
> >
> >
> > On Thu, Feb 17, 2011 at 3:41 PM, gayathri Dev <gd0...@gmail.com> wrote:
> > > I just realised that the order will be in sequence. that made it
> better...
> > > this is what i have tried.. but still i am sure.. there can be a better
> > > one.. please suggest.
> >
> > > select
> > > case when MAX_ORD = MAX_ACTION_TAKEN then MIN_ACTION_NOT_TAKEN
> > > when MAX_ORD <> MAX_ACTION_TAKEN then MAX_ACTION_TAKEN + 1
> > > when MAX_ACTION_TAKEN is null and NOT_TAKEN_ACTION > 0 then
> > > MIN_ACTION_NOT_TAKEN end as next_ord
> > > from (
> > > select
> > > max(ORDER) max_ord,
> > > max(case when ACTION_flag = 'Y' then ORDER end)
> MAX_ACTION_TAKEN,
> > > SUM(case when ACTION_flag is null then 1 else 0 end)
> > > NOT_TAKEN_ACTION,
> > > min(case when ACTION_flag is null then ORDER end)
> > > MIN_ACTION_NOT_TAKEN
> > > FROM participants
> > > order by ORDER);
> >
> > > On Thu, Feb 17, 2011 at 2:08 PM, gayathri Dev <gd0...@gmail.com>
> wrote:
> >
> > >> Hi Avinash,
> >
> > >> it should pick 2
> >
> > >> thanks
> >
> > >> On Thu, Feb 17, 2011 at 1:56 PM, Avinash Naidu <avisna...@gmail.com
> >wrote:
> >
> > >>> order action_flag
> > >>> 2 N
> > >>> 4 Y
> > >>> 6 N
> > >>> 7 N
> > >>> 8 Y
> > >>> should pick 2 or 6??
> >
> > >>> On Thu, Feb 17, 2011 at 1:31 PM, gayathri Dev <gd0...@gmail.com
> >wrote:
> >
> > >>>> Hi all,
> >
> > >>>> I am seriously thinking I need a good teacher who can teach me
> > >>>> analytical functions.
> > >>>> But with a min time i have got to resolve the hurdle I have.
> > >>>> Below are the examples..
> > >>>> basically I shld check the last order's action flag if it is Y, then
> I
> > >>>> shld pick the min order whose action_flag is N
> > >>>> else if the last order's action flag is N, then I shld pick the
> greatest
> > >>>> order who has taken action(action_flag = 'Y')
> > >>>> and choose the next order after him.
> >
> > >>>> order action_flag
> > >>>> 2 Y
> > >>>> 4 N
> > >>>> 6 N
> > >>>> 7 N
> > >>>> 8 Y
> > >>>> should pick 4
> >
> > >>>> order action_flag
> > >>>> 2 Y
> > >>>> 4 N
> > >>>> 6 Y
> > >>>> 7 N
> > >>>> 8 N
> > >>>> should select 7
> >
> > >>>> order action_flag
> > >>>> 2 N
> > >>>> 4 N
> > >>>> 6 N
> > >>>> 7 N
> > >>>> 8 N
> > >>>> should select 2
> >
> > >>>> order action_flag
> > >>>> 2 N
> > >>>> 4 Y
> > >>>> 6 N
> > >>>> 7 N
> > >>>> 8 N
> > >>>> 12 Y
> > >>>> 14 N
> > >>>> 15 N
> > >>>> should select 14
> >
> > >>>> Please help me building this query.
> >
> > >>>> Thanks in advance
> >
> > >>>> --
> > >>>> You received this message because you are subscribed to the Google
> > >>>> Groups "Oracle PL/SQL" group.
> > >>>> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> > >>>> To unsubscribe from this group, send email to
> > >>>> oracle-plsql-unsubscr...@googlegroups.com
> > >>>> For more options, visit this group at
> > >>>>http://groups.google.com/group/Oracle-PLSQL?hl=en
> >
> > >>> --
> > >>> You received this message because you are subscribed to the Google
> > >>> Groups "Oracle PL/SQL" group.
> > >>> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> > >>> To unsubscribe from this group, send email to
> > >>> oracle-plsql-unsubscr...@googlegroups.com
> > >>> For more options, visit this group at
> > >>>http://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text -
> >
> > - Show quoted text -
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
