2 Replies Latest reply on May 28, 2007 12:26 PM by Dan Bracuk

    urgent help on minimize code

    Nick201
      I have 3 different type " 1. state 2. COM 3 DC All three below query are same but base on type it get result.

      Instead of writting three time. Is there any way i can write query 1 time and pass three value one by one.

      I want to pass type one by one because All three get different reuslt and each one has their own section.
      LIKE
      STATE
      IL
      MI
      WI
      AL

      COM
      AG
      DD
      EE
      etc

      Select a.ID,
      a.description,
      1 as 'ID1'
      From Web_Lookup a,Webinfo b
      Where a.id=b.id
      And b.ABC_number='A0001'
      And a.type='State'
      union
      Select
      a.ID,
      a.description,
      -1 as 'ID1'
      From Web_Lookup a
      Where a.type='State'
      AND ID not in (Select a.ID
      From Web_Lookup a,Webinfo b
      Where a.ID=b.ID
      And b.ABC_number='='A0001''


      Select a.ID,
      a.description,
      1 as 'ID1'
      From Web_Lookup a,Webinfo b
      Where a.id=b.id
      And b.ABC_number='A0001'
      And a.type='COM'
      union
      Select
      a.ID,
      a.description,
      -1 as 'ID1'
      From Web_Lookup a
      Where a.type='COM'
      AND ID not in (Select a.ID
      From Web_Lookup a,Webinfo b
      Where a.ID=b.ID
      And b.ABC_number=='A0001'

      Select a.ID,
      a.description,
      1 as 'ID1'
      From Web_Lookup a,Webinfo b
      Where a.id=b.id
      And b.ABC_number='A0001'
      And a.AOA_type='DC'
      union
      Select
      a.ID,
      a.description,
      -1 as 'ID1'
      From Web_Lookup a
      Where a.type='DC'
      AND ID not in (Select a.ID
      From Web_Lookup a,Webinfo b
      Where a.ID=b.ID
      And b.ABC_number='A0001'