1) you need to reference C_cursor in the loop (not r_cursor) - typo?
2) since it's PL/SQL, I'd do a "SELECT INTO myvar" before the INSERT and use myvar in the DECODE
select decode(l_date_flag, 'Y', last_day(add_months(sysdate, -1)),'E', requested_current, sysdate)
from so_headers_all where header_id= r_cursor.HEADER_ID
In addition to what DaPi said, the values don't lione up with the columns. For every column there is one value to go with it. Not to mention that there were numerous extra characters as well as a missing colum. This is my interpretation of what you were trying to do.
Originally posted by Barabas I could'nt get it working using the select in the decode statement.
There are limitations on where you can put a select statement - these limitations are version dependant and in some versions they are different between SQL & PL/SQL. You need to RT*M(s) to find out exactly what you can and can't do.
If you insist on it being a single SQL statement, you will need to recode it something like:
INSERT INTO sales_ledger(. . . .)
(SELECT va1, val2, . . . , decode(...) . . . FROM so_headers_all WHERE . . .)
But I still think it will be more readable if you separate the select & decode from the insert.