Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Incorrect syntax on Group

Author  Topic 

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 09:43:27
Hello,

I have the following code:


, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg.hist_sbb_Base on hse_key_ocr = hse_key_sbb
where substring (compl_cde_ojb, 16, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '&low' AND '&high'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 16, 3),compl_dte_ojb,DES, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAUT)
group by rollup(TECHN, RESCODE,(COMPLET, DESCRIPTION, NAME, ADDRESS, JOB, TYP, NUMB, OPR))
order by 1, 2,3, 5;


And I get the following error message:

Incorrect syntax near the keyword 'group'.

What am I doing wrong here?

Thanks,

Kurt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-16 : 09:44:56
can you post the full query ?


KH

Go to Top of Page

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 09:47:12
Hello Khtan,

Sure and thanks for your help.


select TECHN, RESCODE,
(CASE
WHEN TECHN IS NOT NULL AND RESCODE IS NOT NULL AND COMPLET IS NULL THEN 'TOTAL RESCODE'
when TECHN IS NOT NULL AND RESCODE IS NULL THEN 'TOTAL TECH'
ELSE CONVERT(VARCHAR, CAST(COMPLETE AS DATETIME), 105) END) COMPLETE, DESCRIPTION, NAME, ADDRESS, JOB, TYP, NUM, OPR, sum(QTYW) QTY, sum(NCommissionAUT) CommissionAUT
FROM
(select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 1, 3) RESCODE
, compl_dte_ojb COMPLET
, DES DESCRIPTION
, res_name_sbb NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAUT NCommissionAUT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg.hist_sbb_Base on hse_key_ocr = hse_key_sbb
where substring (compl_cde_ojb, 1, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '&low' AND '&high'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 1, 3),compl_dte_ojb,DES, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAUT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 4, 3) RESCODE
, compl_dte_ojb COMPLET
, DES DESCRIPTION
, res_name_sbb NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAUT NCommissionAUT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg.hist_sbb_Base on hse_key_ocr = hse_key_sbb
where substring (compl_cde_ojb, 4, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '&low' AND '&high'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 4, 3),compl_dte_ojb,DES, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAUT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 7, 3) RESCODE
, compl_dte_ojb COMPLET
, DES DESCRIPTION
, res_name_sbb NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAUT NCommissionAUT
, COUNT (DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg.hist_sbb_Base on hse_key_ocr = hse_key_sbb
where substring (compl_cde_ojb, 7, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '&low' AND '&high'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 7, 3),compl_dte_ojb,DES, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAUT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 10, 3) RESCODE
, compl_dte_ojb COMPLET
, DES DESCRIPTION
, res_name_sbb NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAUT NCommissionAUT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg.hist_sbb_Base on hse_key_ocr = hse_key_sbb
where substring (compl_cde_ojb, 10, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '&low' AND '&high'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 10, 3),compl_dte_ojb,DES, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAUT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 13, 3) RESCODE
, compl_dte_ojb COMPLET
, DES DESCRIPTION
, res_name_sbb NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAUT NCommissionAUT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr, Parser_ojb, RGV_Codes, csg.hist_sbb_Base
where substring (compl_cde_ojb, 13, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '&low' AND '&high'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 13, 3),compl_dte_ojb,DES, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAUT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 16, 3) RESCODE
, compl_dte_ojb COMPLET
, DES DESCRIPTION
, res_name_sbb NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAUT NCommissionAUT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg.hist_sbb_Base on hse_key_ocr = hse_key_sbb
where substring (compl_cde_ojb, 16, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '&low' AND '&high'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 16, 3),compl_dte_ojb,DES, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAUT)
group by rollup(TECHN, RESCODE,(COMPLET, DESCRIPTION, NAME, ADDRESS, JOB, TYP, NUMB, OPR))
order by 1, 2,3, 5;


Again a big thanks!

Kurt
Go to Top of Page

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 09:49:07
Hello Khtan,

here is the full error message:

Msg 156, Level 15, State 1, Line 141
Incorrect syntax near the keyword 'group'.

Kurt
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 09:51:01
Continued from here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79234


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-02-16 : 11:56:35
I don't imagine you can GROUP BY [COMPLET] can you (in the outermost GROPU BY)? I would expect you to have to duplicate the CASE statement that [COMPLET] represents

Kristen
Go to Top of Page
   

- Advertisement -