Dealing with a Single Quote in a DB2 CASE Statement

[system]

Hi,
I’ve had a search through the board and can’t find this question so I hope I’m not going over old ground.

I have the following CASE statement:

 CASE ( PDDB2DB.PWRSCUD.USER_DATA_2 )  
       WHEN '2A        ' THEN 'CHARLES CAPP'  
       WHEN '2BA       ' THEN 'MARK O'NEILL'
       WHEN '4AD       ' THEN 'GEORGE PAGE'   
ELSE '          ' END

The single quote in O’Neill has caused problems.

I’ve tried rewriting it the problem line as

WHEN '2BA       ' THEN 'MARK O'!!CHAR(39)!!'NEILL'

to create a single quote in the result from the ASCII code. Unfortunately it returns the result ‘MARK O39 NEILL’.

Can any one help?

I’m using Business Objects 5.1.8 and IBM DB2 V7 Rel 1.

Many thanks

Jude

---

curtins4u (BOB member since 2007-07-25)

[system]

jude,

I’m not sure but shouldn’t you use the concatenate-characters || in stead of !!
Maybe it’s just a typo

Hope this helps,
Johan

---

JdVries (BOB member since 2006-02-06)

[system]

Johan, thanks for quick answers.

I gave it a try, but unfortunately DB2 treats !! and || as the same thing.

Thank you anyway.

Jude

---

curtins4u (BOB member since 2007-07-25)

[system]

Have a look at this post

120×120 10.4 KB

Hi all, I would like to create a prompt that displays the following : Saisir l’année de visite in @Select(Date de vente\Année de la vente) = @Prompt(concat(‘Saisir l’année de visite’,char(39)),‘N’,mono,free) Except that the character ’ is used to delimit the prompt text and I couldn’t use it. I tried Saisir l’année de visite but I didn’t work neither. I also tried using the concat function inside the @Prompt but I had the error UNV0023 Incorrect definition Do you know how I can make Des…

---

haider (BOB member since 2005-07-18)

[system]

Jude,

but unfortunately DB2 treats !! and || as the same thing.

To bad…
Then probably the CHAR-function is not correct.
Maybe there is something like a CHR-function…
As you’ve noticed, I’m not a DB2-specialist.

Johan

---

JdVries (BOB member since 2006-02-06)

[system]

haider, Johan,

thanks for the responses but it’s still not working at the moment.

These are some other variations in the CASE statement I’ve tried:

WHEN '2BA       ' THEN 'MARK O''!!'NEILL'
WHEN '2BA       ' THEN 'MARK O'' 'NEILL'
WHEN '2BA       ' THEN 'MARK O" 'NEILL'

The last two were my attempt to replicate the other topic that haider referred me to.

I’ve looked for alternatives to using CHAR() but can’t find any.

Many thanks

Jude

---

curtins4u (BOB member since 2007-07-25)

[system]

jude,

A bit googling and altavistaing gave me this site:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin

Search here for ‘chr’…

Hope this helps,
Johan

---

JdVries (BOB member since 2006-02-06)

[system]

Johan, unfortunately that seems to be a feature of DB2 V8

Thanks

Jude

---

curtins4u (BOB member since 2007-07-25)

[system]

WHEN ‘2BA ’ THEN ‘MARK O’’ NEILL’

Ensure that its not double quotes after O but two single quotes and there is no single quote before NEILL.

Cheers

---

haider (BOB member since 2005-07-18)

[system]

haider, I tried it with both " and ‘’ without any joy.

I’ve tried this directly in DB2 QMF and have the same issue, so I assume that it’s a DB2 issue rather than Business Objects.

I’’ try some DB2/SQL forums to see if I can find the answer.

Thank you to both of you for your time.

Jude

---

curtins4u (BOB member since 2007-07-25)