topic Re: REGARDING SELECT QUERY in Application Development and Automation Discussions

REGARDING SELECT QUERY

former_member192432 — Fri, 04 Jul 2008 09:25:27 GMT

Hi experts..

I am getting a error message as 'there is a comma after proceeding colon after select? ' in below code..

&----

*& Report ZALV_FLIGHT

&----

REPORT ZALV_FLIGHT.

TABLES : SCARR , SPFLI.

TYPE-POOLS : SLIS.

**********DATA DECLARATION******************

TYPES : BEGIN OF TS_ITAB ,

CARRID LIKE SCARR-CARRID,

CARRNAME LIKE SCARR-CARRNAME,

CONNID LIKE SPFLI-CONNID,

CITYFROM LIKE SPFLI-CITYFROM,

CITYTO LIKE SPFLI-CITYTO,

DEPTIME LIKE SPFLI-DEPTIME,

ARRTIME LIKE SPFLI-ARRTIME,

END OF TS_ITAB.

DATA : IT_ITAB TYPE STANDARD TABLE OF TS_ITAB WITH HEADER LINE .

DATA : WA_ITAB TYPE TS_ITAB.

*DATA : G_REPID TYPE SY-REPID.

DATA : GT_OUTTAB TYPE TS_ITAB OCCURS 0 WITH HEADER LINE .

DATA : GW_OUTAB TYPE TS_ITAB.

*REFRESH IT_ITAB.

*CLEAR WA_ITAB.

DATA : W_FIELDCAT TYPE SLIS_FIELDCAT_ALV.

DATA : T_FIELDCAT TYPE SLIS_T_FIELDCAT_ALV.

DATA : POS TYPE I .

POS = 1.

*******************INITIALIZATION*************************

INITIALIZATION.

*G_REPID = SY-REPID.

*PERFORM FIELDCAT_INIT USING T_FIELDCAT .

CLEAR W_FIELDCAT.

W_FIELDCAT-COL_POS = POS.

W_FIELDCAT-FIELDNAME = 'SCARR-CARRID'.

W_FIELDCAT-REF_FIELDNAME = 'CARRID'.

W_FIELDCAT-REF_TABNAME = 'SCARR'.

W_FIELDCAT-KEY = 'X'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

POS = POS + 1.

W_FIELDCAT-COL_POS = POS.

W_FIELDCAT-FIELDNAME = 'SCARR-CARRNAME'.

W_FIELDCAT-REF_FIELDNAME = 'CARRNAME'.

W_FIELDCAT-REF_TABNAME = 'SCARR'.

W_FIELDCAT-KEY = 'X'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

POS = POS + 1.

W_FIELDCAT-COL_POS = POS.

W_FIELDCAT-FIELDNAME = 'SPFLI-CONNID'.

W_FIELDCAT-REF_FIELDNAME = 'CONNID'.

W_FIELDCAT-REF_TABNAME = 'SPFLI'.

W_FIELDCAT-KEY = 'X'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

POS = POS + 1.

W_FIELDCAT-COL_POS = POS.

W_FIELDCAT-FIELDNAME = 'SPFLI-CITYFROM'.

W_FIELDCAT-REF_FIELDNAME = 'CITYFROM'.

W_FIELDCAT-REF_TABNAME = 'SPFLI'.

W_FIELDCAT-KEY = 'X'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

POS = POS + 1.

W_FIELDCAT-COL_POS = POS.

W_FIELDCAT-FIELDNAME = 'SPFLI-CITYTO'.

W_FIELDCAT-REF_FIELDNAME = 'CITYTO'.

W_FIELDCAT-REF_TABNAME = 'SPFLI'.

W_FIELDCAT-KEY = 'X'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

POS = POS + 1.

W_FIELDCAT-COL_POS = POS.

W_FIELDCAT-FIELDNAME = 'SPFLI-DEPTIME'.

W_FIELDCAT-REF_FIELDNAME = 'DEPTIME'.

W_FIELDCAT-REF_TABNAME = 'SPFLI'.

W_FIELDCAT-KEY = 'X'.

APPEND W_FIELDCAT TO T_FIELDCAT.

CLEAR W_FIELDCAT.

POS = POS + 1.

W_FIELDCAT-COL_POS = POS.

W_FIELDCAT-FIELDNAME = 'SPFLI-ARRTIME'.

