on 2013 May 10 11:44 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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");
User | Count |
---|---|
79 | |
11 | |
10 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.