Query help

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • excelproblem@hotmail.com
    #1

    Query help

    Hi,
    Is there a simple query to extract the rows as below

    Suppose the data is as follows

    Part# Assebly# Type
    -------- ------------- -------
    P1 A1 Yes
    P2 A2 Yes
    P1 A3 No
    P3 A4 No
    P1 A5 Yes
    P2 A6 Yes

    get all rows where at least one type field for a part# is "No". i.e.
    the result rows should be

    Part# Assebly# Type
    -------- ------------- -------
    P1 A1 Yes
    P1 A3 No
    P1 A5 Yes
    P3 A4 No

    the rows with part#P2 is not extracted because tpye field for all the
    P2 records is "Yes".


    So first I should pick up Part# with type "No" and then get all rows in
    the table with that part# irrespective of Type. And this table is
    having one million rows.

    Tags: None
    • Knut Stolze
      #2
      Re: Query help

      excelproblem@ho tmail.com wrote:
      Hi,
      Is there a simple query to extract the rows as below
      >
      Suppose the data is as follows
      >
      Part# Assebly# Type
      -------- ------------- -------
      P1 A1 Yes
      P2 A2 Yes
      P1 A3 No
      P3 A4 No
      P1 A5 Yes
      P2 A6 Yes
      >
      get all rows where at least one type field for a part# is "No". i.e.
      the result rows should be
      >
      Part# Assebly# Type
      -------- ------------- -------
      P1 A1 Yes
      P1 A3 No
      P1 A5 Yes
      P3 A4 No
      >
      the rows with part#P2 is not extracted because tpye field for all the
      P2 records is "Yes".
      >
      So first I should pick up Part# with type "No" and then get all rows in
      the table with that part# irrespective of Type. And this table is
      having one million rows.
      Before we solve your exercise for you here, it would be nice if you actually
      posted what you have tried so far.

      Hint: There is an EXISTS predicate in SQL. That should answer your
      question.

      --
      Knut Stolze
      DB2 Information Integration Development
      IBM Germany

        Comment

        • Serge Rielau
          #3
          Re: Query help

          excelproblem@ho tmail.com wrote:
          Hi,
          Is there a simple query to extract the rows as below
          >
          Suppose the data is as follows
          >
          Part# Assebly# Type
          -------- ------------- -------
          P1 A1 Yes
          P2 A2 Yes
          P1 A3 No
          P3 A4 No
          P1 A5 Yes
          P2 A6 Yes
          >
          get all rows where at least one type field for a part# is "No". i.e.
          the result rows should be
          >
          Part# Assebly# Type
          -------- ------------- -------
          P1 A1 Yes
          P1 A3 No
          P1 A5 Yes
          P3 A4 No
          >
          the rows with part#P2 is not extracted because tpye field for all the
          P2 records is "Yes".
          >
          >
          So first I should pick up Part# with type "No" and then get all rows in
          the table with that part# irrespective of Type. And this table is
          having one million rows.
          >
          SELECT.... FROM (SELECT ...
          MIN(Type) OVER (PARTITION BY part) AS mintype
          ....)
          WHERE mintype = 'No'


          --
          Serge Rielau
          DB2 Solutions Development
          IBM Toronto Lab

          IOD Conference
          http://www.ibm.com/software/data/ondemandbusiness/conf2006/

            Comment

            Previous template Next
            Working...