Re: Sqlplus question

Teresa Narvaez (teresa_at_Glue.umd.edu) wrote:
: Hello, I need help in writting a sqlplus code to do the following.
: I have two tables tab1 and tab2 with the following attributes.
 

: tab1 :
: Name Null? Type
: ------------------------------- -------- ----
: LAYOUT NOT NULL VARCHAR2(20)
: SUBNET_ID NOT NULL VARCHAR2(5)
: FAC_ID NOT NULL VARCHAR2(3)
: X_POS NOT NULL NUMBER(3)
: Y_POS NOT NULL NUMBER(3)
 
: tab2:
: Name Null? Type
: ------------------------------- -------- ----
: FAC_ID NOT NULL VARCHAR2(3)
: TRK_GRP_NUM NOT NULL NUMBER(4)
: SW_TRK_GRP_NUM NOT NULL NUMBER(4)
: SCLLI VARCHAR2(9)
: CLLI VARCHAR2(16)
: NM_TRK_GRP_TYPE NOT NULL NUMBER(1)
: DEST_FAC_ID VARCHAR2(3)
: DEST_SW_TRK_GRP_NUM NUMBER(4)
: UPD_DATE DATE

: I need to get all the fac_id, x_pos, dest_fac_id such
: that subnet_id = 'japan' and fac_id in tab1 = fac_id in tab2.
: Also, the dest_fac_id is itself a fac_id and i want to make
: sure that i get all the dest_fac_id's only for subnet_id='japan'.
 

: In other words, fac_id and dest_fac_id are names of
: nodes which i need to connect (i.e. fac_id ------ dest_fac_id).
: But I do not want to consider connecting fac_id to dest_fac_id if
: dest_fac_id is not in the subnet of japan. I tried acomplishing
: by joining tab1 and tab2, is there a way to do this without
: joining the tables??
 

: Thank you,
: -Teresa

If I understand your problem right, you need a sub-query to validate the dest_fac_id. try adding this subquery to your where clause:

      where tab1.fac_id = tab2.fac_id
      and tab2.dest_fac_id in 
         (select distinct fac_id from tab1
          where subnet_id = 'japan')

hope this helps,

rick

strictly my own opinions of course! Received on Tue Nov 07 1995 - 00:00:00 CET