1

select id from records where (mean_logratio = -4.81)

-4.810215473175049 value is present in the table which supposed to be fetched

-4.810215473175049 only exact value is accepted not absolute value, in search query for = and != condition

for absolute value like -4.81, not getting expected results

Apurva Dhok
  • 11
  • 4

1 Answers1

0

You can go with either approach:

  1. If you want to compare after rounding off upto two decimal place.
select distinct(workflowid) 
from cyto_records r join cyto_record_results rr on (r.recordid = rr.recordid) 
where (round(rr.mean_logratio::numeric,2) = -4.81)
  1. If you want to truncate upto two decimal and compare then use below mentioned query:
select distinct(workflowid) 
from cyto_records r join cyto_record_results rr on (r.recordid = rr.recordid)
where (trunc(rr.mean_logratio::numeric,2) = -4.81)

In case of data type mismatch error, you may need to cast you data.

Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32
  • ERROR: function round(double precision, integer) does not exist LINE 3: where (round(rr.mean_logratio,2) = -4.81) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 116 – Apurva Dhok Jan 03 '23 at 05:54
  • ERROR: function trunc(double precision, integer) does not exist LINE 3: where (trunc(rr.mean_logratio,2) = -4.81) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 115 – Apurva Dhok Jan 03 '23 at 05:55
  • I'm using Postgresql – Apurva Dhok Jan 03 '23 at 05:55
  • I have clearly mention in the last that you may have to cast your data. you need to cast your rr.mean_logratio to numeric or decimal. Please try the updated answer. – Akhilesh Mishra Jan 03 '23 at 05:59
  • Thank you so much I got the expected result – Apurva Dhok Jan 03 '23 at 06:57
  • You are welcome. Its good practice on SO to accept the answer if it resolve your problem and upvote also – Akhilesh Mishra Jan 03 '23 at 14:42