asked on

Select case statement in OPENQUERY statement

The following is my code and works up till the second to last line or this one

Case when ELIG_NPS_BONUS IS NULL then BONUS_CNTL_NBR IS NULL else BONUS_CNTL_NBR end NPS_BONUS_CNTL_NBR

What I am trying to do is when ELIG_NPS_BONUS IS NULL then another field BONUS_CNTL_NBR IS NULL else show me what in the BONUS_CNTL_NBR

Is there a way to do this, it is an openquery that is hitting an Oracle server and returning the data.

Select * from OPENQUERY(SIDPERS, 'Select ACN, APP_Name, ADMIN_REMARKS, substr(SSN_SM,6,9), UIC, PARA, LINE, 
	GRADE, MOS, DT_EXPIRE, NCOID, 
	Case when ELIG_SLRP IS NULL then NULL else ''X'' end ELIG_SLRP,
	Case when SLRP_CNTL_NBR IS NULL then NULL else SLRP_CNTL_NBR end SLRP_CNTL_NBR,
	Case when ELIG_NPS_BONUS IS NULL then NULL  else ''X'' end ELIG_NPS_BONUS,
	Case when ELIG_NPS_BONUS IS NULL then BONUS_CNTL_NBR IS NULL else BONUS_CNTL_NBR end NPS_BONUS_CNTL_NBR
	from ACN.ACN_CNTL_NBR_TBL where ACN = ''09-119-003''')

Open in new window

SOLUTION

knightEknight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

Select * from OPENQUERY(SIDPERS, 'Select ACN, APP_Name, ADMIN_REMARKS, substr(SSN_SM,6,9), UIC, PARA, LINE,
      GRADE, MOS, DT_EXPIRE, NCOID,
      Case when ELIG_SLRP IS NULL then NULL else ''X'' end ELIG_SLRP,
      Case when SLRP_CNTL_NBR IS NULL then NULL else SLRP_CNTL_NBR end SLRP_CNTL_NBR,
      Case when ELIG_NPS_BONUS IS NULL then NULL else ''X'' end ELIG_NPS_BONUS,
      Case when ELIG_NPS_BONUS IS NULL then NULL else BONUS_CNTL_NBR end NPS_BONUS_CNTL_NBR
      from ACN.ACN_CNTL_NBR_TBL where ACN = ''09-119-003''')

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

knightEknight

You're right - I didn't even realize they were the same column. (cut-n-paste)

kdeutsch

ASKER

all

Ok, see the error of my ways, what about this, I just noticed I made another mistake in that I need to check for a zero or a null

Case when ELIG_SLRP = ''0'' or when ELIG_SLRP is NULL then NULL else ''X'' end ELIG_SLRP,

cyberkiwi

Case when ELIG_SLRP = '0' or ELIG_SLRP is NULL then NULL else 'X' end ELIG_SLRP,

Since you are quoting the zero I assume it is varchar?
You could also do this

Case when nvl(ELIG_SLRP,'0') = '0' then NULL else 'X' end ELIG_SLRP,

kdeutsch

ASKER

thanks