topic Re: SELECT Statement in Application Development and Automation Discussions

SELECT Statement

Former Member — Tue, 06 Jan 2009 19:38:39 GMT

Guys,

so I would like to insert some data from one table to another one based on its calender day (Date). Is this correct statement?

Yesterday = Sy-Datum - 1

SELECT * from SOURCETABLE into DESTINATIONTABLE WHERE CALDAY = Yesterday.

ENDSELECT.

I can see some records when I debug but then the new table stays empty. Is there a reason why?

Thanks,

Re: SELECT Statement

Former Member — Tue, 06 Jan 2009 19:50:15 GMT

Are you trying to put the data into a database table or an internal table.

Rob

Re: SELECT Statement

amit_khare — Tue, 06 Jan 2009 19:55:21 GMT

SELECT * from SOURCETABLE into TABLE DESTINATIONTABLE WHERE CALDAY = Yesterday

Re: SELECT Statement

former_member191735 — Tue, 06 Jan 2009 21:57:50 GMT

Make sure you the source table is DB table and use into statement before target table check the data in table (SE16) for the date you have specified and define Yesterday type CALDAY

Re: SELECT Statement

nkr1shna — Tue, 06 Jan 2009 23:10:24 GMT

Hi Ram,

My suggestion here is to replace SELECT.... ENDSELECT construct and code with SELECT.... INTO TABLE construct which can resolve your issue.

Best Regards,

Krishna

Re: SELECT Statement

Former Member — Wed, 07 Jan 2009 00:17:15 GMT

Guys,

This is not an internal table. It is a database table that I created which will keep physical data and retain the data therefore, I don't think

SELECT * from SOURCETABLE into TABLE DESTINATIONTABLE WHERE CALDAY = Yesterday

will work as it is not an internal table.

Thanks,

Re: SELECT Statement

Former Member — Wed, 07 Jan 2009 02:17:36 GMT

Re: SELECT Statement

Former Member — Wed, 07 Jan 2009 02:31:04 GMT

hi,

Using the Select statement you cannot update the database table ....for this you need to select the data in to internal table and using the UPDATE statement update the DB table..

ITAB will be internal table of type DESTINATIONTABLE.

SELECT * from SOURCETABLE into TABLE ITAB WHERE CALDAY = Yesterday.

UPDATE DESTINATIONTABLE FROM TABLE ITAB.

Re: SELECT Statement

Former Member — Wed, 07 Jan 2009 03:07:03 GMT

Hi,

It is not permitted by SAP to update a database table directly using sql statement. Instead you have to use some other techniques like BAPI to update the same.

Using select query we cannot directly update a database table. Instead fetch the relevant records to an internal table and then using UPDATE statement, you can update the database table. check the link below for your reference.

http://www.abapcode.info/2007/12/updating-database-tables-sql-query.html

Regards,

John

Re: SELECT Statement

Former Member — Wed, 07 Jan 2009 03:10:23 GMT

Hi Ram,

In order to Insert data into a database table you need to use the INSERT statement.

First SELECT data from your SOURCE table into an Internal Table of the same type -

SELECT * FROM <source_table> INTO TABLE <internal_table> WHERE CALDAY = yesterday.

Now your Internal Table has all the data from SOURCE table where CALDAY is yesterday.

Insert this data into your DESTINATION table using INSERT statement -

INSERT <destination_table> FROM TABLE <internal_table>.

Regards,

Himanshu

Re: SELECT Statement

Former Member — Wed, 07 Jan 2009 04:36:17 GMT

Hi,

You can not update the database table by using SELECT statement

1. Select the data into ITAB by using SELECT statement.

2. Use update statement to update the database table.

SELECT * FROM SOURCETABLE INTO TABLE DESTINATION WHERE CALDAY = Yesterday.

UPDATE DESTINATIONTABLE FROM TABLE ITAB.

Regards,

Jyothi CH.

Re: SELECT Statement

former_member222860 — Wed, 07 Jan 2009 07:22:06 GMT

Hi,

u can try with native sql.

just like this:

EXEC SQL.

INSERT  INTO vbak_2 SELECT * FROM VBAK_1 

ENDEXEC

regards\

Mahesh

Re: SELECT Statement

Former Member — Wed, 07 Jan 2009 10:03:55 GMT

Hi Ram

You are not allowed to update the database table directly through a SQL query.

Please use:

SELECT * from SOURCETABLE into TABLE itab WHERE CALDAY = Yesterday.

UPDATE DESTINATIONTABLE from TABLE itab.

Moreover, the above statement will enable you to remove the ENDSELECT statement and will thus enhance your o/p performance.

Re: SELECT Statement

Former Member — Wed, 07 Jan 2009 15:27:12 GMT

I did as you guys suggested. I can see the data coming into Internal Table but then it does not seem update my target table. Here is my code.

TABLES: /BI0/PCOSTCENTER, ZMyCTR.

DATA: it_Cost_CTR like /BI0/PCOSTCENTER occurs 0,

wa_Cost_CTR like /BI0/PCOSTCENTER.

DATA: YESTERDAY TYPE d.

SEVENDAYS = SY-DATUM - 1.

SELECT * from /BI0/PCOSTCENTER into wa_Cost_CTR WHERE CALDAY = YESTERDAY.

Append wa_Cost_CTR to it_Cost_CTR.

UPDATE ZMyCTR from TABLE it_Cost_CTR.

ENDSELECT.

Anything wrong with it???

Thanks,

Re: SELECT Statement

Former Member — Wed, 07 Jan 2009 15:41:35 GMT


> Anything wrong with it???

Yes - you're confusing internal tables with the work area.

Rob

Re: SELECT Statement

Former Member — Wed, 07 Jan 2009 15:41:49 GMT

Hi,

Don't use the update statment, instead use the modify statment.


MODIFY  dbtab      FROM TABLE itab

And make sure you keep that statement outside the endselect. I would rather suggest you directly select the data into the internal table using select ....into table see below


SELECT * from /BI0/PCOSTCENTER into table it_Cost_CTR WHERE CALDAY = YESTERDAY.

modify ZMyCTR from TABLE it_Cost_CTR

Now modify will create a record if it doesn't exist with the key fields of the Z table and update otherwise.

regards,

Advait

Re: SELECT Statement

Former Member — Wed, 07 Jan 2009 16:29:14 GMT

I want to say thanks to everyone who gave me their inputs and especially Advait. I was able to fix the issue by using Modify statement. I wish I could give 6 pts to more than just 2 people.

Thanks again guys,

Re: SELECT Statement

Former Member — Wed, 07 Jan 2009 19:54:41 GMT

You might have been able to SELECT directly into the database table using a subquery, but I really don't know if it would work.

Rob