can I put an if statement within my view?

I have a statement within an oracle view select statement.  (entire view attached if necessary)

MAX(SETPOINT) ||' '|| MAX(SETPOINTUNITS) AS SETPOINT,

Now, the thing is, I do not want to merge the second (space) and third part(setpointunits) of this concatenation if the first part (setpoint) has a value of 'N/A'

I didnt sleep well, and am very tired... I can't think of how to do this.  Please help if you know the answer, it is very urgent.  If you need more info, let me know.  thanks.
CREATE OR REPLACE VIEW I_INSTRUMENTDATA
(SDMTREPORTID, TAG_NO, TAGPREFIX, LOBJCLAID, LOBJID, 
 CLASSNAME, SPECIFICCLASS, SERVICE, DESCRIPTION, MANUFACTURER, 
 SURFACEFINISH, MODELNO, MOC, MATERIALSEAL, EXRATING, 
 CESTAMPING, STATUS, CONTROL, FOXTAG, SERIALNO, 
 PONO, SIGRANGE, SETPOINT, FAILMODE, INSTRLOW, 
 INSTRHIGH, INSTRUNITS, IOPOINT, LOOPDWG, PID, 
 CHANGENO, SPECNO, CALIBRLOW, CALIBRHIGH, CALIBRUNITS, 
 PWEC, Q1RV, Q2RV, Q3RV, Q4RV, 
 Q5RV, Q1IVM, Q2IVM, Q3IVM, Q4IVM, 
 Q5IVM, Q6IVM, Q7IVM, Q1ADQ, Q1BDQ, 
 Q2ADQ, Q2BDQ, Q2CDQ, Q3DQ, Q4DQ, 
 Q5DQ, WLSHEET)
AS 
SELECT
MAX(SDMTREPORTID) AS SDMTREPORTID,
MAX(TAG_NO) AS TAG_NO,
MAX(TAGPREFIX) AS TAGPREFIX,
MAX(LOBJCLAID) AS LOBJCLAID,
MAX(LOBJID) AS LOBJID,
MAX(CLASSNAME) AS CLASSNAME,
MAX(SPECIFICCLASS) AS SPECIFICCLASS,
MAX(SERVICE) AS SERVICE,
MAX(DESCRIPTION)AS DESCRIPTION,
MAX(MANUFACTURER) AS MANUFACTURER,
MAX(SURFACEFINISH) AS SURFACEFINISH,
MAX(MODELNO) AS MODELNO,
MAX(MOC) AS MOC,
MAX(MATERIALSEAL) AS MATERIALSEAL,
MAX(EXRATING) AS EXRATING,
MAX(CESTAMPING) AS CESTAMPING,
MAX(STATUS) AS STATUS,
MAX(CONTROL) AS CONTROL,
MAX(FOXTAG) AS FOXTAG,
MAX(SERIALNO) AS SERIALNO,
MAX(PONO) AS PONO,
MAX(SIGRANGE) AS SIGRANGE,
MAX(SETPOINT) ||' '|| MAX(SETPOINTUNITS) AS SETPOINT,
MAX(FAILMODE) AS FAILMODE,
MAX(INSTRLOW) AS INSTRLOW,
MAX(INSTRHIGH) AS INSTRHIGH,
MAX(INSTRUNITS) AS INSTRUNITS,
MAX(IOPOINT) AS IOPOINT,
MAX(LOOPDWG) AS LOOPDWG,
MAX(PID) AS PID,
MAX(CHANGENO) AS CHANGENO,
MAX(SPECNO) AS SPECNO,
MAX(CALIBRLOW) AS CALIBRLOW,
MAX(CALIBRHIGH) AS CALIBRHIGH,
MAX(CALIBRUNITS) AS CALIBRUNITS,
MAX(PWEC) AS PWEC,
MAX(Q1RV) AS Q1RV, 
MAX(Q2RV) AS Q2RV, 
MAX(Q3RV) AS Q3RV, 
MAX(Q4RV) AS Q4RV, 
MAX(Q5RV) AS Q5RV, 
MAX(Q1IVM) AS Q1IVM,
MAX(Q2IVM) AS Q2IVM,
MAX(Q3IVM) AS Q3IVM,
MAX(Q4IVM) AS Q4IVM,
MAX(Q5IVM) AS Q5IVM,
MAX(Q6IVM) AS Q6IVM,
MAX(Q7IVM) AS Q7IVM,                       
MAX(Q1aDQ) AS Q1aDQ,
MAX(Q1bDQ) AS Q1bDQ,
MAX(Q2aDQ) AS Q2aDQ,
MAX(Q2bDQ) AS Q2bDQ,
MAX(Q2cDQ) AS Q2cDQ,
MAX(Q3DQ) AS Q3DQ, 
MAX(Q4DQ) AS Q4DQ, 
MAX(Q5DQ) AS Q5DQ,
MAX(WLSheet) AS WLSheet
FROM I_INSTRUMENTATTR_COLS GROUP BY SDMTREPORTID, TAG_NO
/

Open in new window

obrienjAsked:
Who is Participating?
 
Ashish PatelConnect With a Mentor Commented:
Like this.
CREATE OR REPLACE VIEW I_INSTRUMENTDATA
(SDMTREPORTID, TAG_NO, TAGPREFIX, LOBJCLAID, LOBJID, 
 CLASSNAME, SPECIFICCLASS, SERVICE, DESCRIPTION, MANUFACTURER, 
 SURFACEFINISH, MODELNO, MOC, MATERIALSEAL, EXRATING, 
 CESTAMPING, STATUS, CONTROL, FOXTAG, SERIALNO, 
 PONO, SIGRANGE, SETPOINT, FAILMODE, INSTRLOW, 
 INSTRHIGH, INSTRUNITS, IOPOINT, LOOPDWG, PID, 
 CHANGENO, SPECNO, CALIBRLOW, CALIBRHIGH, CALIBRUNITS, 
 PWEC, Q1RV, Q2RV, Q3RV, Q4RV, 
 Q5RV, Q1IVM, Q2IVM, Q3IVM, Q4IVM, 
 Q5IVM, Q6IVM, Q7IVM, Q1ADQ, Q1BDQ, 
 Q2ADQ, Q2BDQ, Q2CDQ, Q3DQ, Q4DQ, 
 Q5DQ, WLSHEET)
AS 
SELECT
MAX(SDMTREPORTID) AS SDMTREPORTID,
MAX(TAG_NO) AS TAG_NO,
MAX(TAGPREFIX) AS TAGPREFIX,
MAX(LOBJCLAID) AS LOBJCLAID,
MAX(LOBJID) AS LOBJID,
MAX(CLASSNAME) AS CLASSNAME,
MAX(SPECIFICCLASS) AS SPECIFICCLASS,
MAX(SERVICE) AS SERVICE,
MAX(DESCRIPTION)AS DESCRIPTION,
MAX(MANUFACTURER) AS MANUFACTURER,
MAX(SURFACEFINISH) AS SURFACEFINISH,
MAX(MODELNO) AS MODELNO,
MAX(MOC) AS MOC,
MAX(MATERIALSEAL) AS MATERIALSEAL,
MAX(EXRATING) AS EXRATING,
MAX(CESTAMPING) AS CESTAMPING,
MAX(STATUS) AS STATUS,
MAX(CONTROL) AS CONTROL,
MAX(FOXTAG) AS FOXTAG,
MAX(SERIALNO) AS SERIALNO,
MAX(PONO) AS PONO,
MAX(SIGRANGE) AS SIGRANGE,
Decode(MAX(SETPOINT), 'N/A', MAX(SETPOINT), MAX(SETPOINT) ||' '|| MAX(SETPOINTUNITS)) AS SETPOINT,
MAX(FAILMODE) AS FAILMODE,
MAX(INSTRLOW) AS INSTRLOW,
MAX(INSTRHIGH) AS INSTRHIGH,
MAX(INSTRUNITS) AS INSTRUNITS,
MAX(IOPOINT) AS IOPOINT,
MAX(LOOPDWG) AS LOOPDWG,
MAX(PID) AS PID,
MAX(CHANGENO) AS CHANGENO,
MAX(SPECNO) AS SPECNO,
MAX(CALIBRLOW) AS CALIBRLOW,
MAX(CALIBRHIGH) AS CALIBRHIGH,
MAX(CALIBRUNITS) AS CALIBRUNITS,
MAX(PWEC) AS PWEC,
MAX(Q1RV) AS Q1RV, 
MAX(Q2RV) AS Q2RV, 
MAX(Q3RV) AS Q3RV, 
MAX(Q4RV) AS Q4RV, 
MAX(Q5RV) AS Q5RV, 
MAX(Q1IVM) AS Q1IVM,
MAX(Q2IVM) AS Q2IVM,
MAX(Q3IVM) AS Q3IVM,
MAX(Q4IVM) AS Q4IVM,
MAX(Q5IVM) AS Q5IVM,
MAX(Q6IVM) AS Q6IVM,
MAX(Q7IVM) AS Q7IVM,                       
MAX(Q1aDQ) AS Q1aDQ,
MAX(Q1bDQ) AS Q1bDQ,
MAX(Q2aDQ) AS Q2aDQ,
MAX(Q2bDQ) AS Q2bDQ,
MAX(Q2cDQ) AS Q2cDQ,
MAX(Q3DQ) AS Q3DQ, 
MAX(Q4DQ) AS Q4DQ, 
MAX(Q5DQ) AS Q5DQ,
MAX(WLSheet) AS WLSheet
FROM I_INSTRUMENTATTR_COLS GROUP BY SDMTREPORTID, TAG_NO
/

Open in new window

0
 
