topic Re: Select single with Join?? in Application Development and Automation Discussions

Select single with Join??

Former Member — Tue, 01 Dec 2009 10:36:12 GMT

Hi,

Can we use SELECT SINGLE stataement with INNER JOIN??

Please give me a sample example.

Regards,

Re: Select single with Join??

Former Member — Tue, 01 Dec 2009 10:38:03 GMT

Hello,

Why dont you just try once and see if its working or not before posting?

Vikranth

Re: Select single with Join??

Former Member — Tue, 01 Dec 2009 10:41:10 GMT

Hello,

This is possible.

Of course you have to pay attention with this, because only 1 record from the join will be shown in the result.

Extract from the SAP help:

If SINGLE is specified, the resulting set has a single line. If the remaining additions to the SELECT command select more than one line from the database, the first line that is found is entered into the resulting set.

Wim

Re: Select single with Join??

Former Member — Tue, 01 Dec 2009 10:44:51 GMT

I tied but it gave me syntax error " Incorrect structure of FROM clause"

I tried as follows:

SELECT to_{bukrs t1}periv INTO (ws_bukrs , ws_periv)

FROM TVKO as to

INNER JOIN t001 as t1

WHERE to~vkorg = p_vkorg.

ws_bukrs and ws_periv are globally declared.

Regards,

Re: Select single with Join??

VXLozano — Tue, 01 Dec 2009 10:47:28 GMT

Two things:

1- TO is a reserved word in many languages... try to not use it as an alias

2- I miss the ON clause of your JOIN

The main point is the 2nd one: if you don't say the DBMS which fields must compare to join the tables, it will become angry with you and will not help you to fetch the data

Re: Select single with Join??

SuhaSaha — Tue, 01 Dec 2009 10:52:12 GMT

Obviously you can:

PARAMETERS: p_matnr TYPE matnr.

DATA:
      BEGIN OF st_mat,
        matnr TYPE matnr,
        matkl TYPE matkl,
        maktx TYPE maktx,
      END OF st_mat.

SELECT SINGLE a~matnr a~matkl b~maktx
  INTO st_mat
  FROM mara AS a INNER JOIN makt AS b
  ON b~matnr = a~matnr
  WHERE a~matnr = p_matnr
  AND   spras = sy-langu.

IF sy-subrc = 0.
  WRITE st_mat.
ENDIF.

It does not give a SLIN error as well

BR,

Suhas

Re: Select single with Join??

Former Member — Tue, 01 Dec 2009 10:54:06 GMT

Hello,

Change your select query this way,



parameters: p_vkorg type tvko-vkorg.

data: ws_bukrs type tvko-bukrs,
      ws_periv type t001-periv.

SELECT single to~bukrs t1~periv INTO
(ws_bukrs , ws_periv)
FROM TVKO as to
INNER JOIN t001 as t1
on to~bukrs = t1~bukrs
WHERE to~vkorg = p_vkorg.


write: ws_bukrs, ws_periv.

You dint mention the ON condition and SINGLE addition in your query

Vikranth

Re: Select single with Join??

Former Member — Tue, 01 Dec 2009 10:55:48 GMT

Am not understanding whats the purpose though..If all you need is a SINGLE record , why bother with a join ?

regards,

Deepthi