Thread: 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