oracle - if statement in sql -
sql script
i use "if" statement output greater 365 days specific row should updated "yes" else "no"
if ( to_date('04/06/2015','dd/mm/rrrr')- lodg_date ) > 365 "yes" else "no" end if ; please advise
oracle doesn't support if
statement (outside of pl/sql blocks)
your question lists tags 3 different rdbms (mysql, postgresql , microsoft sql server), , each 1 things little bit differently.
in oracle, can subtract 2 date
expressions , difference value in days (whole , fractional.)
you can use case
expression conditional test, , return value depending on result of test.
for developing , testing expressions, in select statement, e.g.
select case when to_date('04/06/2015','dd/mm/yyyy')-t.lodg_date > 365 'yes' else 'no' end over_a_year , t.lodg_date mytable t
if want perform update operation, update rows in table, set column return expression,
update mytable t set t.my_over_a_year_yes_no_column = case when to_date('04/06/2015','dd/mm/yyyy')-t.lodg_date > 365 'yes' else 'no' end
note: to_date
function , the "subtract 2 date
expressions" pattern works in oracle. won't work in other databases. other databases may allow string literals enclosed in double quotes, oracle not. string literals, 'yes'
, 'no'
have enclosed in single quotes. double quotes reserved enclosing identifiers. , if
not valid sql statement in oracle. can used in context of pl/sql block, that's control flow in pl/sql program; it's not sql statement that's submitted database.
Comments
Post a Comment