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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.