Creating view that compares two or more tables for criteria

• Paul Tedford

  SSC Rookie

  Points: 45

  More actions

  November 24, 2007 at 9:29 pm

  #377181

  Newbie to creating views that look at criteria in one table and compare it to another table.

  What I want to do is create a view that will

  1)grab BASE_ID AND LOT_ID from WORK_ORDER TABLE WHERE TYPE = W

  2)grab start date from WORK_ORDER_SCHED where BASE_ID and LOT_ID = WORK_ORDER BASE_ID and LOT_ID

  3)grab YES or NO for WORKORDER (BASE_ID and LOT_ID):

  a.YES – if all of the parts in REQUIREMENT table for that WORKORDER (BASE_ID and LOT_ID) have a calc_qty less then the PART tables ON_HAND_QTY

  b.No – if any of the parts in REQUIREMENT table for that workorder have a calc qty less then part tables on hand qty

  4)Grab YES if WORKORDER (BASE_ID and LOT_ID) exists in LABOR_TICKET table

  I can create the view that grabs 1, 2, and 4 – but I can’t figure out 3.

  For example here's some sample table data...

  TABLE: PART

  PART_IDON_HAND_QTY

  A050

  A140

  A220

  A330

  A440

  A550

  A610

  A730

  TABLE: WORK_ORDER

  BASE_IDLOT_IDTYPE

  123451W

  123461M

  123471W

  TABLE: REQUIREMENT

  BASE_IDLOT_IDPART_IDCALC_QTY

  123451A040

  123451A130

  123451A210

  123461A330

  123461A420

  123471A520

  123471A615

  123471A710

  TABLE: WORK_ORDER_SCHED

  BASE_IDLOT_IDSTART_DATE

  12345111/23/2007

  12347111/23/2007

  TABLE: LABOR_TICKET

  BASE_IDLOT_ID

  123451

  123471

  RESULTING VIEW SHOULD LOOK LIKE:

  BASE_IDLOT_IDSTART_DATESUFFICIENT_OHQSTARTED

  12345111/23/2007YESYES

  12347111/23/2007NOYES

  Any help would be greatly appreciated.

• Paul Tedford

  SSC Rookie

  Points: 45

  More actions

  November 25, 2007 at 4:23 pm

  #753835

  I apologize in advance as I'm sure "bumping" is frowned upon, just panicking since I can't create this view myself... where's the dunce smiley face 😀

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  November 26, 2007 at 2:18 am

  #753891

  Is this want your looking for?

  SELECTS.BASE_ID, S.LOT_ID, S.START_DATE,

  ( CASE WHEN R.R.BASE_ID IS NOT NULL THEN 'YES' ELSE 'NO' END ) AS SUFFICIENT_OHQ,

  'YES' AS STARTED

  FROMWORK_ORDER_SCHED S

  LEFT JOIN

  (

  SELECTR.BASE_ID, R.LOT_ID

  FROMREQUIREMENT R

  INNER JOIN PART P ON R.PART_ID = P.PART_ID AND R.CALC_QTY < P.ON_HOLD_QTY

  GROUP BY R.BASE_ID, R.LOT_ID

  ) R ON S.BASE_ID = R.BASE_ID AND S.LOT_ID = R.LOT_ID

  --Ramesh
  

  ---