RE: Problem with Outer Join
Aleem, Your query should be: SELECT year_date, to_char(attend_date, 'hh:mi:ss' ) FROM A, B WHERE to_char(year_date, 'dd-mm-') = to_char(attend_date,'dd-mm-') (+) The above says to return all records from the year_date table and , oh, by the way, return any records in the employee table where dates match. A better way to do this would be: WHERE trunc(year_date) = trunc(attend_date) (+) The Trunc command simply strips all time from date columns. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, August 22, 2002 8:38 AM To: Multiple recipients of list ORACLE-L Hi, In our employee's attendance application the attendance data is stored in table 'A' including employee ID and attendance date time. Another table 'B' has one column with dates of current year (365 records). For the sake of simplicity, other fields are ignored. Table: A Employee_ID Attend_date (date with time) Table: B year_date (one record each for a day of current year i.e., 365 records) In a report (single employee at a time) showing all dates of the year and in front of it whether the employee was present or not we are running the following query. Assume that table A has 15 records of employee ID 123, the query should return 365 records with 15 records telling the time but it returns 15 records only. SELECT year_date, to_char(attend_date, 'hh:mi:ss' ) FROM A, B WHERE to_char(year_date, 'dd-mm-') = to_char(attend_date(+), 'dd-mm-') and employee_id = 123; To my understanding this is perhaps because of the filter employee_ID=123. What would be the query to get the required result? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with Outer Join
Tom, I think by the post he wants to limit result set by employee_id for ex. employee_id=123 Rick Mercadante, Thomas F To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: ate.ny.us Subject: RE: Problem with Outer Join Sent by: [EMAIL PROTECTED] 08/22/2002 08:58 AM Please respond to ORACLE-L Aleem, Your query should be: SELECT year_date, to_char(attend_date, 'hh:mi:ss' ) FROM A, B WHERE to_char(year_date, 'dd-mm-') = to_char(attend_date,'dd-mm-') (+) The above says to return all records from the year_date table and , oh, by the way, return any records in the employee table where dates match. A better way to do this would be: WHERE trunc(year_date) = trunc(attend_date) (+) The Trunc command simply strips all time from date columns. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, August 22, 2002 8:38 AM To: Multiple recipients of list ORACLE-L Hi, In our employee's attendance application the attendance data is stored in table 'A' including employee ID and attendance date time. Another table 'B' has one column with dates of current year (365 records). For the sake of simplicity, other fields are ignored. Table: A Employee_ID Attend_date (date with time) Table: B year_date (one record each for a day of current year i.e., 365 records) In a report (single employee at a time) showing all dates of the year and in front of it whether the employee was present or not we are running the following query. Assume that table A has 15 records of employee ID 123, the query should return 365 records with 15 records telling the time but it returns 15 records only. SELECT year_date, to_char(attend_date, 'hh:mi:ss' ) FROM A, B WHERE to_char(year_date, 'dd-mm-') = to_char(attend_date(+), 'dd-mm-') and employee_id = 123; To my understanding this is perhaps because of the filter employee_ID=123. What would be the query to get the required result? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author
RE: Problem with Outer Join
Rick, That's not how I read it. He wants all dates returned showing whether an employee was present or not. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, August 22, 2002 9:39 AM To: Multiple recipients of list ORACLE-L Tom, I think by the post he wants to limit result set by employee_id for ex. employee_id=123 Rick Mercadante, Thomas F To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: ate.ny.us Subject: RE: Problem with Outer Join Sent by: [EMAIL PROTECTED] 08/22/2002 08:58 AM Please respond to ORACLE-L Aleem, Your query should be: SELECT year_date, to_char(attend_date, 'hh:mi:ss' ) FROM A, B WHERE to_char(year_date, 'dd-mm-') = to_char(attend_date,'dd-mm-') (+) The above says to return all records from the year_date table and , oh, by the way, return any records in the employee table where dates match. A better way to do this would be: WHERE trunc(year_date) = trunc(attend_date) (+) The Trunc command simply strips all time from date columns. Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, August 22, 2002 8:38 AM To: Multiple recipients of list ORACLE-L Hi, In our employee's attendance application the attendance data is stored in table 'A' including employee ID and attendance date time. Another table 'B' has one column with dates of current year (365 records). For the sake of simplicity, other fields are ignored. Table: A Employee_ID Attend_date (date with time) Table: B year_date (one record each for a day of current year i.e., 365 records) In a report (single employee at a time) showing all dates of the year and in front of it whether the employee was present or not we are running the following query. Assume that table A has 15 records of employee ID 123, the query should return 365 records with 15 records telling the time but it returns 15 records only. SELECT year_date, to_char(attend_date, 'hh:mi:ss' ) FROM A, B WHERE to_char(year_date, 'dd-mm-') = to_char(attend_date(+), 'dd-mm-') and employee_id = 123; To my understanding this is perhaps because of the filter employee_ID=123. What would be the query to get the required result? TIA! Aleem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Abdul Aleem INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note
