illegal reference to correlation name

[Former Member]

I get the error "Illegal reference to correlation name 'edef'", but I don't know why or how to fix it. Here is my SQL statement. Please help!

/*
gets employee clock in/clock out times and total hours
*/

select
    edef.emp_seq,
    edef.obj_num,
    edef.payroll_id,
    edef.last_name,
    edef.first_name,

    dtl.clk_in_date_tm,
    dtl.clk_out_date_tm,
    dtl.reg_hrs,

    ot.order_type_seq,
    otdef.name
from
    MICROS.emp_def edef,

    MICROS.time_card_dtl as dtl

left join 
(
    select
        MICROS.shift_emp_ot_ttl.emp_seq,
        MICROS.shift_emp_ot_ttl.order_type_seq
    from
        MICROS.shift_emp_ot_ttl
)ot on ot.emp_seq = edef.emp_seq
left join
(
    select
        micros.order_type_def.order_type_seq,
        micros.order_type_def.name
    from
        micros.order_type_def
)otdef on otdef.order_type_seq = ot.order_type_seq
where
    edef.emp_seq = dtl.emp_seq
    and 
    dtl.clk_in_date_tm between '2013-05-06 00:07:00' and '2013-05-07 00:04:00'
order by
edef.last_name

[MarkCulp]

I think it might help to know which version and build number you are using?

[Former Member]

I did a select @@version and it gives this:

9.0.2.3586

Is that what you need?

I should also mention that I don't know Sybase very well. I mainly use MySQL.

[Former Member · Accepted solution]

I was able to get it working:

/*
gets employee clock in/clock out times and total hours
*/
select
    edef.emp_seq,
    edef.obj_num,
    edef.payroll_id,
    edef.last_name,
    edef.first_name,

    dtl.clk_in_date_tm,
    dtl.clk_out_date_tm,
    dtl.reg_hrs,
    dtl.name

from
    MICROS.emp_def edef

left join
(
    /*
        get clock in/out times and total hours
    */
    select
        MICROS.time_card_dtl.emp_seq,
        MICROS.time_card_dtl.clk_in_date_tm,
        MICROS.time_card_dtl.clk_out_date_tm,
        MICROS.time_card_dtl.reg_hrs,
        MICROS.time_card_dtl.job_seq,
        jd.name
    from
        MICROS.time_card_dtl
    inner join
        micros.job_def jd on jd.job_seq = MICROS.time_card_dtl.job_seq
    where
        MICROS.time_card_dtl.clk_in_date_tm between '2013-05-06 00:07:00' and '2013-05-07 00:04:00'
    group by
        MICROS.time_card_dtl.emp_seq, MICROS.time_card_dtl.clk_in_date_tm, MICROS.time_card_dtl.clk_out_date_tm, MICROS.time_card_dtl.reg_hrs, MICROS.time_card_dtl.job_seq, jd.name
    order by
        MICROS.time_card_dtl.emp_seq
)dtl on dtl.emp_seq = edef.emp_seq

where

    dtl.clk_in_date_tm between '2013-05-06 00:07:00' and '2013-05-07 00:04:00'
order by
edef.last_name

[VolkerBarth · Accepted solution]

For the record: It would be nice if you could comment what exactly you have altered to make the statement work. - Surely we can compare the queries ourselves, but that will take time for any reader of your answer...