April 16, 2009 at 10:02 am
I have the following select statement that is throwing an error and I am not sure how to write this so that I am able to put a string into a column that is int datatype.
Select
CASE WHEN (select count(distinct c_number)
FROM t_order_detail_breakdown odb (nolock), t_pick_detail pkd (nolock)
WHERE odb.item_number=pkd.item_number
AND odb.order_number=pkd.order_number
AND odb.wh_id=pkd.wh_id) > 1
THEN 'Multiple'
ELSE
(select TOP 1 odb.set_group from t_order_detail_breakdown odb (nolock), t_pick_detail pkd (nolock)
where odb.item_number = pkd.item_number
And odb.order_number = pkd.order_number
And odb.wh_id = pkd.wh_id
Order by odb.set_group)
END AS set_group
This is used to populate a column in a report that would show the string 'multiple' otherwise just show the information from the table. I have tried different ways of using the CONVERT and CAST, but have not been able to get anything to work.
April 16, 2009 at 1:10 pm
Hi
Try to convert the odb.set_group to VARCHAR within your sub-select:
SELECT
CASE WHEN (select count(distinct c_number)
FROM t_order_detail_breakdown odb (nolock), t_pick_detail pkd (nolock)
WHERE odb.item_number=pkd.item_number
AND odb.order_number=pkd.order_number
AND odb.wh_id=pkd.wh_id) > 1
THEN 'Multiple'
ELSE
(select TOP 1 CONVERT(VARCHAR(10), odb.set_group)
from t_order_detail_breakdown odb (nolock), t_pick_detail pkd (nolock)
where odb.item_number = pkd.item_number
And odb.order_number = pkd.order_number
And odb.wh_id = pkd.wh_id
Order by odb.set_group)
END AS set_group
Greets
Flo
April 16, 2009 at 1:21 pm
I tried that and I still get the same error message.
April 16, 2009 at 1:32 pm
Could you please post the complete error message?
April 16, 2009 at 2:02 pm
I got the error message to quit. Your statment worked. I forgot there was a union and had to change that part also.
But, now my problem is I am not getting the results that I want. It is just putting the word multiple in every row and it should not.
April 16, 2009 at 2:19 pm
Thank you for your help!
The Convert statement worked and I was able to get the results I was looking for.
April 16, 2009 at 2:57 pm
Glad that I could help!
Greets
Flo
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply