Variable Grouping 3

[SkipVought]

Here's my sample table

[b]
OAI[/b]
OUT
AT
AT
IN
OUT
IN

The rules for a new column groupID are as follows:
[tt]
when OAI = 'OUT' then [prev groupID] + 1
else [prev groupID]
[/tt]
in order to get

[b]
OAI groupID[/b]
OUT  1
AT   1
AT   1
IN   1
OUT  2
IN   2

Your expert advice is appreciated.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[carp]

My first advice is to add at least one more column so you (a) have something useful to sort on and (b) have a way to distinguish between identical values (e.g., AT 1). Since Oracle does not guarantee you will get the same order of presentation from one query to the next, "[prev group_id]" does not appear to have any real meaning.

Second piece of advice is to give us more information (such as, "what is this and what are you really trying to do?") so we can give you better advice.

 

[SkipVought]

Yes, I have an OrderID and an OperationID as well as other columns that I used to generate the OAI column

order	oper	OAI
10000	0050	OUT
10000	0060	AT
10000	0065	AT
10000	0070	IN
10000	0120	OUT
10000	0131	IN

This is a data extract of manufacturing orders and operations that have been designated for offload to a vendor. So each of these groups of order/operations as associated with a vendor that is only designated on the OUT operation.

Up to this point, I have used a set of Lag and Lead analytic functions to generate the OUT, AT and IN, where the OUT means that the material goes out to the vendor, AT is an operation at the vendor and IN is a receiving inspection operation when the material returns. Each group of operations will then be aggregated by vendor for the purpose for displaying performance metrics.

I breadboarded it in Excel and now I'm attempting to do as much as what I had done in Excel in a query, prior to importing into a Qlikview application for interactive reporting.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[carp]

Would it be possible to use a sequence here? Then whenever you run into an OUT you could use mySeq.NEXTVAL to pick up a new number. Otherwise, you would use mySeq.CURRVAL. A simple DECODE or CASE statement would then do the trick, if I understand your requirements correctly.

 

[SkipVought]

carp, thanks for the suggestion.

I'm coding a pass thru and this did not seem to work.

[highlight]create sequence grpid[/highlight]

select
  ord
, opr
, wc
, ck
, tx
, oai
[highlight], case when oai='OUT to Vendor' then grpid.nextval else grpid.curval end grp[/highlight]

from
(
select
  ord
, opr
, wc
, ck
, tx
, case when wc like '%35Y'      then 'IN from Vendor'
       when ck = 'ZBOP'         then 'IN from Vendor'
       when tx like '%INSPECT%' then 'IN from Vendor'

       when wc like '%OUT'      then 'OUT to Vendor'
       when ck = 'ZBPR'         then 'OUT to Vendor'
       when tx like '%OFFLOAD%' then 'OUT to Vendor'

       when next_wc like '%OUT'      then 'IN from Vendor'
       when next_ck = 'ZBPR'         then 'IN from Vendor'

       when prev_wc like '%35Y'      then 'OUT to Vendor'
       when prev_ck = 'ZBOP'         then 'OUT to Vendor'
       when prev_tx like '%INSPECT%' then 'OUT to Vendor'
  else 'AT Vendor'
  end OAI

from
(
SELECT
  substr(oh.ORDER_NO,length(oh.ORDER_NO)-9,10)||'-'||op.SEQUENCE_APLFL ord
, op.OPERATION opr
, wc.WORK_CNTR wc
, op.CNTL_KEY  ck
, op.OPR_SHORT_TEXT tx

, lag (wc.work_cntr) over(partition by oh.ORDER_NO||'-'||op.SEQUENCE_APLFL order by oh.ORDER_NO||'-'||op.SEQUENCE_APLFL, op.OPERATION) prev_wc
, lead (wc.work_cntr) over(partition by oh.ORDER_NO||'-'||op.SEQUENCE_APLFL order by oh.ORDER_NO||'-'||op.SEQUENCE_APLFL, op.OPERATION) next_wc

, lag (op.CNTL_KEY) over(partition by oh.ORDER_NO||'-'||op.SEQUENCE_APLFL order by oh.ORDER_NO||'-'||op.SEQUENCE_APLFL, op.OPERATION) prev_ck
, lead (op.CNTL_KEY) over(partition by oh.ORDER_NO||'-'||op.SEQUENCE_APLFL order by oh.ORDER_NO||'-'||op.SEQUENCE_APLFL, op.OPERATION) next_ck

, lag (op.OPR_SHORT_TEXT) over(partition by oh.ORDER_NO||'-'||op.SEQUENCE_APLFL order by oh.ORDER_NO||'-'||op.SEQUENCE_APLFL, op.OPERATION) prev_tx
, lead (op.OPR_SHORT_TEXT) over(partition by oh.ORDER_NO||'-'||op.SEQUENCE_APLFL order by oh.ORDER_NO||'-'||op.SEQUENCE_APLFL, op.OPERATION) next_tx

FROM
  SIX_SIGMA.VS_AFKO_ORDER_HEADER oh
, SIX_SIGMA.VS_AFVC_ORDER_OPERATIONS op
, SIX_SIGMA.VS_CRHD_WORK_CENTER wc

WHERE oh.PLAN_NO = op.PLAN_NUM
  AND wc.RESOURCE_OBJ_ID = op.RESOURCE_OBJ_ID
  AND (substr(wc.WORK_CNTR,1,3) In ('161','165','167') or wc.WORK_CNTR='35Y0035Y')
  and oh.actual_rel_dt    >'00000000'

  and substr(oh.ORDER_NO,length(oh.ORDER_NO)-9,10)='1000038915-000000'

ORDER BY
  substr(oh.ORDER_NO,length(oh.ORDER_NO)-9,10)||'-'||op.SEQUENCE_APLFL
, op.OPERATION
)
)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[carp]

