SQL error

[Former Member]

Hi all

Im trying the get the last order request of an object (saving in the variable zorder) using also the client specified (mandt), but doesnt work. My Select max is also not working !

Any idea how can I fix this?

SELECT single e071~trkorr INTO zorder

FROM e071 CLIENT SPECIFIED

INNER JOIN e070

ON e071_{trkorr = e070}trkorr

WHERE e071~PGMID = 'R3TR' AND

e071~OBJECT = 'PROG' AND

e071~OBJ_NAME = zt-object AND

e070_{as4date = =(SELECT Max(e070}as4date) FROM e070 where e071~PGMID = 'R3TR' AND

e071~OBJECT = 'PROG' AND

e071~OBJ_NAME = zt-object ) AND

mandt = '200'.

Br.

Anna

[Former Member]

hi

try this

SELECT single <b>trkorr</b> INTO zorder

FROM e071 CLIENT SPECIFIED

INNER JOIN e070

ON e071_{trkorr = e070}trkorr

WHERE e071~PGMID = 'R3TR' AND

e071~OBJECT = 'PROG' AND

e071~OBJ_NAME = zt-object AND

e070_{as4date = =(SELECT Max(e070}as4date) FROM e070 where e071~PGMID = 'R3TR' AND

e071~OBJECT = 'PROG' AND

e071~OBJ_NAME = zt-object ) AND

mandt = '200'.

[Former Member]

Hi srinu k

Thanks for ur reply but still doesnt work!

Br.

[Former Member]

hi,

check this now...

SELECT single e071~trkorr INTO zorder

FROM e071

INNER JOIN e070

ON e071_{trkorr = e070}trkorr <b>CLIENT SPECIFIED</b>

WHERE e071~PGMID = 'R3TR' AND

e071~OBJECT = 'PROG' AND

e071~OBJ_NAME = zt-object AND

e070_{as4date = =(SELECT Max(e070}as4date) FROM e070 where e071~PGMID = 'R3TR' AND

e071~OBJECT = 'PROG' AND

e071~OBJ_NAME = zt-object ) AND

mandt = '200'.

[Former Member]

Hi Srinu

The client specified problem is solved. But still get error in the select max.

One more thing, the mandt clause also doesnt work (I had do delete), maybe it is because there is no mandt field in one of the tables ? In this case how I get this statement for a specific mandt ?

Many many thanks.

Anna

[Former Member]

Since the tables are client-independent, the client specified addition isn't meaningful.

If you look at the SELECT MAX statement and consider it outside of the full selection, you are referencing both tables though no join exists.

Message was edited by: Michael Malvey

[Former Member]

Hi Michael

Many thanks for ur reply, I understood your point.

In this case how can I get the last trasport request/order of an object using a select statement?

I tried this but also doenst work:

SELECT single e071~trkorr INTO zorden

FROM e071

INNER JOIN e070

ON e071_{trkorr = e070}trkorr CLIENT SPECIFIED

WHERE e071~PGMID = 'R3TR' AND

e071~OBJECT = 'PROG' AND

e071~OBJ_NAME = 'XXXX'

ORDER BY e070-AS4DATE DESCENDING.

Thanks.

Br

Anna

[Former Member]

Try this:

select e071~trkorr as4date into zorder
   from e071 inner join e070
          on e071~trkorr = e070~trkorr
  where e071~pgmid = 'R3TR'
    and e071~object = 'PROG'
    and e071~obj_name = p_prog
  order by as4date descending.
  exit.
endselect.

[Former Member]

Many thanks Michale and Srinu !

Br.

Anna