Updating Columns

[szeiss]

Using Oracle 9i, I can't seem to get this right.

I have a table (tbl_table) with 3 columns which are all dates (col_date1 & col_date2 & col_date3). I'm trying to get the number of elapsed days as a percentage between col_date2 & col_date3. Some columns will be null.

UPDATE tbl_table
SET col_date1 =
(CASE
WHEN col_date2 is null THEN null
WHEN (col_date2 -sysdate)/(col_date2 -col_date3) > 100 THEN 100
WHEN (col_date2 -sysdate)/(col_date2 -col_date3) < 100 THEN
(col_date2 -sysdate)/(col_date2 col_date3)
END CASE
WHERE USERNAME = p_user;

Thanks,
Sherry

 

[jaxtell]

Putting a number like that into a date column doesn't make much sense to me. I'm thinking you want to make col_date1 a number column. You might need to do some conversions on the other columns before you try to do any math using them.

 

[SantaMufasa]

Sherry,

You code is problematic at several levels:[ul][li]The non-NULL results of your CASE statement are of datatype NUMBER...you are UPDATE-ing those numeric results into col_date1, a DATE datatype...such causes a run-time error.[/li][li]Unless col_date2 is always in the future, the result will always be NEGATIVE, which means that your first CASE...WHEN will never be > 100.[/li][li]Your second "THEN" code fragment, "(col_date2 -sysdate)/(col_date2 col_date3)" is syntactically incorrect since it is missing an arithmetic operator (I presume a "-") between col_date2 and col_date3.[/li][li]I presume, from your initial assignment of "100", that your intent for result assignments are percents that you want to specify as integer percents. So, if the diffences yield a ratio of ".5" that you want "50" percent to be the result, correct? If so, you must multiply your ratios by 100.[/li][/ul]Please address and correct these issues in your code, then repost your adjusted code, along with a restatement of what you are asking us to do for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[szeiss]

Your correct, the column I'm updating should be a number column and yes I'm trying to produce percentages. Col_date2 will always be a future date.

When col_date2 is null I want col_number1 to be null,

When (col_date2 - sysdate) / (col_date2 - col_date3) is greater than 100%, I want to show only 100%

When col_date2 has a value then (col_date2 - sysdate) / (col_date2 - col_date3)


UPDATE tbl_table
SET col_number1 =
(CASE
WHEN col_date2 is null THEN null
WHEN (col_date2 -sysdate)/(col_date2 -col_date3) > 100 THEN 100
WHEN (col_date2 -sysdate)/(col_date2 -col_date3) < 100 THEN
(col_date2 -sysdate)/(col_date2 col_date3)
END CASE
WHERE USERNAME = p_user;

Thanks

 

[SantaMufasa]

sherry,

If you fix the missing "-" in the clause (as I mentioned, above):

...(col_date2 -sysdate)/(col_date2 col_date3)...
                                  ^---- "-" goes here

...then your code should at least run. Whether it logically does what you want is something you will need to confirm.

Your original question was rather vague:

Using Oracle 9i, I can't seem to get this right.

I cannot tell if your are asking for help with[ul][li]syntax error(s) (Oracle gives syntax-error message(s).)[/li][li]run-time error(s) (Oracle throws errors during exection.)[/li][li]logic error(s) (No error messages, but the results are incorrect.)[/li][/ul]Please confirm what type(s) of errors you are encountering, along with any relevant error messages.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[szeiss]

Sorry, yes I did fix it on my end, but I keep getting 'missing right parenthesis' no matter what I do.

 

[SantaMufasa]

For future reference, it's good to post a copy-and-paste of the actual code (which will have visible line numbers), plus error message that appear on your SQL*Plus screen.

In your case, the 'missing right parenthesis' error results from the extraneous ";" at the end of your "END CASE" clause...it should read "END CASE)" <-- notice no semicolon.

Let us know the outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[szeiss]

I'm using TOAD.

Exact code:
UPDATE tbl_table
SET col_number1 =
(CASE
WHEN prelim_start is null THEN null
WHEN (prelim_start -sysdate)/(prelim_start -PER_DUE) > 100 THEN 100
WHEN (prelim_start -sysdate)/(prelim_start -PER_DUE) < 100 THEN
(prelim_start -sysdate)/(prelim_start - PER_DUE)
END CASE)
WHERE USERNAME = p_user;


TOAD ERROR: ORA-00907: missing right parenthesis

Taking out the semi-colon didn't make a difference.

Thanks,

 

[SantaMufasa]

Sherry,

The word "CASE" is extraneous on the "END CASE" clause, plus the initial and ending parentheses are extraneous, as well. Here is the code that I got working from SQL*Plus (and the should work fine in TOAD):

UPDATE tbl_table
    SET col_number1 =
case
       WHEN prelim_start is null THEN null
       WHEN (prelim_start -sysdate)/(prelim_start -PER_DUE) > 100 THEN 100
       WHEN (prelim_start -sysdate)/(prelim_start -PER_DUE) < 100 THEN
       (prelim_start -sysdate)/(prelim_start - PER_DUE)
end
where USERNAME = p_user;

1 row updated.

Let us know your findings, as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ChrisHunt]

This would work too:

UPDATE tbl_table
    SET col_number1 = LEAST(100,
                            (prelim_start -sysdate)*100/(prelim_start - PER_DUE))
where USERNAME = p_user;

Note that to get a percentage you need to multiply the numerator by 100.

Also, it's generally considered bad practice to store this kind of "derived field" in the database. If you've got both dates in the table you don't need to store the result of this calculation - just do it when you need it.



-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd