1

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..

amtest
  • 690
  • 1
  • 6
  • 26
  • 1
    "Empty or single quote" , do you mean `NULL` or empty string? because single quote is a whole different thing. – sagi Dec 15 '16 at 08:32
  • 1
    "It didn't execute"; what's the error? – Marth Dec 15 '16 at 08:33
  • Error like PG::Error: ERROR: syntax error at or near ")" LINE 1: ...HEN (items.item_code is NULL OR items.item_code = ) THEN ite... – amtest Dec 15 '16 at 08:35
  • you probabky gave php statement with single quotes - right?.. – Vao Tsun Dec 15 '16 at 08:36
  • same like this http://stackoverflow.com/questions/27800119/postgresql-case-end-with-multiple-conditions – amtest Dec 15 '16 at 08:38
  • Also that field value will be item_code: '' or item_code: – amtest Dec 15 '16 at 08:40
  • The SQL statement (well, the part you've shown us anyway) is syntactically correct. The problem probably lies with how you're calling it from rails. – Marth Dec 15 '16 at 08:41
  • if you change to this: `CASE WHEN coalesce(length(items.item_code),0) + length(items.item_code) THEN items.name::text ELSE items.item_code::text END as item_code` error is gone?.. – Vao Tsun Dec 15 '16 at 08:45
  • I am calling with scope in rails @Marth – amtest Dec 15 '16 at 08:47
  • That didn't work @VaoTsun, got error PG::Error: ERROR: argument of CASE/WHEN must be type boolean, not type integer – amtest Dec 15 '16 at 08:50
  • 1
    then it was quotes in your ruby - check out sql in my answer – Vao Tsun Dec 15 '16 at 08:52

1 Answers1

2

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
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132