I am looking to query for all open and won opps, but i dont want to use the stagename as there multiple closed-Lost stages and there could couple more added later. I dont want to change the query everytime there is a new stage added in.

WHERE stagename not IN('Closed-Lost','Closed-Lost-NoBudget')

is there a way to get the records without explicitly specifying stagename?


If you have the stage properly linked to the Won-Lost, maybe by...

SELECT ID FROM Opportunity WHERE IsWon = true OR IsClosed = false
  • Adding IsWon purely makes this answer less efficient.
    – Adrian Larson
    Nov 26 '17 at 16:51
  • @Adrian Larson, can you elaborate a bit more your thought and reasons? (So everybody can benefit from it in the future) Dec 16 '17 at 12:22

How about selecting all, that don't contain "Closed" in their stage name:

WHERE stagename LIKE '%Won%' OR (NOT stagename LIKE '%Closed%')
  • This would include closed/won as well. i need to have closedwon and exclude only closed lost ones
    – Prady
    Nov 7 '17 at 18:12
  • @Prady updated my answer to include all wons. You could also write 'Closed-Lost%' if they all have the same prefix
    – Basti
    Nov 7 '17 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.