February 16, 2007 at 12:17 pm
I have a query:
SELECT ReasonText, CreatedWhen, OrderStatusCode
FROM CV3OrderStatusHistory
WHERE (OrderStatusCode = 'perf')
Group by ReasonText, CreatedWhen, OrderStatusCode
Having (reasontext >1)
This is not working, I get an error:
Syntax error converting the varchar value *Auto Activate to a column of data type int.
My data looks like this:
1 Filler Order ID: <977428873070> Filler Facility ID: <ABC>
2 Filler Order ID: <977428874074> Filler Facility ID: <ABC>
3 Filler Order ID: <9774288755555> Filler Facility ID: <ABC>
4 Filler Order ID: <977428873070> Filler Facility ID: <ABC>
I only want the bolded records. I am wondering if it is the format, some characters in different positions or if its because a its a mix of characters and numbers.
Any help would be greatly appreciated.
Thank you
February 16, 2007 at 12:22 pm
your having clause is incorrect, you want the syntax to look like this:
SELECT ReasonText, CreatedWhen, OrderStatusCode
FROM CV3OrderStatusHistory
WHERE (OrderStatusCode = 'perf')
Group by ReasonText, CreatedWhen, OrderStatusCode
Having count(*) >1
February 16, 2007 at 12:28 pm
When I use the format you suggested:
Having count(*) >1
I return no records when there are duplicates there.
Thank you.
February 16, 2007 at 12:45 pm
i assumed that you were looking for duplicate reasontext, createdwhen and orderstatuscode records, which appears to be a false assumption.
without knowing much about the ddl, i am assuming that the filler order id and facility id are also a part of the CV3OrderStatusHistory table.
if that is the case, the following query should get you what you want:
select orig.*
from
(
SELECT filler_order_id, filler_factility_id, ReasonText, CreatedWhen, OrderStatusCode
FROM CV3OrderStatusHistory
WHERE (OrderStatusCode = 'perf') ) orig
inner join (select filler order id, facility id
from CV3OrderStatusHistory
where orderstatuscode = 'perf'
group by filler order id, facility id
having count(*) > 1) dup
on orig.filler_order_id = dup.filler_order_id
and orig.filler_facility_id = dup.filler_facility_id
February 16, 2007 at 1:31 pm
Genius! absolute Genius!
Thank you!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply