Fixed a few things still having a GROUP error

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

Fixed a few things still having a GROUP error

Author

Topic

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 11:21:15

Hello,

Here is the code as it now stands:


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, RES_NAME, ADDRESS, JOB, TYP, NUM, OPR, sum(QTYW) QTY, sum(NCommissionAMT) CommissionAUT
FROM
(select     ir_tech_ojb TECHN
,           substring (compl_cde_ojb, 1, 3) RESCODE
,      compl_dte_ojb COMPLET
,      CodeDes DESCRIPTION
,      res_name_sbb RES_NAME
,      ADDR1_HSE ADDRESS
,      job_typ_ojb JOB
,      job_class_ojb TYP
,      order_no_ocr NUMB
,      ls_chg_op_id_ocr OPR
,      CommissionAMT NCommissionAMT
,      COUNT(DISTINCT order_no_ocr) QTYW      
from Parser.dbo.Parser_ocr
	Inner Join Parser.dbo.Parser_ojb on order_no_ocr = order_no_ojb
	Inner JOIN Parser.dbo.RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE 
	Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
	Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 1, 3) = CODE 
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 1, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
,      substring (compl_cde_ojb, 4, 3) RESCODE
,      compl_dte_ojb COMPLET
,      CodeDes DESCRIPTION
,      res_name_sbb RES_NAME
,      ADDR1_HSE ADDRESS
,      job_typ_ojb JOB
,      job_class_ojb TYP
,      order_no_ocr NUMB
,      ls_chg_op_id_ocr OPR
,      CommissionAMT NCommissionAMT
,      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.dbo.sbb_base on hse_key_ocr = hse_key_sbb
	Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 4, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 4, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
,      substring (compl_cde_ojb, 7, 3) RESCODE
,      compl_dte_ojb COMPLET
,      CodeDes DESCRIPTION
,      res_name_sbb RES_NAME
,      ADDR1_HSE ADDRESS
,      job_typ_ojb JOB
,      job_class_ojb TYP
,      order_no_ocr NUMB
,      ls_chg_op_id_ocr OPR
,      CommissionAMT NCommissionAMT
,      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.dbo.sbb_base on hse_key_ocr = hse_key_sbb
	Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 7, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 7, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
,      substring (compl_cde_ojb, 10, 3) RESCODE
,      compl_dte_ojb COMPLET
,      CodeDes DESCRIPTION
,      res_name_sbb RES_NAME
,      ADDR1_HSE ADDRESS
,      job_typ_ojb JOB
,      job_class_ojb TYP
,      order_no_ocr NUMB
,      ls_chg_op_id_ocr OPR
,      CommissionAMT NCommissionAMT 
,      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.dbo.sbb_base on hse_key_ocr = hse_key_sbb
	Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 10, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 10, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
,      substring (compl_cde_ojb, 13, 3) RESCODE
,      compl_dte_ojb COMPLET
,      CodeDes DESCRIPTION
,      res_name_sbb RES_NAME
,      ADDR1_HSE ADDRESS
,      job_typ_ojb JOB
,      job_class_ojb TYP
,      order_no_ocr NUMB
,      ls_chg_op_id_ocr OPR
,      CommissionAMT NCommissionAMT 
,      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.dbo.sbb_base on hse_key_ocr = hse_key_sbb
	Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 13, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 13, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
,      substring (compl_cde_ojb, 16, 3) RESCODE
,      compl_dte_ojb COMPLET
,      CodeDes DESCRIPTION
,      res_name_sbb RES_NAME
,      ADDR1_HSE ADDRESS
,      job_typ_ojb JOB
,      job_class_ojb TYP
,      order_no_ocr NUMB
,      ls_chg_op_id_ocr OPR
,      CommissionAMT NCommissionAMT 
,      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.dbo.sbb_base on hse_key_ocr = hse_key_sbb
	Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 16, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 16, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT)
