January 2, 2007 at 2:44 pm
this is the query that gives me the error msg.
select title,firstname,surname,required_date,address1,birth_date,request,home_phone,req_id,
user_name,event_number,acc_number,nhi , count(*) as total from vrequest
group by title
group by surname
group by required_date
group by address1
group by birth_date
group by request
group by home_phone
group by req_id
group by user_name
group by event_number
group by acc_number
group by nhi
January 2, 2007 at 2:58 pm
You're missing the firstname in your group by clause.
January 2, 2007 at 3:06 pm
sorry that was paste error. firstname is in group by still get error msg
select
title,firstname,surname,required_date,address1,birth_date,request,home_phone,req_id,
user_name
,event_number,acc_number,nhi , count(*) as total from vrequest
group
by title
group
by firstname
group
by surname
group
by required_date
group
by address1
group
by birth_date
group
by request
group
by home_phone
group
by req_id
group
by user_name
group
by event_number
group
by acc_number
group
by nhi
January 2, 2007 at 3:13 pm
try to run it without the count(*) at the end
January 2, 2007 at 3:19 pm
Still get the same error
January 2, 2007 at 3:36 pm
Sorry Robert,
first day back since the holidays. Took me a while to register :-).
You only need one group by.
do this: group by
title,
firstname,
surname,
required_date,
address1,
birth_date,
request,
home_phone,
req_id,
user_name,
event_number,
acc_number,
nhi
January 2, 2007 at 3:41 pm
Thanks, tried that...
select title,firstname,surname,required_date,address1,birth_date,request,home_phone,req_id,
user_name,event_number,acc_number,nhi, count(*) as total from vrequest
group by title,firstname,surname,required_date,address1,birth_date,request,home_phone,req_id,user_name,event_number,acc_number,nhi
and got the following error msg..
Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
January 2, 2007 at 3:49 pm
Which one of the columns in the query has any of those data types?
January 2, 2007 at 4:00 pm
None have, apart from the dates which are of datatype date all other fields are varchar.
this query runs on sql server 2005 to a linked table on an oracle instance.
January 2, 2007 at 4:17 pm
Must be an Oracle error then. This query should work in SQL 2000/2005. I have never worked with Oracle databases before. If I find anything, I'll let you know. If you find a solution, please post it. Now I'm curious.
January 2, 2007 at 8:50 pm
this works, have to cast request as it is text datatype.
select title,firstname,surname,required_date,address1,birth_date, cast(request as varchar(200)) ,home_phone,req_id,
user_name,event_number,acc_number,nhi, count(*) as total from vrequest
where req_id = '324'
group by title,firstname,surname,required_date,address1,birth_date,cast(request as varchar(200)),
home_phone,req_id,user_name,event_number,acc_number,nhi
January 5, 2007 at 4:11 pm
That is correct syntax w/ Oracle too.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply