sql help

[Former Member]

Dear all,

could plz suggest me in structuring the Select statement. and my requirement is as follws:

i have 2 tables cus_pers and cus_phone.

cus_id is the primary key. got 1,2,3 in cus_id.

in cus_phone table:

cus_id phone no phone type------three fields

1 11 p

1 22 m

1 33 h

2 10 p

2 23 h

i want result as

cus_id mobile no(m) house no(h) pager no(p)

1 22 33 11

2 null 23 10

all mobile no houseno and pager no resides in same field phone no.

i need to select them by phone tye.

thanks in advance

[Former Member]

Hi Srini,

Why don't you make a internal of both the table and select data from it as:

select cus_id phoneno phonetype from cus_pers into corresponding fields of table ITAB where cus_id = itab-....

here ITAB is your internal table.

[dani_mn]

Hi,

declare a internal table with four fields, like this.

Data: begin of itab occurs 0,
      cus_id(10),
      mob_n(10),
      house_n(10),
      pager_n(10),
      end of itab.

then select from the two tables, and check the type of the number. depending on type insert in corresponding value. like.

SELECT ...........

If number_type = 'M'.
   itab-mob_n = phone_no.
elsif number_type = 'H'.
   itab-house_no = phone_no.
elseif number_type = 'P'.
   itab-pager_no = phone_no.
endif.

 READ TABLE itab with key cus_id = cus_phone-cus_id.
   IF sy-subrc = 0.
     MODIFY itab index sy-index.
   else.
     APPEND itab.
   ENDIF.

ENDSELECT.

Regards,

Wasim Ahmed

[Former Member]

Hi,

thanks alot. i completely forgot the basic option.

points awarded for both of u.

[Former Member]

Srini,

I think this would be the better way:

data: itab_phone like cus_phone occurs 0 with header line.

data: begin of itab_out occurs 0,
       cus_id(10) 
       mobileno(15)
       houseno(15)
       pagerno(15)
      end of itab_out.

select cust_id phone_no phone_type into itab_phone from cus_phone.


loop at itab_phone.

itab_out-id = itab_phone-id.

if itab_phone-phone_type eq 'M'.
 itab_out-mobileno = itab_phone-phone_no.
elseif itab_phone-phone_type eq 'H'.
 itab_out-houseno = itab_phone-phone_no.
elseif itab_phone-phone_type eq 'P'.
 itab_out-pagerno = itab_phone-phone_no.
endif.

append  itab_out.

endif.
endloop.

Rgds,

TM

[Former Member]

Hi,

Data jtab type table of cus_phone.

Data: Begin of itab occurs 0,

cus_id(10),

mobile no_m(10),

house no_h(10),

pager no_p(10),

End of itab.

Select * from cus_phone into table jtab.

sort jtab by cus_id.

loop at jtab

itab-cus_id = jtab-cus_id.

if type = 'p'.

itab-pager no_p = jtab-phone no.

endif.

if type = 'm'.

itab-pager no_m = jtab-phone no.

endif.

if type = 'h'.

itab-pager no_h = jtab-phone no.

endif.

append itab.

endloop.

Regards,

Tanveer.

<b>Please mark helpful answers</b>