topic Re: Why duplicates in my_SELECT statement-Small issue in SQL in Application Development and Automation Discussions

Why duplicates in my_SELECT statement-Small issue in SQL

Former Member — Tue, 26 Feb 2008 16:42:01 GMT

Hi Experts,

I hv follwoing SQL, but, in some cases, am getting duplicates......any clue? is it bcoz, am using BSIS first and then BKPF(Header) as second?

SELECT s~hkont

s~belnr

s~bschl

s~shkzg

s~dmbtr

s~kostl

s~prctr

k~bukrs

k~gjahr

k~monat

k~belnr

m~saknr

m~xbilk

m~gvtyp

INTO TABLE t_main

FROM bsis AS s

INNER JOIN bkpf AS k ON k_{bukrs = s}bukrs AND

k_{belnr = s}belnr AND

k_{gjahr = s}gjahr

INNER JOIN ska1 AS m ON m_{saknr = s}hkont

WHERE s~hkont IN s_hkont AND

s~kostl IN s_kostl AND

s~prctr IN s_prctr AND

k~bukrs IN s_bukrs AND

k~gjahr IN s_gjahr AND

k~monat IN s_monat.

Re: Why duplicates in my_SELECT statement-Small issue in SQL

Former Member — Tue, 26 Feb 2008 16:43:49 GMT


SELECT DISTINCT s~hkont
s~belnr
s~bschl
s~shkzg
s~dmbtr
s~kostl
s~prctr
k~bukrs
k~gjahr
k~monat
k~belnr
m~saknr
m~xbilk
m~gvtyp
INTO TABLE t_main
FROM bsis AS s
INNER JOIN bkpf AS k ON k~bukrs = s~bukrs AND
k~belnr = s~belnr AND
k~gjahr = s~gjahr
INNER JOIN ska1 AS m ON m~saknr = s~hkont
WHERE s~hkont IN s_hkont AND
s~kostl IN s_kostl AND
s~prctr IN s_prctr AND
k~bukrs IN s_bukrs AND
k~gjahr IN s_gjahr AND
k~monat IN s_monat.

Add DISTINCT before the SELECT statement...

Greetings,

Blag.

Re: Why duplicates in my_SELECT statement-Small issue in SQL

JozsefSzikszai — Tue, 26 Feb 2008 16:45:49 GMT

hi Srinivas,

you included SKA1 into your join as well, but ska1 contains G/L accounts for all chart of accounts, not just for the one, which is linked to the company code, the actual record is selected. You cannot join SKA1 and BKPF dierectly, because:

SKA1-KTOPL = T001-KTOPL

T001-BUKRS = BKPF-BUKRS

You either include table T001 into your JOIN, or remove SKA1, select the data from SKA1 in a separate step and merge the two internal table later.

hope this helps

Re: Why duplicates in my_SELECT statement-Small issue in SQL

Former Member — Tue, 26 Feb 2008 17:01:33 GMT

I suggest you to use separate internal tables with FOR ALL ENTRIES instead of cluttered INNER JOINS. That way, you would have much control on what you are expecting and what you are doing.

Thanks,

SKJ

Re: Why duplicates in my_SELECT statement-Small issue in SQL

Former Member — Tue, 26 Feb 2008 18:47:08 GMT

thanq.

After implememnting the Alvaro Tejada suggestion i.e. Using the DISTINCT clause, I moved the prog. into TEST/QA systems.

So, pls. let me know that,

1 - Is that suggestion(DISTINCT) wuld NOT work in all cases, like, some times may yield incorrect data?

2 - If so, will implement ur suggestion, i.e. By using T001 table, will JOIN the SKA1 table.......but, here making one more transport is tedious with a bit paper work.......so, if u say, its must, will create a new transport!

Its friednly asking, not criticing

thanq