topic Inner Join in Application Development and Automation Discussions

Inner Join

Former Member — Mon, 08 Feb 2016 18:24:55 GMT

Hi,

Please help me with this coding . When I am trying to execute one inner join it always getting Sy-subrc is equal to 4. when I am trying to execute without any condition also getting 4. When I am checking the table the valid data are available in the table ,

Below mention my code

SELECT s~credit_sgmnt s~credit_limit s~limit_valid_date

p~partner p~credit_group p~risk_class p~new_cust p~limit_rule

k~kunnr k~ktokd v~vkorg

INTO CORRESPONDING FIELDS OF TABLE gt_ukm_wrk

FROM ( ukmbp_cms_sgm AS s

INNER JOIN ukmbp_cms AS p ON s~partner = p~partner

INNER JOIN kna1 AS k ON s~partner = k~kunnr

INNER JOIN knvv AS v ON s~partner = v~kunnr )

WHERE s~partner IN so_partr

AND s~credit_sgmnt IN so_sgmnt " lt_selopt

AND p~credit_group IN gr_group "so_group

AND p~risk_class IN so_risk

AND k~katr1 IN so_katr1

AND k~katr2 IN so_katr2

AND k~katr3 IN so_katr3

AND k~katr4 IN so_katr4

AND k~katr7 IN so_katr7

AND k~katr8 IN so_katr8

AND k~katr9 IN so_katr9

AND k~katr10 IN so_kat10

AND k~kukla IN so_kukla

AND k~ktokd = p_cgroup

AND v~vkorg IN so_vkorg

AND v~vkgrp IN so_vkgrp

AND v~vkbur IN so_vkbur

AND v~bzirk IN so_bzirk

AND v~vtweg IN so_vtweg.

Please help me rectify the issue.....

Thanks in advance

Regards

Visakh M K

Re: Inner Join

jhilden — Mon, 08 Feb 2016 19:33:16 GMT

Nothing immediately obvious, though with that many "IN" operands, it could easily be any one of them that is causing the empty set.

Try reducing the complexity by removing one table at a time, or one where condition at a time, until results come back.

Jeremy

Re: Inner Join

paul_max1 — Tue, 09 Feb 2016 10:20:11 GMT

Check the link between partner and kunnr.

It's mostly probable that there are some conversions or extra zeros or something...

Where did you checked the values?

Re: Inner Join

sab125 — Tue, 09 Feb 2016 11:25:57 GMT

As said, the issue could come from condition on PARTNER & KUNNR

KNA1-KUNNR type KUNNR
UKMBP_CMS_SGM-PARTNER type BU_PARTNER
UKMBP_CMS-PARTNER type BU_PARTNER.

try to split your selection into

KNA1 & KNVV

Then UKMBP_CMS & UKMBP_CMS_SGM with for all entries

Re: Inner Join

PeterJonker — Tue, 09 Feb 2016 12:48:33 GMT

KUNNR is not the same as PARTNER, you need to do a translation through table BD001 or table CVI_CUST_LINK. In these tables there is a link between KUNNR and PARTNER

Re: Inner Join

Former Member — Tue, 09 Feb 2016 12:56:05 GMT

Hi Visakh,

Please check the ukmbp_cms table this table not contain the "new_cust" field.

Regards.

Özgün