Hi All,
I have table like below,based on some PNCODES im assiging some country codes by using case statement.im considering only 5 codes if its satisfies then value assigned if value not satisfied those records should be ignored.But here im getting null values also how to ignore null values in case statement it self.
I have used below query but im getting wrong result.Null values coming as country where ever that conditions failed.is there anyway to stop null values passing in case statement itsel.
select customer_id,case when customer_cd like 'A%' AND customer_cd<>'K0001' THEN 'USA'
WHEN customer_cd='B0001' THEN 'AUSTRALIA'
WHEN customer_cd='C0001' THEN 'NEWZEALAND'
WHEN customer_cd='E0001' THEN 'UK'
WHEN customer_cd='F0001' THEN 'UAE'
END AS COUNTRY
FROM customers
Table:
| customer_id | customer_cd |
| 1 | A0001 |
| 2 | B0001 |
| 3 | C0001 |
| 4 | D0001 |
| 5 | E0001 |
| 6 | F0001 |
| 7 | G0001 |
| 8 | H0001 |
| 9 | I0001 |
| 10 | J0001 |
| 11 | K0001 |
Expected output
| customer_id | country |
| 1 | USA |
| 2 | AUSTRALIA |
| 3 | NEWZEALAND |
| 5 | UK |
| 6 | UAE |
Scripts for reference:
CREATE TABLE customer
( customer_id number(10) NOT NULL,
customer_cd varchar2(50) NOT NULL
);
INSERT INTO customers VALUES ('1', 'A0001');
INSERT INTO customers VALUES ('2', 'B0001');
INSERT INTO customers VALUES ('3', 'C0001');
INSERT INTO customers VALUES ('4', 'D0001');
INSERT INTO customers VALUES ('5', 'E0001');
INSERT INTO customers VALUES ('6', 'F0001');
INSERT INTO customers VALUES ('7', 'G0001');
INSERT INTO customers VALUES ('8', 'H0001');
INSERT INTO customers VALUES ('9', 'I0001');
INSERT INTO customers VALUES ('10', 'J0001');
INSERT INTO customers VALUES ('11', 'K0001');