CODEWITHSUNDEEP
sqlstringpostgresqlcastingcase
JK1993
JK1993

Reputation: 138

Cannot figure out the issue with this SQL CASE

CASE
WHEN CAST(a.category AS VARCHAR) like '%jumpsuits%'   THEN CAST(a.google_product_category AS VARCHAR) = 'Apparel & Accessories > Clothing > One-Pieces > Jumpsuits & Rompers'
WHEN CAST(a.category AS VARCHAR) like '%knitwear%'    THEN CAST(a.google_product_category AS VARCHAR) = 'Apparel & Accessories > Clothing > Dresses'
WHEN CAST(a.category AS VARCHAR) like '%lounge wear%' THEN CAST(a.google_product_category AS VARCHAR) = 'Apparel & Accessories > Clothing > Shirts & Tops'
ELSE CAST(a.google_product_category AS VARCHAR) END as google_product_category

The issue i am facing is: "SYNTAX_ERROR: line 7:6: All CASE results must be the same type: boolean"

I have also joined the table in a previous sub query casting it as a varchar, so there shouldn't be any issues with this.

Anyone have any ideas?

For context i am trying to populate a field that would return a blank where the category is like my "likes" anything else i just want it to remain as it is.

Many thanks

Upvotes: 1

Views: 789

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

Your CASE has expressions returning different data types, which is not allowed. If you're simply trying to update the google_product_category based on the category column, try something like this:

Data sample

CREATE TEMPORARY TABLE t (category TEXT, google_product_category TEXT);
INSERT INTO t VALUES ('Jumpsuit'),('kniTweaR'),('lounge WEAR');

Query

SELECT category,
  CASE 
    WHEN category ILIKE '%jumpsuit%' 
      THEN 'Apparel & Accessories > Clothing > One-Pieces > Jumpsuits & Rompers'
    WHEN category ILIKE '%knitwear%' 
      THEN 'Apparel & Accessories > Clothing > Dresses'
    WHEN category ILIKE '%lounge wear%' 
      THEN 'Apparel & Accessories > Clothing > Shirts & Tops'
  END
FROM t;

  category   |                                case                                 
-------------+---------------------------------------------------------------------
 Jumpsuit    | Apparel & Accessories > Clothing > One-Pieces > Jumpsuits & Rompers
 kniTweaR    | Apparel & Accessories > Clothing > Dresses
 lounge WEAR | Apparel & Accessories > Clothing > Shirts & Tops
(3 Zeilen)

Upvotes: 1

Related Questions