Postgres operator logic not working?

Question

Can't for the life of me figure out why im getting this row back

 order_id | job_name | memname |     status     |  state
----------+----------+---------+----------------+----------
 0e4ba    | pwd_test |         | Wait Condition | Deleted

running this query

select order_id, job_name, memname, status, state 
from a150708002_ajob 
where order_time < '20150601' 
  and status != 'Ended OK' 
   or state != 'Deleted' 
  and order_time < '20150601';

total result:

em800=> select order_id, job_name, memname, status, state from a150708002_ajob where order_time < '20150601' and status != 'Ended OK' or state != 'Deleted' and order_time < '20150601';
 order_id | job_name | memname |     status     |  state
----------+----------+---------+----------------+----------
 0e4ba    | pwd_test |         | Wait Condition | Deleted
 0eevd    | DBPE220A |         | Wait Condition | Held
 0eeve    | SHAW     |         | Wait Condition | Held

Not sure what's going on?

Update:

Tried the below still no success

em800=> select order_id, job_name, memname, status, state from a150708002_ajob where order_time < '20150601' and not (status = 'Ended OK' or state = 'Deleted');
 order_id | job_name | memname |     status     |  state
----------+----------+---------+----------------+----------
 0e4ba    | pwd_test |         | Wait Condition | Deleted
 0eevd    | DBPE220A |         | Wait Condition | Held
 0eeve    | SHAW     |         | Wait Condition | Held
(3 rows)

table schema:

                 Table "public.a150701002_aevt"
        Column        |            Type             | Modifiers
----------------------+-----------------------------+-----------
 evt_no               | integer                     | not null
 evt_time             | timestamp without time zone | not null
 evt_type             | character varying(1)        | not null
 new_value            | integer                     |
 old_value            | integer                     |
 order_id             | character varying(5)        |
 state_digits_new_ffu | character varying(15)       |
 state_digits_old_ffu | character varying(15)       |
Indexes:
    "pk_a150701002_aevt_0" PRIMARY KEY, btree (evt_no)
 task_class               | character varying(3)    |
 ind_cyclic               | character varying(1)    |
 reten_days               | character varying(3)    |
 reten_gen                | character varying(2)    |
 order_table              | character varying(770)  |
 order_lib                | character varying(44)   |
 sticky_ind               | character varying(1)    |
 seq_cnt_added            | integer                 |
 short_ffu                | character varying(12)   |
 dsect_ffu                | character varying(100)  |
 isn_                     | integer                 | not null
 time_ref                 | character varying(1)    |
 time_zone                | character varying(9)    |
 appl_type                | character varying(10)   |
 appl_ver                 | character varying(10)   |
 appl_form                | character varying(30)   |
 cm_ver                   | character varying(10)   |
 state_mask               | character varying(9)    |
 multy_agent              | character varying(1)    |
 schedule_env             | character varying(16)   |
 sys_affinity             | character varying(5)    |
 req_nje_node             | character varying(8)    |
 adjust_cond              | character varying(1)    |
 in_service               | character varying(255)  |
 stat_cal                 | character varying(30)   |
 stat_period              | character varying(1)    |
 instream_jcl             | text                    |
 use_instream_jcl         | character varying(1)    |
 lpar                     | character varying(8)    |
 due_out_daysoffset       | character varying(3)    |
 from_daysoffset          | character varying(3)    |
 to_daysoffset            | character varying(3)    |
 order_time               | character varying(14)   |
 avg_start_time           | character varying(6)    |
 cpu_time                 | integer                 |
 em_stat_cal_ctm          | character varying(20)   |
 em_stat_cal              | character varying(30)   |
 em_stat_period           | character varying(1)    |
 interval_sequence        | character varying(4000) |
 specific_times           | character varying(4000) |
 tolerance                | integer                 |
 cyclic_type              | character varying(1)    |
 current_run              | integer                 |
 elapsed_runtime          | integer                 |
 workloads                | character varying(525)  |
 def_nodegroup            | character varying(50)   |
 nodegroup_set_by         | character varying(1)    |
 failure_rc               | character varying(5)    |
 failure_rc_step          | character varying(8)    |
 failure_rc_procstep      | character varying(8)    |
 highest_rc               | character varying(5)    |
 highest_rc_step          | character varying(8)    |
 highest_rc_procstep      | character varying(8)    |
 highest_rc_memname       | character varying(8)    |
 associated_rbc           | character varying(20)   |
 cm_status                | character varying(32)   |
 depend_service_in        | character varying(1)    |
 depend_service_out       | character varying(1)    |
 em_stat_detail_data      | character varying(1500) |
 prev_odate_rerun_counter | integer                 |
 jobrc                    | character varying(5)    |

in response to Norbert van Nobelen answer

em800=> select order_id, status, state from a150712002_ajob where order_time < '20150615' and (status != 'Ended OK' or state != 'Deleted') and order_time < '20150615';
 order_id |     status     |  state
----------+----------------+----------
 0eevd    | Wait Condition | Deleted
 0eeve    | Wait Condition | Held
(2 rows)

ANSWER:

turns out the logic was fine its just the values inside the cells had a trailing white space

ctmtest1-tctmsv80 [13] python del_jobs_main.py 20150625 150712
select order_id, status, state from a150712002_ajob where order_time < '20150625' and not (status = 'Ended OK' or state = 'Deleted');
' order_id |     status     |  state   '
'----------+----------------+----------'
' 0eeve    | Wait Condition | Deleted '
' 0eevd    | Wait Condition | Deleted '
'(2 rows)'

so i just added a trailing white space character to my query. Thanks guys :)

My deleted answer was wrong for this; I didn't notice that one column was status and one was state. I think you really need to show us the input data (preferably as CREATE TABLE and INSERTs). Take a look at sqlfiddle.com — Craig Ringer
– Craig Ringer, Commented Jul 8, 2015 at 5:12
This table is created by an application. I have added the schema to the original post — Justin S
– Justin S, Commented Jul 8, 2015 at 5:43

Norbert · Accepted Answer · 2015-07-08 04:04:05Z

1

Missing some brackets in the state check:

select order_id, job_name, memname, status, state from a150708002_ajob 
where order_time < '20150601' and 
(status != 'Ended OK' or state != 'Deleted') and order_time < '20150601';

The brackets determine how the OR is evaluated.

answered Jul 8, 2015 at 4:04

Norbert

6,1043 gold badges19 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres operator logic not working?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related