CODEWITHSUNDEEP
ruby-on-railspostgresql
amtest
amtest

Reputation: 700

empty or null field check with postgres query in rails

I am trying to check two condition with case in posgtres query, in my table one field may have two values either will be empty or will be single quote('') so i tried this query but it didn't execute

CASE WHEN (items.item_code is NULL OR items.item_code = '') THEN
items.name::text ELSE items.item_code::text END as item_code

Error like:

PG::Error: ERROR: syntax error at or near ")" LINE 1: ...HEN (items.item_code is NULL OR items.item_code = ) THEN ite..

Upvotes: 1

Views: 2441

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51446

you have problem with quotes in ruby, not in sql statement. try changing to:

CASE 
  WHEN (coalesce(length(items.item_code),0) + length(items.item_code)) < 1
  THEN items.name::text ELSE items.item_code::text 
END as item_code

the code above does same condition check as yours, buta voids using quotes. I suppose your could should be smth like:

CASE WHEN (items.item_code is NULL OR items.item_code = \'\') THEN
items.name::text ELSE items.item_code::text END as item_code

Upvotes: 2

Related Questions