group by rollup(TECHN, RESCODE,(COMPLET, DESCRIPTION, RES_NAME, ADDRESS, JOB, TYP, NUMB, OPR))
order by 1, 2,3, 5;

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

Could the error message be in the INNER JOIN?

What do I need to check for next.

Thanks for eveyrone's help so far.

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 11:26:21

Duplicate post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79234
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79264

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 11:28:52

I have tried to help you with this before.

Change

group by rollup(TECHN, RESCODE,(COMPLET, DESCRIPTION, RES_NAME, ADDRESS, JOB, TYP, NUMB, OPR))
order by 1, 2,3, 5;

to

group by TECHN, RESCODE, COMPLET, DESCRIPTION, RES_NAME, ADDRESS, JOB, TYP, NUMB, OPR
with rollup
order by 1, 2,3, 5;

if you insist having the rollup operator in the query.

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 11:55:00

Hello Peso,

I changed the code per your suggestion this is the new error message:

group by TECHN, RESCODE, COMPLET, DESCRIPTION, RES_NAME, ADDRESS, JOB, TYP, NUMB, OPR
with rollup
order by 1, 2,3, 5

Thanks,

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 11:56:39

But you stil have the same erraneous syntax in the last UNION clause!?

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 12:01:32

Hell Peso,

To answer your question yes.

What do I need to change.

thanks,

Kurt

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 12:16:14

Now I have tried to run just part of the code:


select ir_tech_ojb TECHN
,      substring (compl_cde_ojb, 16, 3) RESCODE
,      compl_dte_ojb COMPLET
,      CodeDes DESCRIPTION
,      res_name_sbb RES_NAME
,      ADDR1_HSE ADDRESS
,      job_typ_ojb JOB
,      job_class_ojb TYP
,      order_no_ocr NUMB
,      ls_chg_op_id_ocr OPR
,      CommissionAMT NCommissionAMT 
,      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.dbo.sbb_base on hse_key_ocr = hse_key_sbb
	Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 16, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 16, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT)
group by TECHN, RESCODE, COMPLET, DESCRIPTION, RES_NAME, ADDRESS, JOB, TYP, NUMB, OPR
with rollup
order by 1, 2,3, 5

And I still get the following error message:

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.
Msg 319, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Any ideas on what I can do next?

Thanks,

kurt

Kristen
Test

22859 Posts

Posted - 2007-02-16 : 12:45:45

That "snippet" of code should end


CommissionAMT     )
Here --------^

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-16 : 13:45:44

Is each part of the UNION exactly the same except for the

substring (compl_cde_ojb, 1, 3)

part ??? If so, then the whole thing can be written about 1/5 the size !

You query really needs a huge rewrite, it can be shorten quite a bit, but the way you have written it, it is impossible for us to know how to help you. You have tons of columns listed throughout without any reference as to what table they come from, so we have no way of knowing where these columns come from, making it is very hard to guess as to what you are doing. You should always, always prefix your columns with what table they come from -- not only to avoid errors, but to be sure that you (and others) understand exactly where your data comes from when joining multiple tables. makes sense, right?

Anyway, a quick guess is that all of your UNION ALL parts can be re-written something like this:


select     ir_tech_ojb TECHN
,           substring (compl_cde_ojb, positions.position, 3) RESCODE
,      compl_dte_ojb COMPLET
,      CodeDes DESCRIPTION
,      res_name_sbb RES_NAME
,      ADDR1_HSE ADDRESS
,      job_typ_ojb JOB
,      job_class_ojb TYP
,      order_no_ocr NUMB
,      ls_chg_op_id_ocr OPR
,      CommissionAMT NCommissionAMT
,      COUNT(DISTINCT order_no_ocr) QTYW      
from Parser.dbo.Parser_ocr
	Inner Join Parser.dbo.Parser_ojb on order_no_ocr = order_no_ojb
	Inner JOIN Parser.dbo.RGV_Codes on substring (compl_cde_ojb, positions.position, 3) = CODE 
	Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
	Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
	cross Join 
           (
		select 1 as position union all 
		select 4 union all 
		select 7 union all 
		select 10 union all 
		select 14) 
           positions
where substring (compl_cde_ojb, 1, 3) = CODE  -- redundant to the JOIN condition
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, positions.position, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT

We CROSS JOIN to a list of numbers that tell us which starting position to use in the substring, which has the effect of doing all of your UNIONS only much easier and shorter. Even better is to store those numbers into a table. Even BETTER is to normalize your data so that you don't have multiple values all stuffed into 1 column in a single row.

Also, from you long GROUP BY list, it is clear that you are not grouping your SELECT at the correct level; you should probably be joining to a derived table that does the grouping. see: http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx for more on better ways to group your SELECT statements.

- Jeff
http://weblogs.sqlteam.com/JeffS

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 13:54:00

Hello Dr. Cross Join and everyone,

Thanks we are almost there!

I tried the code like this:


select     ir_tech_ojb TECHN
,           substring (compl_cde_ojb, positions.position, 3) RESCODE
,      compl_dte_ojb COMPLET
,      CodeDes DESCRIPTION
,      res_name_sbb RES_NAME
,      ADDR1_HSE ADDRESS
,      job_typ_ojb JOB
,      job_class_ojb TYP
,      order_no_ocr NUMB
,      ls_chg_op_id_ocr OPR
,      CommissionAMT NCommissionAMT
,      COUNT(DISTINCT order_no_ocr) QTYW      
from Parser.dbo.Parser_ocr
	Inner Join Parser.dbo.Parser_ojb on order_no_ocr = order_no_ojb
	Inner JOIN Parser.dbo.RGV_Codes on substring (compl_cde_ojb, positions.position, 3) = CODE 
	Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
	Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
	cross Join 
           (
		select 1 as position union all 
		select 4 union all 
		select 7 union all 
		select 10 union all 
		select 14) 
           positions
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, positions.position, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT

But now I get the error message:

Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'and'.

Thanks I think we are almost there.

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 14:01:50

1) Shouldn't {1,4,7,10,14} be {1,4,7,10,13,16} ???
2) After the "positions" alias, you need a WHERE keyword before all comparisons.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 14:04:45

