topic Re: INNER JOIN in Application Development and Automation Discussions

INNER JOIN

Former Member — Thu, 14 Sep 2006 08:37:44 GMT

Hi!! I'm tryhig to do a query on three database views and read it on an internal table, the sintax is ok (at least there's no error messsage when I verify it)but it's not working propperly because when I run the debugging mode the program doesn't execute the select.

My internal table is declared like this:

TYPES:BEGIN OF st_output,

tplnr LIKE viaufkst-tplnr,

pltxt LIKE iflo-pltxt,

acpos LIKE pmco-acpos,

objnr LIKE viaufkst-objnr,

END OF st_output.

DATA gt_output TYPE STANDARD TABLE OF st_output WITH HEADER LINE.

The database views are viaufkst, iflo and pmco and they are declared with the TABLES statement.

The query's sintax is:

SELECT V_{TPLNR V}OBJNR I_{PLTXT P}ACPOS

FROM ( ( VIAUFKST AS V

INNER JOIN IFLO AS I ON V_{TPLNR = I}TPLNR

)

INNER JOIN PMCO AS P ON V_{OBJNR = P}OBJNR )

INTO TABLE gt_output

WHERE V~TPLNR = '0001-GRUP-0001'.

I do know that the where condition is true, so:

Anybody can tell me why is it not working?

Re: INNER JOIN

anversha_s — Thu, 14 Sep 2006 08:44:28 GMT

hi juna,

this will work for u.

chk the new code.

data:BEGIN OF gt_output occurs 0,

tplnr LIKE viaufkst-tplnr,

pltxt LIKE iflo-pltxt,

acpos LIKE pmco-acpos,

objnr LIKE viaufkst-objnr,

END OF gt_output.

SELECT V_{TPLNR V}OBJNR I_{PLTXT P}ACPOS

INTO TABLE gt_output

FROM VIAUFKST AS V

INNER JOIN IFLO AS I

ON V_{TPLNR = I}TPLNR

INNER JOIN PMCO AS P

ON V_{OBJNR = P}OBJNR

WHERE V~TPLNR = '0001-GRUP-0001'.

rgds

anver

if hlped mark points

Re: INNER JOIN

Former Member — Thu, 14 Sep 2006 08:44:57 GMT

Hi,

Try this,

SELECT V_{TPLNR V}OBJNR I_{PLTXT P}ACPOS

INTO TABLE gt_output

FROM ( VIAUFKST AS V

INNER JOIN IFLO AS I ON V_{TPLNR = I}TPLNR )

INNER JOIN PMCO AS P ON V_{OBJNR = P}OBJNR

WHERE V~TPLNR = '0001-GRUP-0001'.

If you are not sure abt the where condition check with only the bold part and check if it is fetching any value in the debugger.

Reward if it helps.

Regards,

Senthil

Message was edited by: senthil kumar

Re: INNER JOIN

Former Member — Thu, 14 Sep 2006 08:45:50 GMT

just check this ..may be because of missmatch ..in

TPLNR = '0001-GRUP-0001'.

TYPES:BEGIN OF st_output,
tplnr LIKE viaufkst-tplnr,
pltxt LIKE iflo-pltxt,
acpos LIKE pmco-acpos,
objnr LIKE viaufkst-objnr,
END OF st_output.

DATA gt_output TYPE STANDARD TABLE OF st_output WITH HEADER LINE.
The database views are viaufkst, iflo and pmco and they are declared with the TABLES statement.
The query's sintax is:
SELECT V~TPLNR V~OBJNR I~PLTXT P~ACPOS

FROM ( ( VIAUFKST AS V
INNER JOIN IFLO AS I ON V~TPLNR = I~TPLNR
)
INNER JOIN PMCO AS P ON V~OBJNR = P~OBJNR )
INTO TABLE gt_output

Re: INNER JOIN

Former Member — Thu, 14 Sep 2006 08:46:21 GMT

There is no need for the brackets but I think it must be executing the statement but since there is no data to be fetched you no able to view the result...

To check that please check on the SY-SUBRC after the select statement.

Regards

Anurag

Re: INNER JOIN

Former Member — Thu, 14 Sep 2006 08:49:42 GMT

Hi Juan

I guess Table VIAUFKST is not having records for field TPLNR Value '0001-GRUP-0001'. Please check.

Regards

Srikanth M

Re: INNER JOIN

Former Member — Thu, 14 Sep 2006 10:42:01 GMT

Ok guys!! We are appraching the solution, I think the problem is on the where clause. I've disabled the where clause an I've made a loop at the table gt_output, to write its content on the screen. Everything is ok and the program shows on the screen all the rows of gt_output, the problem now is that in this list there're 25 rows with the value 0001-GRUP-0001 on the field tplnr. What the hell is going on????

This is thenew sintax:

SELECT V_{TPLNR V}OBJNR I_{PLTXT P}ACPOS

INTO TABLE gt_output

FROM VIAUFKST AS V

INNER JOIN IFLO AS

I ON V_{TPLNR = I}TPLNR

INNER JOIN PMCO AS P

ON V_{OBJNR = P}OBJNR.

WHERE I~TPLNR = '0001-GRUP-0001'.

LOOP AT gt_output into wa_output.

write:/ wa_output-tplnr,WA_OUTPUT-OBJNR,wa_output-acpos,wa_output-pltxt.

ENDLOOP.

Re: INNER JOIN

Former Member — Thu, 14 Sep 2006 10:48:48 GMT

the reason is ..

TPLNR is char type and ur value ('0001-GRUP-0001'.

)stsrted by 000 so ir Surplus the zeros and it take it like -GRUP-0001' and it not fount it in table.

Re: INNER JOIN

Former Member — Thu, 14 Sep 2006 10:51:35 GMT

hi juan ollero ,

The problem with ur select query is that u have not used the 'into corresponding fields of' statement. The following Select query will work.

SELECT V_{TPLNR V}OBJNR I_{PLTXT P}ACPOS

INTO CORRESPONDING FIELDS OF TABLE gt_output

FROM VIAUFKST AS V INNER JOIN IFLO AS I ON V_{TPLNR = I}TPLNR INNER JOIN PMCO AS P ON V_{OBJNR = P}OBJNR

WHERE V~TPLNR = '0001-GRUP-0001'.

Re: INNER JOIN

Former Member — Thu, 14 Sep 2006 11:19:50 GMT

Hi Juan,

The entire code is correct may be check out the where condition.

Thanks

Vikranth Khimavath

Re: INNER JOIN

Former Member — Thu, 14 Sep 2006 11:29:23 GMT

Hi,

SELECT V_{TPLNR V}OBJNR I_{PLTXT P}ACPOS

FROM ( ( VIAUFKST AS V

INNER JOIN IFLO AS I ON V_{TPLNR = I}TPLNR

)

INNER JOIN PMCO AS P ON V_{OBJNR = P}OBJNR )

INTO TABLE gt_output

WHERE V~TPLNR like '%0001%'.Regards

amole