topic Re: SQL error in Application Development and Automation Discussions

SQL error

Former Member — Thu, 28 Sep 2006 17:39:13 GMT

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

Re: SQL error

Former Member — Thu, 28 Sep 2006 17:42:25 GMT

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'.

Re: SQL error

Former Member — Thu, 28 Sep 2006 17:48:23 GMT

Hi srinu k

Thanks for ur reply but still doesnt work!

Br.

Re: SQL error

Former Member — Thu, 28 Sep 2006 17:53:29 GMT

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'.

Re: SQL error

Former Member — Thu, 28 Sep 2006 18:08:19 GMT

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

Re: SQL error

Former Member — Thu, 28 Sep 2006 18:22:11 GMT

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

Re: SQL error

Former Member — Thu, 28 Sep 2006 18:28:26 GMT

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.

Anna

Re: SQL error

Former Member — Thu, 28 Sep 2006 18:32:01 GMT

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.

Re: SQL error

Former Member — Thu, 28 Sep 2006 19:12:27 GMT

Many thanks Michale and Srinu !

Br.

Anna