decodeinformaticainformatica-cloudinformatica-data-integration-hub

How to use decode in IICS when field data contain single quote?

I am new to IICS and my fields that I want to decode looks like below. As you can see the "Outlet_Name" column contains row "NH D'needs". My requirement is to use both columns i.e. Outlet_name and Department and generate an output field call "OD_ID" which consist of integers. So Decode will look like below. However, when I run below decode expression I am receiving validation error saying missing operator. I believe this has to do with the single quote present between D and needs in "NH D'needs". Not sure how to proceed from here ?

Thanks in advance for your time & efforts!

enter image description here

IICS Decode code

DECODE(True,
Outlet_Name = 'NH D'needs' AND Department ='Marketing' , 1,
Outlet_Name = 'NH Mart' AND Department ='Finance' ,2,
Outlet_Name = 'NH Groceries' AND Department ='Inventory' ,3,
Outlet_Name= 'NH Lifestyle' AND Department ='Marketing' , 4,
Outlet_Name = 'NH Digital' AND Department ='Logistics' , 5,
 0
)
Solution

  • Please use CHR(39) to create singe quote. Below should work.

    DECODE(True,
Outlet_Name = 'NH D'|| CHR(39)||'needs' AND Department ='Marketing' , 1,
Outlet_Name = 'NH Mart' AND Department ='Finance' ,2,
Outlet_Name = 'NH Groceries' AND Department ='Inventory' ,3,
Outlet_Name= 'NH Lifestyle' AND Department ='Marketing' , 4,
Outlet_Name = 'NH Digital' AND Department ='Logistics' , 5,
 0
)