Question Re: illegal reference to correlation name in Technology Q&A

illegal reference to correlation name

Former Member — Fri, 10 May 2013 10:44:57 GMT

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

Re: illegal reference to correlation name

MarkCulp — Fri, 10 May 2013 11:02:44 GMT

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

Re: illegal reference to correlation name

Former Member — Fri, 10 May 2013 11:13:16 GMT

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.

Re: illegal reference to correlation name

Former Member — Fri, 10 May 2013 13:18:41 GMT

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

Re: illegal reference to correlation name

Former Member — Fri, 10 May 2013 14:11:40 GMT

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

Re: illegal reference to correlation name

Former Member — Fri, 10 May 2013 16:18:12 GMT

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

Re: illegal reference to correlation name

Former Member — Fri, 10 May 2013 16:21:28 GMT

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

Re: illegal reference to correlation name

Former Member — Mon, 13 May 2013 07:13:23 GMT

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

Re: illegal reference to correlation name

VolkerBarth — Mon, 13 May 2013 08:51:21 GMT

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