|
|
Cooperative Extension Service |
|
 |
||
|
|
||
|
Agricultural
Experiment Station |
||
 |
|
|
Instead of creating a one-line SQL script containing a define statement to set variables at runtime, you can use the new_value option of the column command to set the variable's value generated by a select statement. The snippet below is taken from my Y2K Date Input tip. It checks the length of a date string to see if it has a 2-digit year or a 4-digit year, converting 2-digit years using the RR format and returning the date string with the resulting 4-digit year. To do this, it creates a define statement in a file, and runs the resulting sql file.
spool fieldina.sql
select 'define fieldval = ' || to_char(decode(sign(9-length('&fieldval')),-1,
to_date('&fieldval','DD-MON-YYYY'),to_date('&fieldval','DD-MON-RR')),
'DD-MON-YYYY') from dual;
spool off
@fieldina.sql
This can also be done without creating the sql file and running it. The select statement below does the conversion, producing a column named fieldvalset. The column command then takes the value in that fieldvalset column and stores that into the fieldval variable as its new value, eliminating the intermediate file. (The fieldvalset column could also just be called fieldval.) (Thanks to Bruce Knox here for this tip.)
column fieldvalset new_value fieldval
select to_char(decode(sign(9-length('&fieldval')),-1,
to_date('&fieldval','DD-MON-YYYY'),to_date('&fieldval','DD-MON-RR')),
'DD-MON-YYYY') fieldvalset from dual;
This Page was Last Updated on 06/04/07
You Are Visitor Number |
|
|
© 2006 |
|
|
University of Arkansas • Division of Agriculture |
Mission
•
Disclaimer
•
EEO
•
|
