Forum Stats

  • 3,872,464 Users
  • 2,266,426 Discussions
  • 7,911,213 Comments

Discussions

I want to get info of all the alternate parts of a BOM

979458
979458 Member Posts: 2
edited Jan 15, 2014 11:33AM in SQL & PL/SQL

there is BOM name A with comp

Comp  Quantity

C1          1

C2           0

C3          1

C4          1

C5         0

Alternate part is the compnent with quantity > 0 with subsequent comp with quantity 0 . in above case it is c1,c4.

How to find this with the help of SQL query.

Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,544 Red Diamond

    You mean like this?

    SQL> ed
    Wrote file afiedt.buf   1  with t(Comp, Quantity) as (select 'C1', 1 from dual union all
      2                             select 'C2', 0 from dual union all
      3                             select 'C3', 1 from dual union all
      4                             select 'C4', 1 from dual union all
      5                             select 'C5', 0 from dual)
      6  --
      7  -- end of test data, use below query against your own table
      8  --
      9  select comp, quantity
    10  from (
    11        select comp, quantity
    12              ,case when quantity > 0 and lead(quantity) over (order by comp) = 0 then 1 else 0 end as chk
    13        from t
    14       )
    15* where chk = 1
    SQL> / CO   QUANTITY
    -- ----------
    C1          1
    C4          1
  • Partha Sarathy S
    Partha Sarathy S Member Posts: 1,200 Silver Trophy
    edited Jan 15, 2014 11:40AM

    Like this?

    WITH T1(Comp,Quantity) AS (
    SELECT 'C1',1 FROM DUAL UNION ALL
    SELECT 'C2',0 FROM DUAL UNION ALL
    SELECT 'C3',1 FROM DUAL UNION ALL
    SELECT 'C4',1 FROM DUAL UNION ALL
    SELECT 'C5',0 FROM DUAL),
    T2 AS (
    SELECT t1.*,
           lead(quantity)OVER(ORDER BY comp) ld_quan
    from T1)
    SELECT comp,
            quantity 
    FROM T2
    WHERE quantity > 0
    AND ld_quan =0;
    OUTPUT:
    CO   QUANTITY
    -- ----------
    C1          1
    C4          1
    

This discussion has been closed.