illegal reference to correlation name

[MarkCulp]

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]

The SQL is valid. Please send to us the result string from the statements:

set temporary option quoted_identifier='off'; select rewrite(" <your query="" here=""> ");

Thanks Ani

[Former Member]

I tried the statement below but I still got the "ERROR: [Sybase][ODBC Driver][Adaptive Server Anywhere]Illegal reference to correlation name 'edef'" - Error code: -824 error.

It's happening on the first join, because if I comment out the 2 join blocks and the references to the in the select, the statement works with no errors.

Did I format it correctly?

    /*
gets employee clock in/clock out times and total hours
*/
set temporary option quoted_identifier='off';
select rewrite("
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,
    ot.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,
        otdef.name
    from
        MICROS.shift_emp_ot_ttl
    inner join
        micros.order_type_def otdef on otdef.order_type_seq = MICROS.shift_emp_ot_ttl.order_type_seq
)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");