DB2 CONCAT FAILING

Running DB2 on Power7.

I am attempting to perform a simple concat in a case statement that would combine a Pack with a Retail where Pack not equal to Zero.

Pack    Retail     NewRet
6           2.99       6/2.99

select case when FIELD1 <> 0 then FIELD1 || '/' || FIELD2
                       else FIELD2 END as NewRet,

I have tried multiple variations and failing every time. I am missing something, but what?....
LVL 1
JeffDeveloperAsked:
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.

grendel777Commented:
I haven't use an AS-400 in about 20 years, but I think you have a datatype issue. Are you trying to divide field1 by field2?
select case when FIELD1 <> 0 then (FIELD1 / FIELD2)
                       else FIELD2 END as NewRet

Open in new window

If you want to display a string:
select case when FIELD1 <> 0 then CAST(FIELD1 AS VARCHAR(10)) + '/' + 
     CAST(FIELD2 AS VARCHAR(10))
                       else CAST(FIELD2 AS VARCHAR(10)) END
as NewRet

Open in new window

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
Kent OlsenDBACommented:
Hi Dejo,

Grendell's mostly correct in that it's a data type issue.  DB2 won't automatically convert numeric data to a string type for concatenation.

But DB2 does use the double pipe (||) for concatenation, not the '+' sign like SQL Server does.


Kent
grendel777Commented:
Gotcha (it has been a while!). Does this work?

select case when FIELD1 <> 0 then CAST(FIELD1 AS VARCHAR(10)) || '/' ||
     CAST(FIELD2 AS VARCHAR(10))
                       else CAST(FIELD2 AS VARCHAR(10)) END
as NewRet

Open in new window

JeffDeveloperAuthor Commented:
That code works perfectly.
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
IBM System i

From novice to tech pro — start learning today.