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_sbbwhere substring (compl_cde_ojb, 16, 3) = CODEand ir_tech_ojb between 950 and 999and compl_dte_ojb BETWEEN '&low' AND '&high'and prin_ocr = 8600GROUP 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 |
|
|
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) CommissionAUTFROM(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_sbbwhere substring (compl_cde_ojb, 1, 3) = CODE and ir_tech_ojb between 950 and 999and compl_dte_ojb BETWEEN '&low' AND '&high'and prin_ocr = 8600GROUP 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, CommissionAUTunion allselect 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_sbbwhere substring (compl_cde_ojb, 4, 3) = CODEand ir_tech_ojb between 950 and 999and compl_dte_ojb BETWEEN '&low' AND '&high'and prin_ocr = 8600GROUP 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, CommissionAUTunion allselect 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_sbbwhere substring (compl_cde_ojb, 7, 3) = CODEand ir_tech_ojb between 950 and 999and compl_dte_ojb BETWEEN '&low' AND '&high'and prin_ocr = 8600GROUP 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, CommissionAUTunion allselect 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_sbbwhere substring (compl_cde_ojb, 10, 3) = CODEand ir_tech_ojb between 950 and 999and compl_dte_ojb BETWEEN '&low' AND '&high'and prin_ocr = 8600GROUP 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, CommissionAUTunion allselect 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_Basewhere substring (compl_cde_ojb, 13, 3) = CODEand ir_tech_ojb between 950 and 999and compl_dte_ojb BETWEEN '&low' AND '&high'and prin_ocr = 8600GROUP 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, CommissionAUTunion allselect 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_sbbwhere substring (compl_cde_ojb, 16, 3) = CODEand ir_tech_ojb between 950 and 999and compl_dte_ojb BETWEEN '&low' AND '&high'and prin_ocr = 8600GROUP 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 |
|
|
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 141Incorrect syntax near the keyword 'group'.Kurt |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
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] representsKristen |
|
|
|
|
|