W_FIELDCAT-REF_FIELDNAME = 'ARRTIME'.

W_FIELDCAT-REF_TABNAME = 'SPFLI'.

W_FIELDCAT-KEY = 'X'.

APPEND W_FIELDCAT TO T_FIELDCAT.

********************DATA SELECTION ************

START-OF-SELECTION.

SELECT SCARR~CARRID

SCARR~CARRNAME

SPFLI~CONNID

SPFLI~CITYFROM

SPFLI~CITYTO

SPFLI~DEPTIME

SPFLI~ARRTIME

INTO

( GW_OUTAB-SCARR-CARRID,

GW_OUTAB-SCARR-CARRNAME,

GW_OUTAB-SPFLI-CONNID,

GW_OUTAB-SPFLI-CITYFROM,

GW_OUTAB-SPFLI-CITYTO,

GW_OUTAB-SPFLI-DEPTIME,

GW_OUTAB-SPFLI-ARRTIME )

FROM SCARR INNER JOIN SPFLI ON SCARR_{CARRID = SPFLI}CARRID WHERE

SCARR~CARRID BETWEEN 'AA' AND 'HH'.

APPEND GW_OUTAB TO GT_OUTTAB.

ENDSELECT.

*******END-OF-SELECTION*******************

END-OF-SELECTION.

CALL FUNCTION 'REUSE_ALV_LIST_DISPLAY'

EXPORTING

I_INTERFACE_CHECK = ' '
I_BYPASSING_BUFFER =
I_BUFFER_ACTIVE = ' '

I_CALLBACK_PROGRAM = 'SY-REPID'

I_CALLBACK_PF_STATUS_SET = ' '
I_CALLBACK_USER_COMMAND = ' '
I_STRUCTURE_NAME =
IS_LAYOUT =

IT_FIELDCAT = T_FIELDCAT

IT_EXCLUDING =
IT_SPECIAL_GROUPS =
IT_SORT =
IT_FILTER =
IS_SEL_HIDE =
I_DEFAULT = 'X'
I_SAVE = ' '
IS_VARIANT =
IT_EVENTS =
IT_EVENT_EXIT =
IS_PRINT =
IS_REPREP_ID =
I_SCREEN_START_COLUMN = 0
I_SCREEN_START_LINE = 0
I_SCREEN_END_COLUMN = 0
I_SCREEN_END_LINE = 0
IR_SALV_LIST_ADAPTER =
IT_EXCEPT_QINFO =
I_SUPPRESS_EMPTY_DATA = ABAP_FALSE
IMPORTING
E_EXIT_CAUSED_BY_CALLER =
ES_EXIT_CAUSED_BY_USER =

TABLES

T_OUTTAB = GT_OUTTAB

EXCEPTIONS
PROGRAM_ERROR = 1
OTHERS = 2

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Re: REGARDING SELECT QUERY

Former Member — Fri, 04 Jul 2008 09:33:41 GMT

Hi,

See this sample code for using inner joins.

SELECT k_{vbeln k}fkart k_{fkdat k}kunag k_{knumv k}spart k_{inco1 k}ktgrd p_{prctr p}werks

p_{posnr p}posnr p_{fkimg p}spart p_{matnr p}arktx p_{lgort p}aubel p~ktgrm

p_{matkl p}kzwi1 p_{kzwi2 p}kzwi3 p_{kzwi4 p}kzwi5 p~kzwi6

p_{cmpre p}mwsbp p_{netwr p}ktgrm p_{pstyv s}brand_id q_{ktokd q}kunnr

FROM vbrk AS k INNER JOIN vbrp AS p ON k_{vbeln = p}vbeln

INNER JOIN mara AS s ON p_{matnr = s}matnr

INNER JOIN kna1 AS q ON k_{kunag = q}kunnr

INTO CORRESPONDING FIELDS OF TABLE ist_vbrp

WHERE k~vbeln IN s_vbeln AND

k~vkorg = p_salorg AND

k~vtweg IN s_distc AND

k~bukrs = p_bukrs AND

k~spart IN s_div AND

k~fkdat IN s_date AND

p~matnr IN s_mat_no AND

p~werks IN p_plant AND

p~shkzg IN l_shkzg AND

s~brand_id IN s_brand AND

p~vkgrp IN s_vkgrp AND

