topic Re: SQL Statement in Application Development and Automation Discussions

SQL Statement

Former Member — Fri, 19 Jun 2015 17:42:35 GMT

Hello All,

KeyField1	KeyField2	KeyField3	date	Time	Field4
F11	F21	1	20150101	131000	F4A
F11	F21	2	20150201	104000	F4B
F11	F21	3	20150301	112000	F4C
F11	F21	4	20150301	172000	F4D
F12	F22	5	20150103	154000	F4L
F12	F22	6	20141012	045300	F4S
F12	F22	7	20150606	224000	F4C
F12	F22	8	20150509	113700	F4d
F13	...
F14	...
.......	...
.......	.....

I have above fields in my table.

User can give input of Key fields 'Key Field1' and 'KeyField2' .

For example user has passed

Key Field1 in('F11', 'F12') and

Key Field2 in('F21', 'F22')

I require the latest one in these based on date and time i.e records highlighted in red with bigger font.

I know this can be done by getting the records with input key fields into internal table then sorting them by

KeyField1 Keyfield2 ASCENDING

Date Time Descending

and deleting adjacent duplicates comparing KeyField1 and KeyField2.

there are many similar way of dealing with this after bringing after bringing it into internal table.

Is there any way in Open SQL where we can get the exact required data(i.e the two records highlighted)

without getting additional records into application layer. The filtering sould occur at DB layer itself.

With regards,

Sandeep Akella

Re: SQL Statement

Former Member — Fri, 19 Jun 2015 17:56:02 GMT

I am afraid, you will have to go via the internal table processing way only.

Couldn't find a way to select the highlighted records via a SQL statement.

Re: SQL Statement

Ankit_Maskara — Sun, 21 Jun 2015 03:08:05 GMT

Hi,

You can try the subquery option.

write select as below -

select key_field1 key_field2 key_filed3 max(date) max(time) field4 from <table name>

into corresponding fields of table <tablename>

where key_field1 in ( select key_field1 where key_field1 in s_key_field1)

and key_field2 in ( select key_field2 where key_field1 in s_key_field2).

Assumptions : Only one unique combination of key_field1 and key_field2 will exist as shown by your data sample.

Also i have denoted the input values via select options s_key_field1 and s_key_field2. The aggregation function works with Date and Time type fields.

BR.

Re: SQL Statement

SwadhinGhatuary — Mon, 22 Jun 2015 08:26:27 GMT

Hi,

A dummy code

TABLES vbap.

TYPES:BEGIN OF ty_vbap,
vbeln TYPE vbeln,
posnr TYPE posnr,
END OF ty_vbap.

DATA lt_vbap TYPE TABLE OF ty_vbap.

SELECT-OPTIONS s_vbeln FOR vbap-vbeln.

SELECT vbeln MAX( posnr ) FROM vbap
INTO TABLE lt_vbap
WHERE vbeln IN s_vbeln
GROUP BY vbeln.

BREAK-POINT.

But I suggest go for Internal table to get Highlighted row instead of aggregation function,.

Re: SQL Statement

deepan_v_s — Mon, 22 Jun 2015 10:08:17 GMT

Hello Sandeep,

You can use a subquery to solve this.

Select * from table where

key1 = 'value' AND

key2 = 'value' AND

date IN (Select MAX (date) from table)

into INTERNAL_TABLE.

And sort the resulting IT based on TIME and KEY fields and pick up the latest record based on Date and Time.

This approach will reduce the number of records and load on the DB. As far i researched this is a good approach.

If you have Timestamp field in the table then it can be done efficiently based on MAX (Timestamp).

Hope this helps !!!

Regards,

Deepan Swaminathan.

Re: SQL Statement

Former Member — Tue, 30 Jun 2015 10:40:38 GMT

It worked well for me.

Thank you.

topic Re: SQL Statement in Application Development and Automation Discussions

SQL Statement

F11

F21

4

20150301

172000

F4D

F12

F22

7

20150606

224000

F4C

Re: SQL Statement

Re: SQL Statement

Re: SQL Statement

Re: SQL Statement

Re: SQL Statement