How to put sql statements so that I can use it in a Calculation view?

[former_member210481]

Hi,

I have few DML STATEMENTS THAT I WOULD LIKE TO RUN VIA A PROCEDURE SO THAT I CAN USE IT AS AN INPUT INA CALVIEW, HOW WOULD I IMPLEMENT IT.

mY SQL STATEMENT IS

UPDATE "AVG_DIM" SET "MONTH" = MONTH(TO_DATE("ZDATE",'YYYYMMDD'));

UPDATE "AVG_DIM" SET

"YTD" = case when month(current_date) >= "MONTH"

       then 'Y' else 'N' end,

       "LASTTHREEMONTH" = case when month(current_date)-3 >= "MONTH"

       then 'N' else 'Y' end,

       "LASTMONTH" = case when month(current_date)-1 = "MONTH"

then 'Y' else 'N' end,

"LASTSIXMONTH" = case when month(current_date)-6 >= "MONTH"

then 'N' else 'Y' end;

[sreehari_vpillai]

Hi ,

You can write the statements in a procedure and call the same in calculation view. But note that, the procedure should be READ ONLY; Which means, you can not update the database inside the procedure. Calc views are meant for fetching the data and not an update module.

Sree

[former_member226419]

Hi Sree,

Then in this case , calling procedure inside calculation view will not work, since he tries to update the table..

Am i understanding correctly?

Br

Sumeet

[former_member226419]

Hi,

Create a procedure---> insert these statements in the same..----> create one scripted calculation view---> call your procedure in your scripted calculation view as

var_out = call procedure name();

Br

Sumeet