SubQuery Using Max

[jcmv007]

FYI - Using SQL Server 2000, Windows XP SP1. -

I tying to run the following query but I get the following error message:
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'FROM'.
Server: Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'and'.

Any ideas what I might have written wrong?

SELECT 
t1.oprer_artcod as Codigo,
t1.oprer_fecmod as Fecha_Modificacion,
datepart(year, convert(datetime, convert(varchar, t1.oprer_fecmod))) as Ano,
datepart(month, convert(datetime, convert(varchar, t1.oprer_fecmod))) as Mes,
-- Precio Nuevo
	SELECT 	sq1.oprer_prenue
	FROM 	oprer as sq1
	WHERE	convert(char(7), sq1.oprer_artcod)+convert(char(8), sq1.oprer_fecmod)+
		convert(varchar(8), sq1.oprer_hormod)+ convert(char(3), sq1.oprer_locnum)+
		convert(char(1), sq1.oprer_area)= 
						 (SELECT MAX(	convert(char(7), sq.oprer_artcod)+
								convert(char(8), sq.oprer_fecmod)+
								convert(varchar(8), sq1.oprer_hormod)+
								convert(char(3), sq.oprer_locnum)+
								convert(char(1), sq.oprer_area)
							     )
							FROM oprer as sq WHERE sq.oprer_artcod = T1.oprer_artcod and 
							sq.oprer_fecmod = T1.oprer_fecmod)
FROM
oprer as t1

where
oprer_tiptrn = 1 and
oprer_artcod in (select codigo from ##cpvpmo where codigo >1)and
oprer_fecmod between 20030401 and 20030431 and
oprer_area = 3 and
oprer_preant <> oprer_prenue

Group by
t1.oprer_artcod,
t1.oprer_fecmod,
datepart(year, convert(datetime, convert(varchar, t1.oprer_fecmod))),
datepart(month, convert(datetime, convert(varchar, t1.oprer_fecmod))) 
 
Order By
t1.oprer_artcod,
t1.oprer_fecmod

 

[JamesLean]

You need parentheses around your sub-select in your main select list:

...
-- Precio Nuevo
(
  SELECT sq1.oprer_prenue
  ...
)
FROM
oprer as t1
...

--James

 

[SQLBill]

Subselects MUST be in parenthesis:

SELECT column1, (SELECT max(column2) FROM t2) AS [maxc2]
FROM t1

So, try this:

SELECT
t1.oprer_artcod as Codigo,
t1.oprer_fecmod as Fecha_Modificacion,
datepart(year, convert(datetime, convert(varchar, t1.oprer_fecmod))) as Ano,
datepart(month, convert(datetime, convert(varchar, t1.oprer_fecmod))) as Mes,
-- Precio Nuevo
(SELECT sq1.oprer_prenue
FROM oprer as sq1
WHERE convert(char(7), sq1.oprer_artcod)+convert(char(8), sq1.oprer_fecmod)+
convert(varchar(8), sq1.oprer_hormod)+ convert(char(3), sq1.oprer_locnum)+
convert(char(1), sq1.oprer_area)=
(SELECT MAX( convert(char(7), sq.oprer_artcod)+
convert(char(8), sq.oprer_fecmod)+
convert(varchar(8), sq1.oprer_hormod)+
convert(char(3), sq.oprer_locnum)+
convert(char(1), sq.oprer_area)
)
FROM oprer as sq WHERE sq.oprer_artcod = T1.oprer_artcod and
sq.oprer_fecmod = T1.oprer_fecmod))
FROM
oprer as t1

where
oprer_tiptrn = 1 and
oprer_artcod in (select codigo from ##cpvpmo where codigo >1)and
oprer_fecmod between 20030401 and 20030431 and
oprer_area = 3 and
oprer_preant <> oprer_prenue

Group by
t1.oprer_artcod,
t1.oprer_fecmod,
datepart(year, convert(datetime, convert(varchar, t1.oprer_fecmod))),
datepart(month, convert(datetime, convert(varchar, t1.oprer_fecmod)))

Order By
t1.oprer_artcod,
t1.oprer_fecmod


-SQLBill

 

[jcmv007]

Thank you -simple error!

Ok, I fixed it but now I'm getting a the following error message:
Server: Msg 8124, Level 16, State 1, Line 49
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

 

[SQLBill]

Here's the problem:

(SELECT MAX( convert(char(7), sq.oprer_artcod)+
convert(char(8), sq.oprer_fecmod)+
convert(varchar(8), sq1.oprer_hormod)+
convert(char(3), sq.oprer_locnum)+
convert(char(1), sq.oprer_area)


You are using columns from sq and sq1 in the MAX and that's not allowed. Are the table names (sq and sq1) correct or is one or more a typo?

-SQLBill

 

[jcmv007]

SQLBill,

It is a typo. All the columns come from sq.