illegal reference to correlation name

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

asked 10 May '13, 10:44

raphael
151●5●6●14
accept rate: 33%

edited 10 May '13, 11:15

Mark Culp
24.8k●9●139●295

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

(10 May '13, 11:02) Mark Culp

Replies hidden

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.

(10 May '13, 11:13) raphael

flat view

3 Answers:

active answers oldest answers newest answers popular answers

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

permanent link

answered 10 May '13, 16:18

raphael
151●5●6●14
accept rate: 33%

edited 10 May '13, 16:18

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

(13 May '13, 08:51) Volker Barth

flat view

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

permanent link

answered 10 May '13, 16:21

Tyson Lewis
2.2k●16●41
accept rate: 22%

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

(13 May '13, 07:13) raphael

flat view

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

permanent link

answered 10 May '13, 13:18

Nica _SAP
866●7●22
accept rate: 3%

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

(10 May '13, 14:11) raphael

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

asa-9 ×107
error ×93
query-help ×12
correlation ×3

question asked: 10 May '13, 10:44

question was seen: 8,363 times

last updated: 13 May '13, 08:51

SAP SQL Anywhere

illegal reference to correlation name

Follow this question

Related questions