User-Sql Failing

[Bill_Henderson]

Hello all,__New to version 10. I am trying to use a SQL command that uses FULL OUTER Join, any reason why this fails ? __ ____ __ __kfleming __Charter Member__1834 posts Mar-28-03, 03:13 PM (EST) __ __1. "RE: R&R v10.0.012 Sql-User"__In response to message #0__ __ Either the driver or R&R^s driver interface does not like your syntax.__What driver are you using and what is the exact SELECT that you are entering?__Kathleen__R&R Support____ ____ __ Bill Henderson __unregistered user__ Mar-30-03, 09:23 AM (EST) __ __2. "RE: R&R v10.0.012 Sql-User"__In response to message #1__ __ The driver and code below is what I^m using:__Microsoft SQL Server ODBC Driver Version 2000.81.9031.14______SELECT ISNULL(et.Empl_id, a.Empl_id) AS Empl_id, __ISNULL(et.Task_id, a.Task_id) AS Task_id, __ISNULL(et.Actual_Time, 0) + SUM(ISNULL(a.Actual_Time, 0)) AS Actual_Hours, __ISNULL(et.Adjust_Time, 0) + SUM(ISNULL(a.Adjust_Time, 0)) AS Adjust_Hours, __ISNULL(et.RecordDate, a.CurrentDate) AS RDate, __ISNULL(et.Comments, a.Comments) AS Comments, __ISNULL(et.Entry_Status, ^p^) AS Entry_Status____FROM EmpTime_Info et, FULL OUTER JOIN Adjustment a, ON et.Empl_id = a.Empl_id __AND et.Task_id = a.Task_id __AND a.CurrentDate = et.RecordDate____GROUP BY ISNULL(et.Empl_id, a.Empl_id), __ISNULL(et.Task_id, a.Task_id), __et.Actual_Time, __et.Adjust_Time, __ISNULL(et.RecordDate, a.CurrentDate), __ISNULL(et.Comments, a.Comments), __et.Entry_Status____ __ __ __ __ __

[kfleming]

The problem is in the interface layer that R&R uses to communicate with the ODBC driver. Although the driver understands full outer joins, R&R balks at FULL OUTER JOIN as an unknown.__Right now the only way around this would be to create a view in SQL that does the SELECT and then have R&R report on that view or to implement the join as two UNION^ed SELECTS one for the left join and one for the right.____R&R^s user SQL however will not allow you to create a view from the R&R side. You would need to use the SQL Query Analyzer or some other tool to create the view.____Kathleen__R&R Support