topic Re: SELECT INNER JOIN vs WHERE in Application Development and Automation Discussions

SELECT INNER JOIN vs WHERE

Former Member — Thu, 06 Oct 2011 11:49:51 GMT

Hello

What is the best programming, putting selection in the inner join or in where clause. Below is some sample code that works but wondering if C~statu='X' would have been better in the Where part of the statement in stead as part of the inner join? Maybe don't need the Where in this select statment just code in the inner joins?

SELECT a_{lgnum a}bdatu b_{tanum b}tapos a_{tbnum b}tbpos b~werks

b_{lgort b}matnr b_{meins b}maktx b_{vsolm b}vltyp b~vlpla

b~nlpla

INTO TABLE gt_ltak

FROM ltak AS a

INNER JOIN ltap AS b ON b_{lgnum = a}lgnum AND

b_{tanum = a}tanum

INNER JOIN lrf_wkqu AS c ON c_{lgnum = a}lgnum AND

c~bname = sy-uname AND

c_{queue = a}queue AND

c~statu = 'X'

INNER JOIN zis_prog_vars AS d ON d~pgm = 'ZRF_TRANS_03_0100' AND

d~fld = l_priority AND

d_{val1 = a}tbpri

WHERE a~lgnum = '170' AND

a~tanum = '0000001399'.

Re: SELECT INNER JOIN vs WHERE

Former Member — Thu, 06 Oct 2011 11:53:57 GMT

i really would wonder if this select you coded worked at all or is compilable.

In join ONLY conditions that are neccessary to join the tables make sense. rest should be done in the where clause.

Re: SELECT INNER JOIN vs WHERE

Former Member — Thu, 06 Oct 2011 15:23:48 GMT

Hi,

Herewith the corrected version:


SELECT a~lgnum a~bdatu b~tanum b~tapos a~tbnum b~tbpos b~werks
       b~lgort b~matnr b~meins b~maktx b~vsolm b~vltyp b~vlpla b~nlpla

  INTO TABLE gt_ltak
  FROM ltak AS a

 INNER JOIN ltap AS b ON b~lgnum = a~lgnum AND
                         b~tanum = a~tanum   

 INNER JOIN lrf_wkqu AS c ON c~lgnum = a~lgnum AND
                             c~queue = a~queue AND

 INNER JOIN zis_prog_vars AS d ON d~val1 = a~tbpri

 WHERE a~lgnum = '170'
   AND a~tanum = '0000001399'
   AND c~bname = sy-uname
   AND c~statu = 'X' 
   AND d~pgm = 'ZRF_TRANS_03_0100'
   AND d~fld = l_priority.

Kr,