Cannot use Select within DECODE Stmnt?

[Barabas]

Hello all


Getting a complile error, when i try and use the following decode statement within a cursor for loop.

I cant compile as the pkg does not like the select statement within the decode statement,

Is the decode statement restricting the ability to perform a select statement within it?

Anybody else encounter this problem or know of a way round in when using the decode statement?

Thanks in advance



This is the simplified code:

For C_cursor in r_curosr

INSERT INTO SALES_LEDGER (
LAST_UPDATE_DATE, /
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_ID,
GROUP_ID,
SPREAD_FLAG,
TRANSACTION_REASON,
DERIVED_PROCESS_FLAG,
quantity,
total_value_order,
reporting_date

)
VALUES (

SYSDATE,
SYSDATE
g_user_id,
SYSDATE,
g_user_id,
g_login_id,
LEDGER_S1.NEXTVAL,
l_group_id,
r_cursor.SPREAD_FLAG,
'Scheduled Change',
'N',
l_sum_qty,
l_total_val,
decode(l_date_flag, 'Y',last_day(add_months(sysdate, -1)),'E',
(select date_requested_current from
so_headers_all where header_id= r_cursor.HEADER_ID),sysdate)

);
END Loop;


This is the error, i cant compile as the pkg does not like the select statement within the decode statement

(1):PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
(1):
(1): ( - + mod not null others
(1): avg
(1): count current exists max min prior sql stddev sum variance
(1): execute forall time timestamp interval date
(1):
(1):
(2):PLS-00103: Encountered the symbol "," when expecting one of the following:
(2):
(2): ; return returning and or

[DaPi]

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
otherwise try:
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

[gandolf989]

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.

SELECT date_requested_current
  INTO v_my_date
  FROM so_headers_all 
 WHERE header_id = r_cursor.header_id;

INSERT INTO sales_ledger
   ( last_update_date,
     last_updated_by,
     last_update_login,
     transaction_id,
     GROUP_ID,
     spread_flag,
     transaction_reason,
     derived_process_flag,
     quantity,
     total_value_order,
     reporting_date )
VALUES ( SYSDATE,
     g_user_id,
     SYSDATE,
     g_user_id,
     ledger_s1.NEXTVAL,
     l_group_id,
     r_cursor.spread_flag,
     'Scheduled Change',
     l_sum_qty,
     l_total_val,
     v_my_date );

[Barabas]

Thanks for your input.

Apologies for the typos, i relaize that they did'nt help my plight!

I used the select date requested current into a variable, and used the variable in my decode statement.

I could'nt get it working using the select in the decode statement.

[gandolf989]

Originally posted by Barabas
Thanks for your input.

Apologies for the typos, i relaize that they did'nt help my plight!

I used the select date requested current into a variable, and used the variable in my decode statement.

I could'nt get it working using the select in the decode statement.

If you are getting an error now is the time to post it.

[DaPi]

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.