topic Re: Select Statement in Application Development and Automation Discussions

Select Statement

Former Member — Mon, 11 Feb 2008 04:33:21 GMT

Dear All,

I have to choose the distinct records from the table ANLZ into one Internal Table where the date(adatu) is maximum.

Now I have given the following select stmt. I am getting the internal table But not getting the adatu field.

SELECT DISTINCT anln1

MAX( adatu )

FROM anlz

INTO CORRESPONDING FIELDS OF TABLE gt_anlz

WHERE anln1 IN s_anln1

GROUP by anln1.

Output.

anln1 adatu

000000100119 |00000000|

Why I am not getting Date field? Please help me.

My actual requirement is like this

Need anln1 and pernr from the table according to the maximum date field.

SELECT a~anln1
MAX( a~adatu )
a~pernr
b~anlkl
FROM anlz AS a INNER JOIN anla AS b
ON a_{anln1 = b}anln1
INTO CORRESPONDING FIELDS OF TABLE gt_anlz
WHERE b~anlkl = '10150010' AND
a~anln1 IN s_anln1
GROUP BY A_{ANLN1 a}pernr B~ANLKL .

But this is also not working properly...

Thanks in Advance

Regards,

LIJO

Re: Select Statement

Former Member — Mon, 11 Feb 2008 04:43:31 GMT

Hi,

check these things..........

do u have anyother date field in that internal table(gt_anlz

)

1) if yes check the content of that field.....

2) if no change ur query as below

SELECT DISTINCT anln1
MAX( adatu )
FROM anlz
INTO CORRESPONDING FIELDS OF TABLE gt_anlz
WHERE anln1 IN s_anln1
and        adatu NE '00000000'
GROUP by anln1.

Cheers,

jose.

Re: Select Statement

Former Member — Mon, 11 Feb 2008 04:45:56 GMT

Hi..

The database column whose values are to be aggregated must not have the type STRING or RAWSTRING. ..

Here adatu is date filed which is stored as char8..

So u can not use max function to this filed.

Select from database.. and perform a logic to filterout largest date filed.

Hop this will help

Regards

Sheeba

Edited by: Sheeba Bhaskaran on Feb 11, 2008 10:16 AM

Re: Select Statement

Former Member — Mon, 11 Feb 2008 04:48:49 GMT

John,

Code like below .It will increase the performance of the program also.

SELECT a~anln1

a~adatu

a~pernr

b~anlkl

FROM anlz AS a INNER JOIN anla AS b

ON a_{anln1 = b}anln1

INTO CORRESPONDING FIELDS OF TABLE gt_anlz

WHERE b~anlkl = '10150010' AND

a~anln1 IN s_anln1.

SORT gt_anlz by anln1 adatu Descending.

DELETE ADJACENT DUPLICATES FROM anln1

COMPARING anln1.

Don't forget to reward if useful...

Re: Select Statement

Former Member — Mon, 11 Feb 2008 04:51:57 GMT

hi,

plzz dont use DISTINCT word whiele using select it will effect the performance of your report.