-2

I have a decimal column that i need to to run a wildcard query on in a case statement but the wildcards will need a between clause that is between one wildcard to another e.g

case when pct between '999.0%' and '700.0%' then 'Top percentile'
how do I write this please

Spinx
  • 1
  • 5
  • 2
    What do you need that `between 700 and 999` does not provide? – Alex K. Dec 21 '17 at 14:22
  • I have done this as I have arrange of values with different decimal places, but its not working even when I put the % mark to wildcard – Spinx Dec 21 '17 at 14:32
  • what have you done so far? Could you provide your code, sample data and actual/expected output? – hering Dec 21 '17 at 14:35
  • SELECT ITEM , CASE WHEN pct > = 1000 THEN '1000% ' WHEN pct BETWEEN '999%' and '800%' then '999 - 800% ' – Spinx Dec 21 '17 at 14:37
  • table looks like this – Spinx Dec 21 '17 at 14:38
  • 792.241379310345 692.866666666667 293.75 269 245.9375 197.972972972973 179.782608695652 170 169.711331322706 163.545816733068 159.615384615385 153.213166144201 – Spinx Dec 21 '17 at 14:38
  • 1
    [Edit] your post to include such essential info. Don't post it in unformatted comments that may be deleted at any time. And clarify why you think you need wildcards, i.e. what results you get that seem incorrect. There is no clear reason to require wildcards here whatsoever. – underscore_d Dec 21 '17 at 14:44

1 Answers1

1

Since pct is decimal then you cannot and you don't need to use the wildcard with it. Just remove the wild cards and the commas ' ':

SELECT ITEM 
  , CASE WHEN pct > = 1000 THEN '1000% ' 
         WHEN pct BETWEEN 800 and 999 then '800 - 999% ' END
FROM table

Also 800 should come first instead of 999 in between. You need also ELSE for other values less than 800.

  • @Spinx - You're welcome any time, please accept the answer if you find it useful . –  Dec 21 '17 at 15:27