Re: unknown function or variable in expression

[John Kellogg <johnk@EAGLE.MITI.COM>]

Another way is to always select all three possible date values and decide
which one to use. This way you don't need the nested selects:

Begin-Procedure Get-Refit
Begin-Select
r.mp_rft_strt_dt,
r.mp_rft_cmpltn_dt
      do Get-Job-Data
    from tt_uicrft r
End-Select
End-Procedure

Begin-Procedure Get-Job-Data
Begin-Select
j.mt_jcn_nr,
convert(char(5), j.mt_schdld_job_cmpltn_dt,1) &jcn_eday
convert(char(5),dateadd(dd,(j.mt_rltv_job_cmpltn_da - 1),
        &r.mp_rft_start_dt),1) &date1
convert(char(5),dateadd(dd, -(j.mt_rltv_job_cmpltn_da),
        &r.mp_rft_start_dt),1) &date2
j.mt_rltv_job_strt_cd                         &rltv_cd

     if isnull(&jcn_eday)
        evaluate &rltv_cd
        when = 'A'
           let $use_date = &date1
           break
        when = 'B'
           let $use_date = &date2
           break
        end-evaluate
     else
        let $use_date = &jcn_eday
     end-if
     show ' comp ' $use_date
     from tt_job j
    where j.key_field = &u.key_field

End-Select
End-Procedure

John L. Kellogg
MITI Technical Support Manager
Angel Guzman wrote:
>
> I am having problems getting a routine coded correctly.
> I am trying to print a partial date value (mm/dd), but if the date is
> null then the date to be printed would be based on another date.
> I have coded this is various ways, at times I have gotten the 1301
> error (forward ref not perm in sel list bind vars).
> I'd really appreciate anyone's help.
>
> Begin-Procedure Get-Refit
> Begin-Select
> r.mp_rft_strt_dt,
> r.mp_rft_cmpltn_dt
>       do Get-Job-Data
>     from tt_uicrft r
> End-Select
> End-Procedure
>
> Begin-Procedure Get-Job-Data
> Begin-Select
> j.mt_jcn_nr,
> convert(char(5), j.mt_schdld_job_cmpltn_dt,1) &jcn_eday,
> j.mt_rltv_job_strt_cd                         &rltv_cd
>
>      if isnull($jcn_eday) = 1 AND $rltv_cd = 'A'
>         convert(char(5),
>                 dateadd(dd,(j.mt_rltv_job_cmpltn_da - 1),
>                            &r.mp_rft_start_dt),1) &jcn-eday,
>      end-if
>      if isnull($jcn_eday) = 1 AND $rltv_cd = 'B'
>         convert(char(5),
>                 dateadd(dd, -(j.mt_rltv_job_cmpltn_da),
>                               &r.mp_rft_start_dt),1) &jcn-eday,
>      end-if
>      show ' comp ' $jcn_eday
>      from tt_job j
>     where j.key_field = &u.key_field
>
> End-Select
> End-Procedure