SQL: display note for specific string combine with column
Hello Guru,
Having below sample data
create table XXTEST (DOCNBR VARCHAR(10),RNBR VARCHAR(20));
insert into XXTEST (DOCNBR,RNBR) values ('149610978','149610978-B1');
insert into XXTEST (DOCNBR,RNBR) values ('149610978','149610978-B2');
insert into XXTEST (DOCNBR,RNBR) values ('149610978','149610978-C1');
insert into XXTEST (DOCNBR,RNBR) values ('149610978','149610978-C2');
insert into XXTEST (DOCNBR,RNBR) values ('149610978','149610978-S1');
What I want is to display 'SPECIAL' if in RNBR I have combination of DOCNBR and -S. I tried below query but no luck. Please advise
SELECT X.DOCNBR,X.RNBR,
CASE WHEN X.DOCNBR ||'-S' = X.RNBR