cancel
Showing results for 
Search instead for 
Did you mean: 

illegal reference to correlation name

Former Member
9,452

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
Participant
0 Kudos

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

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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
Contributor

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...

Answers (2)

Answers (2)

Former Member

Perhaps the joins need some swapping. Specifically, in the order it is written you have

... edef, ... dtl left join (...) ot on ot.emp_seq = edef.emp_seq

The operands (dtl and ot) for the left join do not match the join condition correlations (ot and edef). Swapping the order of edef and dtl in the query may allow the query to execute without error:

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.time_card_dtl as dtl,

    MICROS.emp_def edef -- switch position of dtl and edef

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
Former Member
0 Kudos

Wow, it looks like that worked as well! So we have at least 2 ways to write this. Thank you!

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
0 Kudos

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");