-
simple procedure help needed
Folks,
I inherited this procedure which updates column TLC with our weeks generated from function calculate_week. I'm pretty dumb in PL/SQL so please help in making this procedure work as this is updating Null values into column. Our create_date column is of Timestamp with local timezone data type.-Oracle 9i rel2 on Nt
CREATE OR REPLACE PROCEDURE TEST_WEEK
AS
V_CREATE_DATE NUMBER;
V_SEQ_ID NUMBER;
CURSOR getcrsrww IS
SELECT CALCULATE_WEEK(TO_CHAR(CREATE_DATE,'DD-MON-YY HH:MI:SSXFFAM')) INTO V_CREATE_DATE FROM BIN_NOT
WHERE TIN_SEQ_ID =V_SEQ_ID;
BEGIN
UPDATE BIN_NOT SET TLC = V_CREATE_DATE;
END TEST_WEEK;
Thanks
Last edited by portal; 03-21-2005 at 07:22 PM.
sat
-
Hun, why is the cursor not open? Is it possible to access data
without opening it? Can u please list the body of the function
CALCULATE_WEEK.
-
This function when executed yeilds the results correctly as per our rqmnt. Only thro procedure its giving a problem with Nulls.
create or replace function CALCULATE_WEEK
(P_DATE IN BIN_BOT.CREATE_DATE%TYPE
)
RETURN NUMBER
IS
last_day_in_the_week BIN_BOT.CREATE_DATE%TYPE;
last_day_in_the_first_week BIN_BOT.CREATE_DATE%TYPE;
number_of_weeks_in_between number;
begin
last_day_in_the_week := next_day(p_date-1,'saturday');
last_day_in_the_first_week := next_day(trunc(last_day_in_the_week,'yyyy')-1,'saturday');
number_of_weeks_in_between := ( to_number(to_char(last_day_in_the_week,'ddd'))
-
to_number(to_char(last_day_in_the_first_week,'ddd'))) / 7;
return ( to_number( to_char(last_day_in_the_first_week,'yyyy') ) * 100
+ number_of_weeks_in_between + 1 );
end;
Thanks
sat
-
can u identify the fields/columns that contains nulls and are giving
u problems? u can apply nvl(fld1,0) to it
-
or put condition filter "where col1 is not null"
-
After this modification also its updating null values, please advice me guys
CREATE OR REPLACE PROCEDURE TEST_WEEK
AS
V_CREATE_DATE NUMBER;
V_SEQ_ID NUMBER;
CURSOR getcrsrww IS
SELECT CALCULATE_WEEK(TO_CHAR(CREATE_DATE,'DD-MON-YY HH:MI:SSXFFAM')) INTO V_CREATE_DATE FROM BIN_NOT
WHERE TIN_SEQ_ID =V_SEQ_ID;
BEGIN
OPEN getcrsrww;
fetch getcrsrww into V_CREATE_DATE;
UPDATE BIN_NOT SET TLC = V_CREATE_DATE;
close getcrsrww;
END TEST_WEEK;
sat
-
u only need guys help
did u know that ladies are the topnotch in exams
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|