subquery Unsupported data type

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

subquery Unsupported data type

Author

Topic

ymamalis
Starting Member

42 Posts

Posted - 2009-03-10 : 03:00:25

Hi i wrote this one and in the column id_ontotita is having an error. what is wrong? Please help

SELECT MAX(MaxOfid_xp) AS MaxOfMaxOfid_xp, id_ontotita AS id_ont, MAX(hmer_xp) AS MaxOfhmer_xp
FROM (SELECT MAX(id_xp) AS MaxOfid_xp, id_ontotita, hmer_xp
FROM ONTOT_xreopistwseis AS xp
WHERE (hmer_xp <= @dateeos)
GROUP BY id_ontotita, hmer_xp) AS xp1
GROUP BY id_ontotita
ORDER BY MaxOfMaxOfid_xp, id_ont

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 03:07:52

the query looks fine. whats the error you got?

ymamalis
Starting Member

42 Posts

Posted - 2009-03-18 : 04:17:23

i get the <Unsupported Data Type> in the column id_ontotita

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 05:23:15

What datatype is column id_ontotita?

E 12°55'05.63"
N 56°04'39.26"

ymamalis
Starting Member

42 Posts

Posted - 2009-03-18 : 05:40:58

it is integer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 06:14:25

This is your query today.

SELECT		MAX(MaxOfid_xp) AS MaxOfMaxOfid_xp,
		id_ontotita AS id_ont,
		MAX(hmer_xp) AS MaxOfhmer_xp
FROM		(
			SELECT		MAX(id_xp) AS MaxOfid_xp,
					id_ontotita,
					hmer_xp
			FROM		ONTOT_xreopistwseis AS xp
			WHERE		hmer_xp <= @dateeos
			GROUP BY	id_ontotita,
					hmer_xp
		) AS xp1
GROUP BY	id_ontotita
ORDER BY	MaxOfMaxOfid_xp,
		id_ont

The code is bloated. Max is Max no matter where you get the value.
Try this instead

SELECT		MAX(id_xp) AS MaxOfMaxOfid_xp,
		id_ontotita AS id_ont,
		MAX(hmer_xp) AS MaxOfhmer_xp
FROM		ONTOT_xreopistwseis AS xp
WHERE		hmer_xp <= @dateeos
GROUP BY	id_ontotita
ORDER BY	MAX(id_xp),
		id_ontotita

E 12°55'05.63"
N 56°04'39.26"

ymamalis
Starting Member

42 Posts

Posted - 2009-03-18 : 09:59:19

Thanks a lot my friend . thanks !!!!!!!

ymamalis
Starting Member

42 Posts

Posted - 2009-03-23 : 08:11:17

hi i am trying to update the first query with this one but i get an error. what is wrong please?

SELECT id_xp AS MaxOfMaxOfid_xp, dbo.GetOntot(id_ontotita) AS Ontothta, running_sum_xp
FROM ONTOT_xreopistwseis AS xp
WHERE (id_xp IN
(SELECT TOP (100) PERCENT MAX(id_xp) AS MaxOfMaxOfid_xp, id_ontotita AS id_ont, MAX(hmer_xp) AS MaxOfhmer_xp
FROM ONTOT_xreopistwseis AS xp
WHERE (hmer_xp <= @dateeos)
GROUP BY id_ontotita))
ORDER BY MaxOfMaxOfid_xp, Ontothta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-23 : 12:24:31

[code]SELECT id_xp AS MaxOfMaxOfid_xp, dbo.GetOntot(id_ontotita) AS Ontothta, running_sum_xp
FROM ONTOT_xreopistwseis AS xp
INNER JOIN
(SELECT TOP (100) PERCENT MAX(id_xp) AS MaxOfMaxOfid_xp, id_ontotita AS id_ont, MAX(hmer_xp) AS MaxOfhmer_xp
FROM ONTOT_xreopistwseis AS xp
WHERE (hmer_xp <= @dateeos)
GROUP BY id_ontotita)t
ON t.MaxOfMaxOfid_xp = xp.id_xp
ORDER BY t.MaxOfMaxOfid_xp, Ontothta
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources