SQL Server case when and to group to return value when no rows are returned
I am trying to solve the problem, I cannot find an easy solution. I am trying to write a query that uses the case where the operator returns a value based on some count. In the same request, I also need to return the order as I need to use the data returned by this for another request.
Below is the request:
select CASE WHEN count(name) = 0 THEN '1' ELSE CONVERT(varchar,count(name) + 1) END AS box
, orderid
from order
where len(name) = 7
and orderid = 'XYZ'
group by orderid
The above problem is that, as you can imagine, the string is returned only when a name of length 7 exists in the data. I need this query to return a valid value regardless.
The request should return: 1, XYZ
when a name of length 7 does not exist. The request works when a valid name of length 7 exists.
What would be the best way to solve this problem? Note that the data can contain multiple names that are less than length 7. These names will then change to length 7 after the transaction.
Below is the sample data and the desired results:
NAME ORDERID
ABC 001
XYZ 001
123 001
OOP 002
LMT 002
When I run my request for orderid = '1'
, the request should return 1, 001
. The data will constantly change, so it was originally as shown in the picture above. As transactions occur, the data will change as:
NAME ORDERID
ABCABCABC 001
XYZ 001
123 001
OOP 002
LMT 002
At this point, I don't need to worry about my query above, because it will return a valid string: 2, 001
The problem is that the column name does not match the length, the query returns nothing.
source to share
You may need the following:
select convert(varchar(255),
sum(case when len(name) = 7 and orderid = 'XYZ' then 1 else 0 end) + 1
end) as box,
orderid
from order
group by orderid;
This translates all of the filtering logic into select
.
If you want a single line with counters and order id, which is preferably XYZ, but something else if that is not available:
select convert(varchar(255),
sum(case when len(name) = 7 and orderid = 'XYZ' then 1 else 0 end) + 1
end) as box,
coalesce(max(case when orderid = 'XYZ' then orderid end),
max(orderid)
) as orderid
from order
group by orderid;
source to share
Quite ugly, but with a left join with an inline view of 1 record, each record in B gets XYZ, so we're guaranteed to have at least 1 record; and if the order has names with length 7, we use those values, otherwise the order on the left will be zero and we will concatenate the value from the inline view.
I would normally do this in the UI, if no records are returned, handle this at the presentation level, not here.
But if you must:
UNTESTED (I don't see a logical flaw or syntax yet, but I don't have a testing environment)
SELECT coalesce(CASE WHEN count(a.name) = 0
THEN '1'
ELSE CONVERT(varchar,count(a.name) + 1) END
,b.Box) AS box
, coalesce(a.orderid,b.orderID)
FROM (SELECT '1' Box, 'xyz' OrderID) b
LEFT join [order] A
on 1=1
and len(a.name) = 7
and a.orderid = 'XYZ'
GROUP BY coalesce(a.orderid,b.OrderID)
source to share