asked on

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

Ashish Patel

Use Decode function there and you would be good.

ASKER CERTIFIED SOLUTION

Ashish Patel

membership

This solution is only available to members.

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

Start Free Trial

prasanthi_k

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

obrienj

ASKER

god, really slow today, the second you said it I realized it as well. thanks.