Select Statement

[Former Member]

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

[Former Member]

hi,

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

hi,

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