| Author |
 Topic  |
|
|
bob123
Starting Member
16 Posts |
 Posted - 04/12/2007 : 16:04:53
|
When trying to build a report in sql server 2005 business intelligence dev studio (to deploy to report server) based on the following query I get an error msg.
Error in group by clause.
Unable to parse query text.
SELECT sub.*, ap.apat_code
FROM k_apptreg ap,
(SELECT R.REFL_DATE, R.EVENT_NO, C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, P.SURNAME AS CLINICIAN,
R.DEPT_CODE, D .DEPT_TITLE, R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, MAX(A.APPT_DATE)
AS LAST_APPT
FROM ORACARE.K_REFLREG R, ORACARE.K_CPIREG C, ORACARE.K_DEPTLIST D, ORACARE.K_PROFREG P, ORACARE.K_APPTREG A
WHERE R.PT_CODE = C.PT_CODE AND R.DEPT_CODE = D .DEPT_CODE AND R.HOSP_CODE = D .HOSP_CODE AND
R.CONS_MD_CODE = P.MPROF_CODE AND R.EVENT_NO = A.EVENT_NO (+) AND (R.ANNLAPPT_DATE < :PARAM1) AND
a.xtend_ind = 'Y'
GROUP BY R.REFL_DATE, R.EVENT_NO, C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, P.SURNAME, R.DEPT_CODE,
D .DEPT_TITLE, R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE
ORDER BY r.event_no) sub
WHERE ap.event_no (+) = sub.event_no AND sub.last_appt = ap.appt_date (+) AND xtend_ind = 'Y'
ORDER BY sub.annlappt_date
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 04/12/2007 : 16:06:56
|
What are these command?
:Param1
(+)
Have you just copied a query from ORACLE directly to SQL Server?
Peter Larsson
Helsingborg, Sweden |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 04/12/2007 : 16:17:53
|
Or are you still using PL-SQL?
SELECT sub.*,
ap.apat_code
FROM k_apptreg AS ap
INNER JOIN (
SELECT r.REFL_DATE,
r.EVENT_NO,
c.PREFERRED_NAME,
c.SURNAME,
r.ANNLAPPT_DATE,
r.CLNP_CODE,
p.SURNAME AS CLINICIAN,
r.DEPT_CODE,
d.DEPT_TITLE,
r.PT_CODE,
r.REFLREAS_DESC,
r.HOSP_CODE,
r.REFP_CODE,
MAX(a.APPT_DATE) AS LAST_APPT
FROM ORACARE.K_REFLREG AS r
INNER JOIN ORACARE.K_CPIREG AS c ON c.PT_CODE = r.PT_CODE
INNER JOIN ORACARE.K_DEPTLIST AS d ON d.DEPT_CODE = r.DEPT_CODE AND d.HOSP_CODE = r.HOSP_CODE
INNER JOIN ORACARE.K_PROFREG AS p ON p.MPROF_CODE = r.CONS_MD_CODE
INNER JOIN ORACARE.K_APPTREG AS a ON a.EVENT_NO = r.EVENT_NO AND a.XTEND_IND = 'Y'
WHERE r.ANNLAPPT_DATE < @PARAM1
GROUP BY r.REFL_DATE,
r.EVENT_NO,
c.PREFERRED_NAME,
c.SURNAME,
r.ANNLAPPT_DATE,
r.CLNP_CODE,
p.SURNAME,
r.DEPT_CODE,
d.DEPT_TITLE,
r.PT_CODE,
r.REFLREAS_DESC,
r.HOSP_CODE,
r.REFP_CODE
) AS sub ON sub.event_no = ap.event_no AND sub.last_appt = ap.appt_date AND xtend_ind = 'Y'
ORDER BY sub.annlappt_date
Peter Larsson
Helsingborg, Sweden |
|
|
| |
Topic |
|
|
|
error in group by clause...
