skip to Main Content

I want to set variable either true or false based on Status column values, if any value is coming Cancel in Status column then I want to set variable as false OR want to set as true.

I have query like this :

Select Status from tablename where Id = 77 and ThreadId IN (30, 40, 50, 60, 69, 70, 80, 90, 100, 110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210, 220, 230, 231, 232, 233, 234, 240, 241, 242, 243, 244, 250, 260, 270, 280, 290, 300, 310, 320, 330, 340, 350, 360, 370, 380, 390, 400)

So now this query is returning all status for this company for this all threads. It is returning values either Cancel OR Accept.

Accept
Accept
Accept
Accept
Cancel
Accept
Accept

What I want to do is, if any value is Cancel coming then I want to set variable as false in this query so is it possible ?

3

Answers


  1. you can use case statement :

    select CASE WHEN Status='Accept' THEN 'True'
                   ELSE 'False' END AS status_boolean
    from tablename where Id = 77 and ThreadId IN (30, 40, 50, 60, 69, 70, 80, 90, 100, 110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210, 220, 230, 231, 232, 233, 234, 240, 241, 242, 243, 244, 250, 260, 270, 280, 290, 300, 310, 320, 330, 340, 350, 360, 370, 380, 390, 400)
    
    Login or Signup to reply.
  2. You may use sql case expression

    Select case when Status='Cancel' then 'false' end as Status from tablename where Id = 77 and ThreadId IN (30, 40, 50, 60, 69, 70, 80, 90, 100, 110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210, 220, 230, 231, 232, 233, 234, 240, 241, 242, 243, 244, 250, 260, 270, 280, 290, 300, 310, 320, 330, 340, 350, 360, 370, 380, 390, 400)
    
    Login or Signup to reply.
  3. Try the following:

    select case when max(Status) = 'Accept' then 'true' else 'false' end as flag
    from tablename 
    where Id = 77 and 
      ThreadId IN (30, 40, 50) -- list other ids
    

    When there is no ‘Cancel’ value then all the status values would be ‘Accecpt’, the max function will return ‘Accept’, and the case expression evaluates to true.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search