topic Re: Inner join in Application Development and Automation Discussions

Inner join

Former Member — Thu, 20 Sep 2007 07:02:47 GMT

Hi,

While using inner join can we compare 2 feilds with diiferent lengths.

We have used following coding but it is giving error. Plz help me to solve it & get reward.

SELECT P_{AGR_NAME P}LANGU P_{PROFILE P}PTEXT

INTO TABLE T_AGR_PROF

FROM AGR_PROF AS P

INNER JOIN

UST12 AS U

ON <b>U_{auth(10) = P}PROFILE</b>

WHERE U_{OBJCT in S_OBJCT AND U}FIELD in S_FIELD AND U~VON in S_VON.

Thanks in advance.

Re: Inner join

Former Member — Thu, 20 Sep 2007 07:24:03 GMT

hi,

try this,

SELECT P_{AGR_NAME P}LANGU P_{PROFILE P}PTEXT

INTO TABLE T_AGR_PROF

FROM AGR_PROF AS P

INNER JOIN

UST12 AS U

ON <b>U_{auth = P}PROFILE</b>

WHERE U_{OBJCT in S_OBJCT AND U}FIELD in S_FIELD AND U~VON in S_VON.

And we can compare 2 fields with diff length but here in inner join we have to write only the tables field name.

We can not write like U~auth(10) as u have written in your coding.

thanks

Dharmshta

Re: Inner join

Former Member — Thu, 20 Sep 2007 07:27:35 GMT

hi,

u cant explicitly give length for a field of table as it takes automatically and compares according to that.

SELECT P_{AGR_NAME P}LANGU P_{PROFILE P}PTEXT

INTO TABLE T_AGR_PROF

FROM AGR_PROF AS P

INNER JOIN

UST12 AS U

ON U_{auth = P}PROFILE

WHERE U_{OBJCT in S_OBJCT AND U}FIELD in S_FIELD AND U~VON in S_VON.

if helpful reward some points.

with regards,

Suresh Aluri.

Re: Inner join

Former Member — Thu, 20 Sep 2007 07:34:17 GMT

Hi...

we already tried as u said but it is not able to compare both fields as AUTH has 12 char long data and PROFILE has 10 char long ..so plz tell how to solve it .

apart from joins is there any other way to solve this and increase the performence of the program.

Re: Inner join

Former Member — Thu, 20 Sep 2007 07:39:32 GMT

I m not getting ur question because both the AUTH and PROFILE are 12 char long.

Re: Inner join

Former Member — Thu, 20 Sep 2007 07:48:15 GMT

ok but in data base data is like that in profile last two char are removed . so i need to compare first 10 char in auth to profile in database.. so how to solve it...

thanks for ur response

Re: Inner join

Former Member — Thu, 20 Sep 2007 07:55:36 GMT

If that is your requirement, try this.


SELECT P~AGR_NAME P~LANGU P~PROFILE P~PTEXT U~AUTH
INTO TABLE T_AGR_PROF
FROM AGR_PROF AS P
INNER JOIN
UST12 AS U
ON U~auth(10) = P~PROFILE
WHERE U~OBJCT in S_OBJCT AND U~FIELD in S_FIELD AND U~VON in S_VON.

Then concatenate the field auth (10 chars), before filtering the data in your internal table.


data: lv_auth(10) type c.

loop at T_AGR_PROF.
  lv_auth = T_AGR_PROF-auth+0(10).
  
  if lv_auth NE t_agr_prof-profile.
    delete t_agr_prof.
  endif.

  clear t_agr_prof.
endloop.

Re: Inner join

Former Member — Thu, 20 Sep 2007 08:08:55 GMT

hi,

Try this

DATA: BEGIN OF itab OCCURS 0,

agr_name LIKE agr_prof-agr_name,

langu LIKE agr_prof-langu,

profile LIKE agr_prof-profile,

ptext LIKE agr_prof-ptext,

END OF itab.

DATA: BEGIN OF itab1 OCCURS 0,

auth LIKE ust12-auth,

END OF itab1.

DATA: BEGIN OF final OCCURS 0,

agr_name LIKE agr_prof-agr_name,

langu LIKE agr_prof-langu,

profile LIKE agr_prof-profile,

ptext LIKE agr_prof-ptext,

END OF final.

SELECT * FROM agr_prof INTO CORRESPONDING FIELDS OF TABLE itab.

SELECT * FROM ust12 INTO CORRESPONDING FIELDS OF TABLE itab1.

LOOP AT itab.

LOOP AT itab1.

IF itab1-auth(10) = itab-profile(10).

MOVE-CORRESPONDING itab TO final.

APPEND final.

EXIT.

ENDIF.

ENDLOOP.

Re: Inner join

Former Member — Thu, 20 Sep 2007 08:56:15 GMT

HI,

DATA : BEGIN OF ITAB1 OCCURS 0,

AUTH. LIKE UST12-AUTH.,

END OF ITAB1.

DATA : BEGIN OF ITAB2 OCCURS 0,

AGR LIKE T_AGR_PROF-AGR,

LANGU LIKE T_AGR_PROF-LANGU,

NAME LIKE T_AGR_PROF-NAME,

PROFILE LIKE T_AGR_PROF-PROFILE,

PTEXT LIKE T_AGR_PROF-PTEXT,

END OF ITAB1.

SELECT PROFILE INTO TABLE ITAB1 FROM UST12.

LOOP AT ITAB1.

CONCATENATE ITAB1-AUTH.(10) INTO ITAB1-AUTH.

MODIFY ITAB1.

ENDLOOP.

SELECT AGR LANGU NAME PROFILE PTEXT INTO TABLE ITAB2 FROM T_AGR_PROF.

LOOP AT ITAB2.

CONCATENATE ITAB2-PROFILE(10) INTO ITAB2-PROFILE.

MODIFY ITAB2.

ENDLOOP.

LOOP AT ITAB2.

READ ITAB1 WITH KEY AUTH = ITAB2-PROFILE.

IF SY-SUBRC = 0 .

" APPEND IT NOW ALL TO OTHER INTERNAL TABLE OR WRITE HERE

ENDIF.

ENDLOOP.

I THINK IT WILL BE USEFUL ....

NOTE: I DINT CHECK FOR SYNTAX PLS CHECK IT...

REGARD,

KARTHIK

Re: Inner join

Former Member — Thu, 20 Sep 2007 09:23:52 GMT

write like this.

data : begin of IT_UST12 OCCURS 0,

u_auth like UST12-u_auth,

u_auth1(10) type C,

end of IT_UST12.

select u_auth from UST12 into table IT_UST12

WHERE OBJCT in S_OBJCT AND FIELD in S_FIELD AND VON in S_VON.

loop at it_ust12.

it_ust12-u_auth1 = it_ust12-u_auth+0(10).

modify it_ust12.

endloop.

sort IT_UST12 by u_auth1 u_auth.

if not IT_UST12 is initial.

SELECT AGR_NAME LANGU PROFILE PTEXT

INTO TABLE T_AGR_PROF

FROM AGR_PROF for all entries in IT_UST12

WHERE profile = IT_UST12-u_auth1.

endif.

reward if useful.

Amit Singla