Solved

# if else

Posted on 2006-11-23
Medium Priority
903 Views
hi there
as
T1.HLOC = IF T2.HLOC  IS MISSING THEN '.' ELSE T2.HLOC   dont work in db2
whts the other way around
thanks
bobby
SELECT
T1.HLOC  ,
T2.HLOC
FROM
(STG_PURCHDE1_WR T1  LEFT OUTER JOIN V_RRR_PURCHDE2_WR T2
ON
T1.HCOMP  = T2.HCOMP
AND T1.HDIV  = T2.HDIV
AND T1.DPORD#  = T2.DPORD#
AND T1.DLINE#  = T2.DLINE#
AND T1.HLOC = IF T2.HLOC  IS MISSING THEN '.' ELSE T2.HLOC  )
0
Question by:bobby2929
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 37

Expert Comment

ID: 18006773
hi
what you need is the case expression  :
T1.HLOC = IF T2.HLOC  IS MISSING THEN '.' ELSE T2.HLOC

will be

T1.HLOC = CASE WHEN T2.HLOC IS NULL THEN '.'
WHEN SOME-OTHER-CONDITION THEN SOME-OTHER-VALUE
ELSE T2.HLOC END

you have to write the END in order to close the case clause

momi
0

LVL 7

Accepted Solution

ID: 18007368
or use COALESCE function

T1.HLOC = COALESCE(T2.HLOC , '.')

COALESCE(T2.HLOC , '.') will return T2.HLOC,  if T2.HLOC is not null otherwise '.'

more help here:

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
###### Suggested Courses
Course of the Month10 days, 13 hours left to enroll