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's gravatar image

raphael
1515614
accept rate: 33%

edited 10 May '13, 11:15

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297

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

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's gravatar image

raphael
1515614
accept rate: 33%

edited 10 May '13, 16:18

1

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

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%20Lewis's gravatar image

Tyson Lewis
2.2k1641
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

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%20_SAP's gravatar image

Nica _SAP
866722
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
Your answer
toggle preview

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

Question tags:

×108
×95
×12
×3

question asked: 10 May '13, 10:44

question was seen: 8,904 times

last updated: 13 May '13, 08:51