Oracle Multiple Field Concatenation (Handling NULL)

I have three fields that I need to concatenate as well as add dashes between them.

My concatenation looks like this:
FACT_CLAIM.Company_dim_id||'-'||FACT_CLAIM.line_number||'-'||FACT_CLAIM.sub_line_code as TEST3
This works fine until the last field, which can be null, the first two cannot be null. So I end  up with data like this:
1. This is good: 589-2-R
2. This is bad: 589-1-

I don't want that dash after the 1 in my second scenario, this is what is desired 589-1.

I've tried CONCAT and a CASE statement and I can't get that last dash in my second scenario to go away.

CONCAT did this, no dash at all:
CONCAT(CONCAT(CONCAT(FACT_CLAIM.COMPANY_DIM_ID,'-'),FACT_CLAIM.line_number),FACT_CLAIM.sub_line_code)
589-3
589-2R

CASE Statement:
(CASE WHEN FACT_CLAIM.sub_line_code is NULL THEN FACT_CLAIM.Company_dim_id||'-'||FACT_CLAIM.line_number
ELSE FACT_CLAIM.Company_dim_id||'-'||FACT_CLAIM.line_number||'-'||FACT_CLAIM.sub_line_code
 END)

Thanks for your help.
SharonBernalAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
The test case:
/*
drop table fact_claim purge;
create table fact_claim(company_dim_id char(1), line_number char(1), sub_line_code char(1));

insert into fact_claim values('a','a','a');
insert into fact_claim values('a','a',null);
commit;
*/


select
rtrim(FACT_CLAIM.Company_dim_id||'-'||FACT_CLAIM.line_number||'-'||FACT_CLAIM.sub_line_code,'-') as TEST3
from fact_claim;


select
FACT_CLAIM.Company_dim_id||'-'||FACT_CLAIM.line_number||case when FACT_CLAIM.sub_line_code is not null then '-'||FACT_CLAIM.sub_line_code end as TEST3 
from fact_claim;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Using case try something like:

FACT_CLAIM.Company_dim_id||'-'||FACT_CLAIM.line_number||case when FACT_CLAIM.sub_line_code is not null then '-'||FACT_CLAIM.sub_line_code end as TEST3
0
 
slightwv (䄆 Netminder) Commented:
I prefer the first way because it makes more sense to me but another way is:
rtrim(FACT_CLAIM.Company_dim_id||'-'||FACT_CLAIM.line_number||'-'||FACT_CLAIM.sub_line_code,'-') as TEST3
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
fact_claim.company_dim_id || '-' || fact_claim.line_number || nvl2(fact_claim.sub_line_code, '-'|| fact_claim.sub_line_code,null)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Please try this..if any field can be null..

INPUT DATA

| COMPANY_DIM_ID | LINE_NUMBER | SUB_LINE_CODE |
|----------------|-------------|---------------|
|              a |           a |             a |
|              a |           a |        (null) |
|              a |      (null) |        (null) |
|              a |           a |             b |
|         (null) |      (null) |             b |
|         (null) |           a |             b |
|         (null) |      (null) |        (null) |

Open in new window


SOLUTION
select company_dim_id,line_number,sub_line_code,
TRIM(
    CASE WHEN fact_claim.company_dim_id IS NOT NULL THEN fact_claim.company_dim_id ELSE ' ' END
 || CASE WHEN fact_claim.company_dim_id IS NOT NULL AND fact_claim.line_number IS NOT NULL THEN CONCAT('-', fact_claim.line_number) 
         WHEN fact_claim.company_dim_id IS NULL AND fact_claim.line_number IS NOT NULL THEN fact_claim.line_number
  ELSE ' ' END 
 || CASE WHEN fact_claim.line_number IS NOT NULL AND fact_claim.sub_line_code IS NOT NULL THEN CONCAT('-', fact_claim.sub_line_code) 
         WHEN fact_claim.line_number IS NULL AND fact_claim.sub_line_code IS NOT NULL THEN fact_claim.sub_line_code
  ELSE ' ' END
 ) addition
from fact_claim;

Open in new window


OUTPUT -> Check the ADDITION  column.
| COMPANY_DIM_ID | LINE_NUMBER | SUB_LINE_CODE | ADDITION |
|----------------|-------------|---------------|----------|
|              a |           a |             a |    a-a-a |
|              a |           a |        (null) |      a-a |
|              a |      (null) |        (null) |        a |
|              a |           a |             b |    a-a-b |
|         (null) |      (null) |             b |        b |
|         (null) |           a |             b |      a-b |
|         (null) |      (null) |        (null) |   (null) |

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>Please try this..if any field can be null..

Pawan, please take the time read the question:
This works fine until the last field, which can be null, the first two cannot be null .
0
 
Pawan KumarDatabase ExpertCommented:
Thanks slightwv,  I already read the requirement that why I mentioned Please try this..if any field can be null.

>>This works fine until the last field, which can be null, the first two cannot be null .
The code is working fine for above scenario and even work if any of the field is null, means can be considered in future(if not today).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.