[code]select ir_tech_ojb as TECHN,
substring(compl_cde_ojb, positions.position, 3) as RESCODE,
compl_dte_ojb as COMPLET,
CodeDes as DESCRIPTION,
res_name_sbb as RES_NAME,
ADDR1_HSE as ADDRESS,
job_typ_ojb as JOB,
job_class_ojb as TYP,
order_no_ocr as NUMB,
ls_chg_op_id_ocr as OPR,
CommissionAMT as NCommissionAMT,
COUNT(DISTINCT order_no_ocr) as QTYW
from Parser.dbo.Parser_ocr
Inner Join Parser.dbo.Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN Parser.dbo.RGV_Codes on substring (compl_cde_ojb, positions.position, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
cross Join (
select 1 as position union all
select 4 union all
select 7 union all
select 10 union all
select 13 union all
select 16
) as positions
WHERE ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb,
substring(compl_cde_ojb, positions.position, 3),
compl_dte_ojb,
CodeDes,
res_name_sbb,
ADDR1_HSE,
job_typ_ojb,
job_class_ojb,
order_no_ocr,
ls_chg_op_id_ocr,
CommissionAMT[/code]

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 14:08:11

Hello Peso,

Thanks tried running it.

Here is the new error message:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "positions.position" could not be bound.

As always a big thanks for your help.

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 14:27:14

Maybe Positions.Position is a reserved word? Enclose them and some other with brackets.

select		ir_tech_ojb as TECHN,
		substring(compl_cde_ojb, [positions].[position], 3) as RESCODE,
		compl_dte_ojb as COMPLET,
		CodeDes as [DESCRIPTION],
		res_name_sbb as RES_NAME,
		ADDR1_HSE as [ADDRESS],
		job_typ_ojb as JOB,
		job_class_ojb as TYP,
		order_no_ocr as NUMB,
		ls_chg_op_id_ocr as OPR,
		CommissionAMT as NCommissionAMT,
		COUNT(DISTINCT order_no_ocr) as QTYW      
from		Parser.dbo.Parser_ocr
Inner Join	Parser.dbo.Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN	Parser.dbo.RGV_Codes on substring (compl_cde_ojb, [positions].[position], 3) = CODE 
Inner JOIN	csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN	csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
cross Join	(
			select 1 as [position] union all 
			select 4 union all 
			select 7 union all 
			select 10 union all 
			select 13 union all 
			select 16
		) as [positions]
WHERE		ir_tech_ojb between 950 and 999
		and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
		and prin_ocr = 8600
GROUP BY	ir_tech_ojb,
		substring(compl_cde_ojb, [positions].[position], 3),
		compl_dte_ojb,
		CodeDes,
		res_name_sbb,
		ADDR1_HSE,
		job_typ_ojb,
		job_class_ojb,
		order_no_ocr,
		ls_chg_op_id_ocr,
		CommissionAMT

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 14:54:40

Hello Peso,

Still the same error message.

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "positions.position" could not be bound.

Is there something that might not be turned on somewhere else or identified in SQL Server software that I am not aware of?

Thanks,

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 15:09:21

Replace all [Positions] with [p], and all [Position] with [pstn] so we get rid of all the reserved words.

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 15:13:48

Hello again Peso,

Thanks kindly for your sugggestions replaced and now get the same type of message:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "p.pstn" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "p.pstn" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "p.pstn" could not be bound.

What should I now check for. Why isn't this binding?

Thanks,

Kurt

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 15:49:34

Hello,

Did some looking around in my database tables.

In this statement:

select ir_tech_ojb as TECHN,

The ir_tech_ojb is in a table called dbo.NRT_OJB_JOBS.

Is this why I am getting these errors. Where do I include it like you would a FROM statement?

Thanks,

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 16:06:27

Post the COMPLETE query you have right now.

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 16:12:27

Hello Peso,

Per your request here is the complete query:


select		ir_tech_ojb as TECHN,
		substring(compl_cde_ojb, [p].[pstn], 3) as RESCODE,
		compl_dte_ojb as COMPLET,
		CodeDes as [DESCRIPTION],
		res_name_sbb as RES_NAME,
		ADDR1_HSE as [ADDRESS],
		job_typ_ojb as JOB,
		job_class_ojb as TYP,
		order_no_ocr as NUMB,
		ls_chg_op_id_ocr as OPR,
		CommissionAMT as NCommissionAMT,
		COUNT(DISTINCT order_no_ocr) as QTYW      
from		Parser.dbo.Parser_ocr
Inner Join	Parser.dbo.Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN	Parser.dbo.RGV_Codes on substring (compl_cde_ojb, [p].[pstn], 3) = CODE 
Inner JOIN	csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN	csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
cross Join	(
			select 1 as [p] union all 
			select 4 union all 
			select 7 union all 
			select 10 union all 
			select 13 union all 
			select 16
		) as [pstn]
WHERE		ir_tech_ojb between 950 and 999
		and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
		and prin_ocr = 8600
GROUP BY	ir_tech_ojb,
		substring(compl_cde_ojb, [p].[pstn], 3),
		compl_dte_ojb,
		CodeDes,
		res_name_sbb,
		ADDR1_HSE,
		job_typ_ojb,
		job_class_ojb,
		order_no_ocr,
		ls_chg_op_id_ocr,
		CommissionAMT

thanks,

Kurt

kdnichols
Posting Yak Master

232 Posts

Posted - 2007-02-16 : 16:29:58

Hello Peso,

The name of the table where the ir_tech_ojb

is

CSG_NRT.dbo.NRT_OJB_JOBS

Do we need to identify this in a FROM clause somewhere?

Kurt

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources