Can interval take a value from a field?
Sometimes I think the hurdle to learning more advanced sql is figuring
out how to phrase the search question. Well, I'm not having any luck
there so here I come again to the list, hat in hand :-)
I have a table with an id key and an expiration value in years
trgexpd_trg_id | expiration_value
----------------+------------------
240 | 1 year
749 | 3 years
4917 | 2 years
and so on.
I'm trying to use the expiration value as the value for an interval in a
query that looks like:
select
ts_date as "Transcript Date",
ts_expiration_date as "Current Expiration Date",
expiration_value as "Expiration Interval"
from transcript, training_expiration_value where
ts_training_id = trgexpd_trg_id and
ts_training_id in (select cda_training_number from cdas) and
ts_expiration_date != ts_date + interval 'expiration_value';
and I'm getting the following error:
ERROR: invalid input syntax for type interval: "expiration_value"
Is there a way to use the value in expiration_value for the interval?
Thanks,
Jeff Ross
On Tue, 2008-09-09 at 17:03 -0600, Jeff Ross wrote:
select
ts_date as "Transcript Date",
ts_expiration_date as "Current Expiration Date",
expiration_value as "Expiration Interval"
from transcript, training_expiration_value where
ts_training_id = trgexpd_trg_id and
ts_training_id in (select cda_training_number from cdas) and
ts_expiration_date != ts_date + interval 'expiration_value';and I'm getting the following error:
ERROR: invalid input syntax for type interval: "expiration_value"
This error is saying that it is trying to convert the string
'expiration_value' to an interval.
What you really want it to convert the string value held inside a
variable named "expiration_value" to an interval.
For that, you need to do expiration_value::interval
Regards,
Jeff Davis
Jeff Davis wrote:
On Tue, 2008-09-09 at 17:03 -0600, Jeff Ross wrote:
select
ts_date as "Transcript Date",
ts_expiration_date as "Current Expiration Date",
expiration_value as "Expiration Interval"
from transcript, training_expiration_value where
ts_training_id = trgexpd_trg_id and
ts_training_id in (select cda_training_number from cdas) and
ts_expiration_date != ts_date + interval 'expiration_value';and I'm getting the following error:
ERROR: invalid input syntax for type interval: "expiration_value"
This error is saying that it is trying to convert the string
'expiration_value' to an interval.What you really want it to convert the string value held inside a
variable named "expiration_value" to an interval.For that, you need to do expiration_value::interval
Regards,
Jeff Davis
Thank you! That combined with the subselect wrapper trick I learned
last time I visited the list hat in hand worked wonderfully.
Jeff