A couple of things:
1. There are two "r"s in currval.
2. You have to do at least one NEXTVAL in a session before a CURRVAL will return anything. So this may make a sequence impracticable unless you can rerig your query to get all of the OUTs first (so your session is doing a NEXTVAL before it tries to do any CURRVALs). And this raises the problem of the CURRVAL getting ratcheted up too high so that when you get to the non-OUTs, CURRVAL does not apply to the non-OUT records. This might drive you into using a procedure that would process your table in two phases:

Phase 1 - Update the GRP value on all of the OUTs with a NULL GRP value to grpid.NEXTVAL.
Phase 2 - For all non-OUTs with a NULL GRP value:
Update the non-OUTs with the GRP of the corresponding OUT record.

There is probably a more elegant approach, but it's not coming to me just now.

 

[ChrisHunt]

Don't have time to research a solution, but I'd look at using analytic functions to provide a running total of [tt]DECODE(oai,'OUT',1,0)[/tt]

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[ChrisHunt]

OK, this seems to do what you originally asked for:

WITH my_table AS     -- this bit just generates some test data
(SELECT 10000 orderno, '0050' oper,  'OUT' oai FROM dual UNION ALL
 SELECT 10000, '0060', 'AT' FROM dual UNION ALL
 SELECT 10000, '0065', 'AT' FROM dual UNION ALL
 SELECT 10000, '0070', 'IN' FROM dual UNION ALL
 SELECT 10000, '0120', 'OUT' FROM dual UNION ALL
 SELECT 10000, '0131', 'IN' FROM dual)

SELECT orderno,       -- the real query starts here
       oper,
       oai,
       SUM(DECODE(oai,'OUT',1,0)) OVER (PARTITION BY orderno ORDER BY orderno,oper) groupid
FROM   my_table
ORDER BY orderno,oper

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[SkipVought]

Carp & Chris,

Thanks to both of you for your contributions. When I get back to my office I'll begin testing. Just wanted to let you both know that you are appreciated!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Chris,

You da man! If I could make you an honorary Texas Ranger, I would; that is, if it would not insult your British sensibilities. At the least, I raise a pint of Purple Star to you. ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Oh, and I did make one small change, by removing the Partition By orderid, the group values continue to incriment without regard to a new orderid, which is what I needed, but never specified in my request.

The SUM(Decode()) was GENIUS!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[carp]

Chris - a splendid answer. Let me by you a star!