topic Re: SELECT statement... in Application Development and Automation Discussions

SELECT statement...

Former Member — Tue, 20 Jan 2009 08:50:06 GMT

Hi all,

I am trying to compile the below code, but I get the compile error message.

Anyone know why after I added the field E070~TRSTATUS, compile is not successful.

I need the field TRSTATUS selected into ITAB.

How should I solve this select statement error?

Thanks for the advice.


.
.
.
DATA BEGIN OF DATASTRUC.
   DATA   OBJECT_NAME  LIKE TADIR-OBJ_NAME.
   DATA   OBJECT_TYPE   LIKE TADIR-OBJECT.
   DATA   PROGRAM_ID    LIKE TADIR-PGMID.
   DATA   PACKAGE         LIKE TADIR-DEVCLASS.
   DATA   TRSTATUS         LIKE E070-TRSTATUS.
DATA END OF DATASTRUC.

DATA ITAB LIKE SORTED TABLE OF DATASTRUC
          WITH NON-UNIQUE KEY OBJECT_NAME.

SELECT TADIR~OBJ_NAME TADIR~OBJECT TADIR~PGMID TADIR~DEVCLASS E070~TRSTATUS
  INTO TABLE ITAB
  FROM TADIR
  WHERE TADIR~AUTHOR = USER
    AND TADIR~OBJ_NAME NOT IN
    ( SELECT E071~OBJ_NAME E070~TRSTATUS FROM E071
      JOIN E070 ON E071~TRKORR = E070~TRKORR
         WHERE E070~AS4USER = USER ).
.
.
.

Use meaningful subject line

Edited by: Vijay Babu Dudla on Jan 22, 2009 6:02 AM

Re: SELECT statement...

Former Member — Tue, 20 Jan 2009 08:54:30 GMT

Hi, the table TADIR does not have field E070~TRSTATUS in line

SELECT TADIR_{OBJ_NAME TADIR}OBJECT TADIR_{PGMID TADIR}DEVCLASS E070~TRSTATUS

INTO TABLE ITAB

FROM TADIR

. Please check your select statement

Re: SELECT statement...

Former Member — Tue, 20 Jan 2009 08:55:49 GMT

kindly check the syntax for ur select query .

I think u gone wrong in the syntax of ur select statement u r using two different table fields ..but u mentioned only one table name in the query.Try resolving the pbm using Join condition,

thank you

regards.

rajye

pls close the thread if its resolved

Edited by: Rajeshwari P on Jan 20, 2009 9:56 AM

Edited by: Rajeshwari P on Jan 20, 2009 11:23 AM

Re: SELECT statement...

Former Member — Tue, 20 Jan 2009 08:57:07 GMT

Hi Wong,

you do not have TRSTATUS field in TADIR, yet you are using that column name in your from clause.

SELECT TADIR_{OBJ_NAME TADIR}OBJECT TADIR_{PGMID TADIR}DEVCLASS e070~TRSTATUS

INTO TABLE ITAB

FROM TADIR.

This statement is trying to fetch the column TRSTATUS with alias name E070 from table TADIR.

You have to rephrase the query.

If you need to fetch based on the change request try the below code.

SELECT tadir_{obj_name tadir}object tadir_{pgmid tadir}devclass e070~trstatus

INTO TABLE itab

FROM tadir JOIN e070

ON tadir_{korrnum = e070}trkorr "change request based join

AND tadir~author = user

AND tadir~obj_name NOT IN

( SELECT e071_{obj_name e070}trstatus FROM e071

JOIN e070 ON e071_{trkorr = e070}trkorr

WHERE e070~as4user = user ).

Edited by: prosenjit chaudhuri on Jan 20, 2009 10:08 AM

Re: SELECT statement...

Former Member — Tue, 20 Jan 2009 08:57:50 GMT

When You use two table to select data , you need to specify the join condition.

SELECT tadir~obj_name tadir~object tadir~pgmid tadir~devclass
e070~trstatus
  INTO TABLE itab
  FROM tadir INNER JOIN e070
 ON tadir~author = e070~as4user
  WHERE tadir~author = user
    AND tadir~obj_name NOT IN
    ( SELECT e071~obj_name e070~trstatus FROM e071
      JOIN e070 ON e071~trkorr = e070~trkorr
         WHERE e070~as4user = user ).

Regards,

Shailaja

Re: SELECT statement...

Former Member — Tue, 20 Jan 2009 08:58:35 GMT

The error being is because of multiple fields (OBJ_NAME, TRSTATUS) in the right hand query assigned to single field (TADIR~OBJ_NAME) in the left hand query, which is wrong.

SELECT TADIR_{OBJ_NAME TADIR}OBJECT TADIR_{PGMID TADIR}DEVCLASS E070~TRSTATUS

INTO TABLE ITAB

FROM TADIR

WHERE TADIR~AUTHOR = USER

AND TADIR~OBJ_NAME NOT IN

( SELECT E071_{OBJ_NAME E070}TRSTATUS FROM E071

JOIN E070 ON E071_{TRKORR = E070}TRKORR

WHERE E070~AS4USER = USER ).

Re: SELECT statement...

Former Member — Tue, 20 Jan 2009 09:01:39 GMT

Hi,

In ur main select statement u didnt use join . but u pick value from two tables. Thats y it showing error.

Check E070~TRSTATUS in ur main select statement.

Hope this will help u.

Thanks.