Business Objects Query: Merge Two Columns of Data into One Column

Here are my two columns within my query. How do I format this query into one column.

COLUMN-1
=Substr([CCPO ID];1;2)+Substr([Agcy Sub Elm Flag];1;2)+Substr([PAS];1;50)+Substr([Org Struct ID Shred];1;50)

COLUMN-2
=Substr([CCPO ID];1;2)+Substr([Agcy Sub Elm Flag];1;2)+Substr([Unit ID Cd];1;6)+Substr([Org Struct ID Shred];1;50)

The difference is that C1 looks for +Substr([PAS];1;50) and C2 looks for +Substr([Unit ID Cd];1;6)
CMILLERAsked:
Who is Participating?
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.

Naveen KumarProduction Manager / Application Support ManagerCommented:
Do you mean you want to concatenate those column outputs ? like "Column-1 column-2".

If yes, then use concat function to make them as 1 column output.

If not, can you detail on exactly what output do you need with some sample data would be good to understand your question.
0
CMILLERAuthor Commented:
I am looking for a IF statement.

=IF not C1 then C2
0
MikeOM_DBACommented:
DECODE(C1,NULL,C2,C1)

Open in new window

:p
0
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.

CMILLERAuthor Commented:
ok, where do I put that?
0
MikeOM_DBACommented:
First, what does this: "=IF not C1 then C2" mean?
Not what? not null? not = 0?

You replace as formula for col 1.
0
CMILLERAuthor Commented:
Here is the "Not What"

The difference is that C1 looks for +Substr([PAS];1;50) and C2 looks for +Substr([Unit ID Cd];1;6)
0
MikeOM_DBACommented:
And that difference is compared to what condition?
And what is the expected result from this compare?
0
CMILLERAuthor Commented:
Lets backup....currently this query is formatted for two columns and here is the output. Please see the attached. I think I see what you are asking for. Since this is in BO, I dont see the query structure.

The difference is the employee type from the [Agcy Sub Elm Flag]

[Agcy Sub Elm Flag]=ZR: =Substr([CCPO ID];1;2)+Substr([Agcy Sub Elm Flag];1;2)+Substr([Unit ID Cd];1;6)+Substr([Org Struct ID Shred];1;50)

[Agcy Sub Elm Flag]=ZF: =Substr([CCPO ID];1;2)+Substr([Agcy Sub Elm Flag];1;2)+Substr([PAS];1;50)+Substr([Org Struct ID Shred];1;50)
Query-C1-C2.xlsx
0
MikeOM_DBACommented:
The difference is the employee type from the [Agcy Sub Elm Flag]

Does this mean you would choose between "substr([Unit ID Cd];1;6)" and "Substr([PAS];1;50)" depending on the employee type from "[Agcy Sub Elm Flag]"?

What values would determine which to choose?
Or...do you choose depending on the LENGTH of the data?

PS: Your code seems to be from Windoze SQL server or Excel.
0
MikeOM_DBACommented:
Perhaps this is what you need:
COLUMN-NEW
=Substr([CCPO ID];1;2)+Substr([Agcy Sub Elm Flag];1;2)+IF(LENGTH([PAS])> LENGTH([Unit ID Cd]),Substr([PAS];1;50),Substr([Unit ID Cd];1;6))+Substr([Org Struct ID Shred];1;50)

Open in new window

0
CMILLERAuthor Commented:
its from Business Objects
0
MikeOM_DBACommented:
Has been too long since I used BO...did the post "COLUMN-NEW" work?
0
CMILLERAuthor Commented:
I am trying it now.....
0
CMILLERAuthor Commented:
Having an issue with parenthesis and +'s
0
MikeOM_DBACommented:
What do you use in BO for condition: 'IF()',  'DECODE()' or CASE...?
...Replace the 'IF()' with that.
0
CMILLERAuthor Commented:
It has IF and LENGTH
BO-IF.JPG
Length.JPG
0
CMILLERAuthor Commented:
Here is an example of what I have used before with an IF

=If([P ID]="1" Or [O ID] = "2" And ([T ID]= "Q" Or [N ID]="E" Or [NG ID]= "F"))Then "NDS" Else "DS"
0
MikeOM_DBACommented:
OK, here it is:
=Substr([CCPO ID];1;2)+Substr([Agcy Sub Elm Flag];1;2)+IF(LENGTH([PAS])> LENGTH([Unit ID Cd]) THEN Substr([PAS];1;50) ELSE Substr([Unit ID Cd];1;6))+Substr([Org Struct ID Shred];1;50)

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
CMILLERAuthor Commented:
It Worked, Thanks.
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.