I am trying to solve a problem I can't seem to find an easy solution for. I am trying to write a query that uses a case when statement to return a value based on some counts. In the same query, I also need to return an orderid as I need to utilize the data returned by this for another query.
Below is the query:
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 issue above is that as you can imagine, a row is only returned when a a name with length 7 exists in the data. I need this query to return a valid value regardless of that.
The query should return: 1, XYZ when no name of length 7 exists. The query does work when a valid 7 length name exists.
What would be the best way of handling this issue? Note that the data can contain multiple names that are below length 7. These names will then change to length 7 after a transaction takes place.
Below is sample data and desired results:
NAME ORDERID
ABC 001
XYZ 001
123 001
OOP 002
LMT 002
When I run my query for orderid = '1', the query should return 1, 001. The data will be constantly changing so initially it was as pictured above. As transactions take place, data will be changed as:
NAME ORDERID
ABCABCABC 001
XYZ 001
123 001
OOP 002
LMT 002
At this point, I don't have to worry about my query above because it will return a valid row: 2, 001
Issue is when the name column us not the right length, the query returns nothing.