topic Re: Sql problem in select clause... in Application Development and Automation Discussions

Sql problem in select clause...

Former Member — Fri, 25 Nov 2005 09:38:00 GMT

Hi,

I need some help with my sql-statement.

I need to substract MENGE from DABMG.

It gives me an error below...

Any pointers ?

Get PO data Schedule

select

X_EKKO~EBELN as EBELN

X_EKKO~LIFNR as LIFNR

X_EKKO~BEDAT as BEDAT

X_EKKO~EKGRP as EKGRP

X_EKPO~WERKS as WERKS

X_EKPO~EBELP as EBELP

X_EKPO~MATNR as MATNR

(X_EKET_{MENGE-X_EKET}DABMG) as MENGE " PROBLEM!!

X_EKET~EINDT as EINDT

from EKKO as X_EKKO

inner join EKPO as X_EKPO on X_EKPO_{EBELN = X_EKKO}EBELN

inner join EKET as X_EKET on X_EKET_{EBELN = X_EKPO}EBELN AND X_EKET_{EBELP = X_EKPO}EBELP

into corresponding fields of table i_zopenpo_schedule

where

(i_where_clause).

//Martin

Re: Sql problem in select clause...

FredericGirod — Fri, 25 Nov 2005 09:43:04 GMT

Hi Martin,

SAP is not a very good tool to play with SQL, maybe you have to be careful when you write a SQL command. The best way to play with ABAP is: get the data from the database and after play with this data.

The cost time to get the data is really important, so stop to add inner join, group by or anything to strong for the SAP kernel. And inner join works well when you have a relation 1:n (with the key).

My answer will be, substract outside from the SELECT.

Rgd

Frédéric

Re: Sql problem in select clause...

Former Member — Fri, 25 Nov 2005 09:43:53 GMT

Hi Martin,

You can not subtract like this in SQL.

Use Functions like SUM, Count in SQL statement.

Regards,

Amey

Re: Sql problem in select clause...

Former Member — Fri, 25 Nov 2005 09:46:36 GMT

Hi,

Its not possible to substract in select statement itslef.

Make it outside.

And one more thing you r substracting DABMG from MENGE.

But you mentioned that MENGE from DABMG in second line.

Check & try again.

(X_EKET_{MENGE-X_EKET}DABMG) as MENGE " PROBLEM!!

Regards

KER

Re: Sql problem in select clause...

abdul_hakim — Fri, 25 Nov 2005 09:47:49 GMT

Hi Martin,

You cannot subtract like this in SQL statement.

Get the values from the DBtable and do your validation in your program(outside the SELECT).I would also advice you to avoid joins in this situation which may again end up with performance issue..

Regards,

Abdul

Re: Sql problem in select clause...

abdul_hakim — Fri, 25 Nov 2005 09:57:35 GMT

Hi Martin,

Also replace your query with the following query.

It will give good performance...

SELECT ebeln

lifnr

bedat

ekgrp

FROM ekko INTO TABLE int_ekko.

SELECT ebeln

werks

ebelp

matnr

FROM ekpo INTO TABLE int_ekpo

FOR ALL ENTRIES IN int_ekko

WHERE ebeln EQ int_ekko-ebeln.

SELECT ebeln

ebelp

menge

dabmg

FROM eket INTO TABLE int_eket

FOR ALL ENTRIES IN int_ekpo

WHERE ebeln EQ int_ekpo-ebeln AND

ebelp EQ int_ekpo-ebelp.

LOOP AT int_eket INTO wa_eket.

ENDLOOP.

Regards,

Abdul