topic Re: Select query in Application Development and Automation Discussions

Select query

Former Member — Tue, 20 Feb 2007 13:50:12 GMT

Hi all,

Please Send me the Code for this Query.

Check for BEDSD = X in table TVEP

Note all such ETTYP

With this ETTYP check for entries in VBEP

If entries exist in VBEP, Note all such ETTYP

With this ETTYP check in TVEPZ, and note PSTYV

With this PSTYV check in T184 and note AUART

From Table TVAKZ.

Thanks in Advance

Mamatha.

Re: Select query

Former Member — Tue, 20 Feb 2007 14:16:52 GMT

Hi,

SELECT ettyp FROM TVEP
                      INTO TABLE i_tvep
                      WHERE bedsd = 'X'.
if sy-subrc = 0.
 SELECT ettyp FROM vbep
                      INTO TABLE i_vbep
                      FOR ALL ENTRIES IN i_tvep
                      WHERE ettyp = i_tvep-ettyp.
if sy-subrc = 0.
  SELECT pstyv FROM tvpez
                      INTO TABLE i_tvpez
                      FOR ALL ENTRIES IN i_vbep
                      WHERE ettyp = i_vbep-ettyp.
if sy-subrc = 0.
  SELECT *  FROM t184
                      INTO TABLE i_t184
                      FOR ALL ENTRIES IN i_tvpez
                      WHERE pstyv = i_tvpez-pstyv.
if sy-subrc = 0.

  SELECT auart  FROM tvakz
                      INTO TABLE i_tvakz
                      FOR ALL ENTRIES IN i_t184
                      WHERE pstyv = i_t184-pstyv.

if sy-subrc  = 0.
.................
endif.
endif.
endif.
endif.
endif.

Instead u can also use inner join.


SELECT c~pstyv  FROM TVEP
                AS a inner join 
                vbep as b
                ON a~ettyp = b~ettyp
                INNER JOIN tvep as c
                ON b~ettyp = c~ettyp
                INTO TABLE i_pstyv
                WHERE a~bedsd = 'X'.
if sy-subrc = 0.
  SELECT b~auart  FROM t184
                  AS a inner join
                  tvakz as b
                  ON a~pstyv = b~pstyv
                  FOR ALL ENTRIES IN i_pstyv
                  INTO TABLE i_auart
                  WHERE a~pstyv = i_pstyv-pstyv.
if sy-subrc = 0.
.................
endif.
endif.

Message was edited by:

Judith Jessie Selvi

Re: Select query

Former Member — Tue, 20 Feb 2007 14:19:44 GMT

Hi Judith Jessie Selvi ,

Thanks very much. Can u send the code with Inner Join?

Thanks in Advance

Mamatha

Re: Select query

Former Member — Tue, 20 Feb 2007 14:28:16 GMT

SELECT c~pstyv  FROM TVEP
                AS a inner join 
                vbep as b
                ON a~ettyp = b~ettyp
                INNER JOIN tvep as c
                ON b~ettyp = c~ettyp
                INTO TABLE i_pstyv
                WHERE a~bedsd = 'X'.
if sy-subrc = 0.
  SELECT b~auart  FROM t184
                  AS a inner join
                  tvakz as b
                  ON a~pstyv = b~pstyv
                  FOR ALL ENTRIES IN i_pstyv
                  INTO TABLE i_auart
                  WHERE a~pstyv = i_pstyv-pstyv.
if sy-subrc = 0.
.................
endif.
endif.

Reward if u fine helpful.

Re: Select query

Former Member — Tue, 20 Feb 2007 14:29:07 GMT

TYPES:

BEGIN OF zts_sched_category,

ettyp TYPE ettyd,

END OF zts_sched_category,

BEGIN OF zts_item_category,

pstyv TYPE pstyv,

END OF zts_item_category,

BEGIN OF zts_ordertype,

auart TYPE auart,

END OF zts_ordertype.

DATA:

zls_sched_category TYPE zts_sched_category,

zlt_sched_categories TYPE STANDARD TABLE OF zts_sched_category,

zlt_item_categories TYPE STANDARD TABLE OF zts_item_category,

zlt_ordertypes TYPE STANDARD TABLE OF zts_ordertype.

SELECT ettyp

FROM tvep

INTO TABLE zlt_sched_categories

WHERE bedsd = 'X'.

SORT zlt_sched_categories.

DELETE ADJACENT DUPLICATES FROM zlt_sched_categories.

LOOP AT zlt_sched_categories INTO zls_sched_category.

SELECT COUNT( * )

FROM vbep

WHERE ettyp = zls_sched_category-ettyp.

IF syst-dbcnt = 0.

DELETE zlt_sched_categories.

ENDIF.

ENDLOOP.

CHECK: zlt_sched_categories IS NOT INITIAL.

SELECT pstyv

FROM tvepz

INTO TABLE zlt_item_categories

FOR ALL ENTRIES IN zlt_sched_categories

WHERE ettyp = zlt_sched_categories-ettyp.

SORT zlt_item_categories.

DELETE ADJACENT DUPLICATES FROM zlt_item_categories.

CHECK: zlt_item_categories IS NOT INITIAL.

SELECT auart

FROM t184

INTO TABLE zlt_ordertypes

FOR ALL ENTRIES IN zlt_item_categories

WHERE pstyv = zlt_item_categories-pstyv.

SORT zlt_ordertypes.

DELETE ADJACENT DUPLICATES FROM zlt_ordertypes.

Re: Select query

Former Member — Tue, 20 Feb 2007 15:54:40 GMT

Hi Judith Jessie Selvi ,

TVEPZ is a Pooled table. weshould not use Joins for Pooled Tables.

So, could you please decleare the all internal tables for the following code.

SELECT ettyp FROM TVEP

INTO TABLE i_tvep

WHERE bedsd = 'X'.

if sy-subrc = 0.

SELECT ettyp FROM vbep

INTO TABLE i_vbep

FOR ALL ENTRIES IN i_tvep

WHERE ettyp = i_tvep-ettyp.

if sy-subrc = 0.

SELECT pstyv FROM tvpez

INTO TABLE i_tvpez

FOR ALL ENTRIES IN i_vbep

WHERE ettyp = i_vbep-ettyp.

if sy-subrc = 0.

SELECT * FROM t184

INTO TABLE i_t184

FOR ALL ENTRIES IN i_tvpez

WHERE pstyv = i_tvpez-pstyv.

if sy-subrc = 0.

SELECT auart FROM tvakz

INTO TABLE i_tvakz

FOR ALL ENTRIES IN i_t184

WHERE pstyv = i_t184-pstyv.

and output will be:

VKORG/ AUART/ “Desc. Of AUART”/PSTYV/

“Desc of PSTYV”/ ETTYP/ “Desc of ETTYP”/

“relevant for Transfer of Requirements”

Thanks in Advance

Mamatha