topic Re: Select statement. in Application Development and Automation Discussions

Select statement.

Former Member — Tue, 17 Jun 2008 10:27:51 GMT

Dear experts,

I am currently working on an inserting records program.

Before the record is inserted into a table. It will perform a SELECT query to get the latest running number. My problem here is the COUNTER field in the table is designed in CHAR11.

With the SELECT statement showing below, it will hits a problem when the running number increased to 10. The select statement will not getting the latest number - 10. It will always getting number - 9. It is because it is descending order 10 is considering 1.

Here is my program :


SELECT charg counter FROM ztable_test UP TO 1 ROWS
  INTO (lf_charg, lv_counter)
  WHERE charg EQ ls_table-charg
    AND minch EQ ls_table-minch
    AND ccod1 EQ ls_table-ccod1
  ORDER BY counter DESCENDING.
ENDSELECT.
IF sy-subrc EQ 0.
  ls_osada-counter = lv_counter + 1.
  INSERT ztspp_osada2 FROM ls_osada.
  EXIT.
ENDIF.

I remember in either DB2 or Oracle, the select statement can be like this:


SELECT charg, INT(counter) FROM ztable_test
  WHERE charg = ls_table-charg
    AND minch = ls_table-minch
    AND ccod1= ls_table-ccod1
  ORDER BY INT(counter) DESCENDING.

How should I do it in ABAP?

I would really appreciate if someone can help me on this.

Sample of code would greatly appreciated.

Thanks in advance.

Re: Select statement.

christine_evans — Tue, 17 Jun 2008 13:35:35 GMT

I'm not sure I quite understand your problem. But if you want to get the MAX COUNTER value from the table for the criteria in your WHERE clause, why not just use MAX? Something like....

SELECT charg max( counter )
INTO      var1  var2
FROM    tab
WHERE   .....
GROUP BY charg.

And I'd probably define my COUNTER variable as integer and not char. Though char should work ok since all you're doing it just selecting a value into it.

......just re-read your original message and you say the counter field in the table is defined as char. Why would anyone want to define a counter field as char? If it were even defined as NUMC then would get the leading zeros that would make your ordering or the use of MAX work properly.

Is there any way that you get redefine the table column? If not, I'm not sure what you can do.

Edited by: Christine Evans on Jun 17, 2008 4:01 PM

Re: Select statement.

Former Member — Tue, 17 Jun 2008 14:13:56 GMT

myahsam

i have code but am not sure how can i put here as you create block for code.

can you please tell me how can we create a block for code so that i can paste a code inside it.

Amit.

Re: Select statement.

Former Member — Tue, 17 Jun 2008 14:20:37 GMT

Hi Christine Evans,

Thanks for your answer. It really solved my problem, but I need to change the field data type into NUMC2 instead of CHAR2.

I guess this is the better and faster way to solve the problem.

I never know there is a MAX feature in ABAP select statement.

Thanks alot.

Re: Select statement.

Former Member — Tue, 17 Jun 2008 14:24:42 GMT

Hi Amit,

What do you mean by block for code?

I think you are trying to ask how to paste the code into this thread.

"remove the space within the blacket

Paste your code here...

"remove the space within the blacket