Ashish PatelCommented:
Use Decode function there and you would be good.
0
 
prasanthi_kCommented:
Try this :
CREATE OR REPLACE VIEW I_INSTRUMENTDATA
(SDMTREPORTID, TAG_NO, TAGPREFIX, LOBJCLAID, LOBJID, 
 CLASSNAME, SPECIFICCLASS, SERVICE, DESCRIPTION, MANUFACTURER, 
 SURFACEFINISH, MODELNO, MOC, MATERIALSEAL, EXRATING, 
 CESTAMPING, STATUS, CONTROL, FOXTAG, SERIALNO, 
 PONO, SIGRANGE, SETPOINT, FAILMODE, INSTRLOW, 
 INSTRHIGH, INSTRUNITS, IOPOINT, LOOPDWG, PID, 
 CHANGENO, SPECNO, CALIBRLOW, CALIBRHIGH, CALIBRUNITS, 
 PWEC, Q1RV, Q2RV, Q3RV, Q4RV, 
 Q5RV, Q1IVM, Q2IVM, Q3IVM, Q4IVM, 
 Q5IVM, Q6IVM, Q7IVM, Q1ADQ, Q1BDQ, 
 Q2ADQ, Q2BDQ, Q2CDQ, Q3DQ, Q4DQ, 
 Q5DQ, WLSHEET)
AS 
SELECT
MAX(SDMTREPORTID) AS SDMTREPORTID,
MAX(TAG_NO) AS TAG_NO,
MAX(TAGPREFIX) AS TAGPREFIX,
MAX(LOBJCLAID) AS LOBJCLAID,
MAX(LOBJID) AS LOBJID,
MAX(CLASSNAME) AS CLASSNAME,
MAX(SPECIFICCLASS) AS SPECIFICCLASS,
MAX(SERVICE) AS SERVICE,
MAX(DESCRIPTION)AS DESCRIPTION,
MAX(MANUFACTURER) AS MANUFACTURER,
MAX(SURFACEFINISH) AS SURFACEFINISH,
MAX(MODELNO) AS MODELNO,
MAX(MOC) AS MOC,
MAX(MATERIALSEAL) AS MATERIALSEAL,
MAX(EXRATING) AS EXRATING,
MAX(CESTAMPING) AS CESTAMPING,
MAX(STATUS) AS STATUS,
MAX(CONTROL) AS CONTROL,
MAX(FOXTAG) AS FOXTAG,
MAX(SERIALNO) AS SERIALNO,
MAX(PONO) AS PONO,
MAX(SIGRANGE) AS SIGRANGE,
DECODE(SETPOINT, 'N/A', MAX(SETPOINT), MAX(SETPOINT) ||' '|| MAX(SETPOINTUNITS)) AS SETPOINT,
MAX(FAILMODE) AS FAILMODE,
MAX(INSTRLOW) AS INSTRLOW,
MAX(INSTRHIGH) AS INSTRHIGH,
MAX(INSTRUNITS) AS INSTRUNITS,
MAX(IOPOINT) AS IOPOINT,
MAX(LOOPDWG) AS LOOPDWG,
MAX(PID) AS PID,
MAX(CHANGENO) AS CHANGENO,
MAX(SPECNO) AS SPECNO,
MAX(CALIBRLOW) AS CALIBRLOW,
MAX(CALIBRHIGH) AS CALIBRHIGH,
MAX(CALIBRUNITS) AS CALIBRUNITS,
MAX(PWEC) AS PWEC,
MAX(Q1RV) AS Q1RV, 
MAX(Q2RV) AS Q2RV, 
MAX(Q3RV) AS Q3RV, 
MAX(Q4RV) AS Q4RV, 
MAX(Q5RV) AS Q5RV, 
MAX(Q1IVM) AS Q1IVM,
MAX(Q2IVM) AS Q2IVM,
MAX(Q3IVM) AS Q3IVM,
MAX(Q4IVM) AS Q4IVM,
MAX(Q5IVM) AS Q5IVM,
MAX(Q6IVM) AS Q6IVM,
MAX(Q7IVM) AS Q7IVM,                       
MAX(Q1aDQ) AS Q1aDQ,
MAX(Q1bDQ) AS Q1bDQ,
MAX(Q2aDQ) AS Q2aDQ,
MAX(Q2bDQ) AS Q2bDQ,
MAX(Q2cDQ) AS Q2cDQ,
MAX(Q3DQ) AS Q3DQ, 
MAX(Q4DQ) AS Q4DQ, 
MAX(Q5DQ) AS Q5DQ,
MAX(WLSheet) AS WLSheet
FROM I_INSTRUMENTATTR_COLS GROUP BY SDMTREPORTID, TAG_NO
/

Open in new window

0
 
obrienjAuthor Commented:
god, really slow today, the second you said it I realized it as well. thanks.
0
All Courses

From novice to tech pro — start learning today.