topic Re: Join statment is not working in Application Development and Automation Discussions

Join statment is not working

Former Member — Wed, 23 Jul 2008 02:12:38 GMT

Hello Experts,

I am trying to write a below code where i will be using JOIN statment to select data from 2 table faglflexas and cepct.These two tables have only one field in common(prctr).I would also be getting the value of prctr from the user on selection screen.

But the code syntax is not allowing me to prctr in both the places.

SELECT faglflexa_{poper faglflexa}ryear faglflexa_{prctr cepct}ktext " FROM faglflexa

INTO CORRESPONDING FIELDS OF TABLE gi_prctr

FROM cepct JOIN faglflexa ON cepct_{prctr = faglflexa}prctr

WHERE poper IN s_period

AND ryear EQ p_fyear.

AND prctr in s_prctr.

I am getting error in last line of AND prctr in s_prctr. asking that I prctr is defined in two places in the select query.

please help.

points will be rewarded definately.

Re: Join statment is not working

Former Member — Wed, 23 Jul 2008 02:17:36 GMT

Hi,

The code written by you is giving a syntax error as PRCTR field is present in both the tables and you are using a join but in the where clause you are not specifying the field with respect to a table.

try the below syntax:

SELECT faglflexa_{poper faglflexa}ryear faglflexa_{prctr cepct}ktext " FROM faglflexa

INTO CORRESPONDING FIELDS OF TABLE gi_prctr

FROM cepct JOIN faglflexa ON cepct_{prctr = faglflexa}prctr

WHERE poper IN s_period

AND ryear EQ p_fyear.

AND cepct~prctr in s_prctr.

Thanks.

Re: Join statment is not working

former_member194669 — Wed, 23 Jul 2008 02:19:26 GMT

Try this way


SELECT faglflexa~poper faglflexa~ryear faglflexa~prctr cepct~ktext " FROM faglflexa
INTO CORRESPONDING FIELDS OF TABLE gi_prctr
FROM cepct INNER JOIN faglflexa ON cepct~prctr = faglflexa~prctr
WHERE faglflexa~poper IN s_period
AND faglflexa~ryear EQ p_fyear.
AND faglflexa~prctr in s_prctr.

Re: Join statment is not working

Former Member — Wed, 23 Jul 2008 02:22:42 GMT

Hi,

there is small mistake in your query, just try this one.

SELECT faglflexa_{poper faglflexa}ryear faglflexa_{prctr cepct}ktext " FROM faglflexa

INTO CORRESPONDING FIELDS OF TABLE gi_prctr

FROM cepct JOIN faglflexa ON cepct_{prctr = faglflexa}prctr

WHERE poper IN s_period

AND ryear EQ p_fyear.

AND faglflexa~prctr in s_prctr.

Re: Join statment is not working

Former Member — Wed, 23 Jul 2008 02:23:38 GMT

Thanks Sananth,

It solved the problem.