q~kunnr IN s_kunnr AND

q~ktokd IN s_ktokd AND

k~fkart IN ('ZIV','ZTP','ZCI','S1') AND

p~prctr IN s_prctr.

hope this helps you.

Reward points if helpfull.

Thanks & Regards.

Y.R.Prem Kumar

Re: REGARDING SELECT QUERY

sachin_mathapati — Fri, 04 Jul 2008 09:35:21 GMT

Hi Chetan ,

Change your select query as per below code..

SELECT scarr~carrid

scarr~carrname

spfli~connid

spfli~cityfrom

spfli~cityto

spfli~deptime

spfli~arrtime

*( GW_OUTAB-CARRID,

*GW_OUTAB-CARRNAME,

*GW_OUTAB-CONNID,

*GW_OUTAB-CITYFROM,

*GW_OUTAB-CITYTO,

*GW_OUTAB-DEPTIME,

*GW_OUTAB-ARRTIME )

INTO TABLE gt_outtab

FROM scarr INNER JOIN spfli ON scarr_{carrid = spfli}carrid WHERE

scarr~carrid BETWEEN 'AA' AND 'HH'.

Instead of select .. endselect use into table gt_outtab.

Reward if Helpful.

Regards,

Sachin M m

Re: REGARDING SELECT QUERY

venkatasriram_mygapula — Fri, 04 Jul 2008 09:35:23 GMT

Hi,

INTO

( GW_OUTAB-SCARR-CARRID,

GW_OUTAB-SCARR-CARRNAME,

GW_OUTAB-SPFLI-CONNID,

GW_OUTAB-SPFLI-CITYFROM,

GW_OUTAB-SPFLI-CITYTO,

GW_OUTAB-SPFLI-DEPTIME,

GW_OUTAB-SPFLI-ARRTIME )

FROM SCARR INNER JOIN SPFLI ON SCARR_{CARRID = SPFLI}CARRID WHERE

SCARR~CARRID BETWEEN 'AA' AND 'HH'.

here ur not using work area, directly fetcing into corresponding fields.

but here dont put ' , ' for every field in above code give the space only.

Re: REGARDING SELECT QUERY

Former Member — Fri, 04 Jul 2008 09:43:14 GMT

Hi Chetan,

Instead of using SELECT-ENDSELECT go for SELECT and move the selected fields into the internal table.

Here in your situation, you have the table GT_OUTTAB with same fields into which you are appending the data. So, its better to use the whole intenal table GT_OUTTAB.

Try the following.

Select

from <dbtable>

into table GT_OUTTAB

where <condition>.

And infact make sure that you never use

SELECT-ENDSELECT statement as there are performance issues.

Hope this helps you.

Regards,

Chandra Sekhar

Re: REGARDING SELECT QUERY

Former Member — Fri, 04 Jul 2008 09:45:01 GMT

Use the below select query.

SELECT

SCARR~CARRID

SCARR~CARRNAME

SPFLI~CONNID

SPFLI~CITYFROM

SPFLI~CITYTO

SPFLI~DEPTIME

SPFLI~ARRTIME

INTO

GW_OUTAB

FROM SCARR as SCARR innER JOIN SPFLI as SPFLI ON SCARR_{CARRID = SPFLI}CARRID

where SCARR~CARRID BETWEEN 'AA' AND 'HH'.

Re: REGARDING SELECT QUERY

Former Member — Fri, 04 Jul 2008 10:00:26 GMT

You have to specify

INTO (GW_OUTAB-CARRID, 
      GW_OUTAB-CARRNAME,
      GW_OUTAB-CONNID,
      GW_OUTAB-CITYFROM,
      GW_OUTAB-CITYTO,
      GW_OUTAB-DEPTIME,
      GW_OUTAB-ARRTIME)

Specify GW_OUTAB-CARRID instead of GW_OUTAB-SCARR-CARRID.

i.e. GW_OUTTAB-<Fieldname> rather than GW_OUTTAB-<Tablename>-<fieldname> as you are referring to internal table

The better approach will be directly use INTO GW_OUTTAB as the sequence of the fields in the workarea is same as that of sequence in which you are fetching the fields.

Reward if useful.

Re: REGARDING SELECT QUERY

former_member192432 — Thu, 10 Dec 2009 12:28:04 GMT

thanks for reply