reignsupreme
asked on
PL/SQL Select statement with If-Then-Else condition
I have a select statement that requires a condition to return the proper value in 1 of the columns, here is my syntax and an example of what I need returned in the last column "REASON"
SELECT DISTINCT
SI.LABEL SITENAME,
P.SCREENNUM,
P.RANDNUM,
P.LABEL,
P.PATINIT,
P.SFAILREASONID,
P.REASONID,
P.CRITERID,
TO_CHAR(P.SCREENDT, 'DD-MON-YYYY') SCREENDT ,
S.VALUE STATUS,
LTRIM(C.LABEL) SFAILTEXT,
LTRIM(C2.LABEL) PFAILTEXT,
C.LABEL ||' '|| P.CRITERID ||' '|| P.SFAILCOMMENT REASON
FROM
PNPATIENT_VIEW P,
PNSITE SI,
PATIENTSTATUSLIST_VIEW S,
SCREENFAILREASONS_VIEW C,
SCREENFAILREASONS_VIEW C2,
PNSEARCH_VIEW V1,
PNSEARCH_VIEW V2
WHERE P.SFAILREASONID = C.CODE (+)
AND P.REASONID = C2.CODE (+)
AND P.STATUS = S.CODE (+)
AND SI.SUBMODINSTID = P.SITEID(+)
AND P.SITEID = TO_NUMBER(V1.VALUE)
AND P.SUBMODINSTID = TO_NUMBER(V2.VALUE)
The condition is:
if (p.reasonid is not null) and (c.sfailreasonid is null) then
ltrim(c.label) sfailtext
else
ltrim(c.label) sfailtext
end if
SELECT DISTINCT
SI.LABEL SITENAME,
P.SCREENNUM,
P.RANDNUM,
P.LABEL,
P.PATINIT,
P.SFAILREASONID,
P.REASONID,
P.CRITERID,
TO_CHAR(P.SCREENDT, 'DD-MON-YYYY') SCREENDT ,
S.VALUE STATUS,
LTRIM(C.LABEL) SFAILTEXT,
LTRIM(C2.LABEL) PFAILTEXT,
C.LABEL ||' '|| P.CRITERID ||' '|| P.SFAILCOMMENT REASON
FROM
PNPATIENT_VIEW P,
PNSITE SI,
PATIENTSTATUSLIST_VIEW S,
SCREENFAILREASONS_VIEW C,
SCREENFAILREASONS_VIEW C2,
PNSEARCH_VIEW V1,
PNSEARCH_VIEW V2
WHERE P.SFAILREASONID = C.CODE (+)
AND P.REASONID = C2.CODE (+)
AND P.STATUS = S.CODE (+)
AND SI.SUBMODINSTID = P.SITEID(+)
AND P.SITEID = TO_NUMBER(V1.VALUE)
AND P.SUBMODINSTID = TO_NUMBER(V2.VALUE)
The condition is:
if (p.reasonid is not null) and (c.sfailreasonid is null) then
ltrim(c.label) sfailtext
else
ltrim(c.label) sfailtext
end if
Hi riegnsupreme,
You can solve this problem by using oracle's DECODE() statement. I am confused why you have the same code in the 'else' part that you have in your 'if' part. Shouldn't they be different?
-Jon
You can solve this problem by using oracle's DECODE() statement. I am confused why you have the same code in the 'else' part that you have in your 'if' part. Shouldn't they be different?
-Jon
Using Case will work also.
-Jon
-Jon
ASKER
Hi DanielWilson,
In the above example I get an error "Missing Keyword"
In the above example I get an error "Missing Keyword"
ASKER
Hi onstottj,
Maybe this will clarify my question further:
I have 2 columns SFAILTEXT & PFAILTEXT, if SFAILTEXT is not null, I would like to put that value in the "REASON" column, if SFAILTEXT is null but PFAILFAILTEXT is NOT null, I would like to put PFAILTEXT's value in the "REASON" column. If both columns have a value, then SFAILTEXT's value should take priority and be placed in the "REASON" Column
Maybe this will clarify my question further:
I have 2 columns SFAILTEXT & PFAILTEXT, if SFAILTEXT is not null, I would like to put that value in the "REASON" column, if SFAILTEXT is null but PFAILFAILTEXT is NOT null, I would like to put PFAILTEXT's value in the "REASON" column. If both columns have a value, then SFAILTEXT's value should take priority and be placed in the "REASON" Column
Does it give any indication where the keyword is missing?
there are a couple problems w/ the code.
1. As onstottj pointed out, both branches of the if/then or case return the same result. That's a logic error, but not a syntax error.
2. We now have to columns in the result trying to be named REASON. is the if/then or replacement thereof intended to replace C.LABEL ||' '|| P.CRITERID ||' '|| P.SFAILCOMMENT REASON ?
there are a couple problems w/ the code.
1. As onstottj pointed out, both branches of the if/then or case return the same result. That's a logic error, but not a syntax error.
2. We now have to columns in the result trying to be named REASON. is the if/then or replacement thereof intended to replace C.LABEL ||' '|| P.CRITERID ||' '|| P.SFAILCOMMENT REASON ?
That clarification helps me some ... I'm not familiar w/ the DECODE option, so that solution may be more elegant. The following should work, however:
SELECT DISTINCT
SI.LABEL SITENAME,
P.SCREENNUM,
P.RANDNUM,
P.LABEL,
P.PATINIT,
P.SFAILREASONID,
P.REASONID,
P.CRITERID,
TO_CHAR(P.SCREENDT, 'DD-MON-YYYY') SCREENDT ,
S.VALUE STATUS,
LTRIM(C.LABEL) SFAILTEXT,
LTRIM(C2.LABEL) PFAILTEXT,
(Case When (c.sfailreasonid is null)
Then ltrim(c2.label)
Else ltrim(c.label)) REASON
FROM
PNPATIENT_VIEW P,
PNSITE SI,
PATIENTSTATUSLIST_VIEW S,
SCREENFAILREASONS_VIEW C,
SCREENFAILREASONS_VIEW C2,
PNSEARCH_VIEW V1,
PNSEARCH_VIEW V2
WHERE P.SFAILREASONID = C.CODE (+)
AND P.REASONID = C2.CODE (+)
AND P.STATUS = S.CODE (+)
AND SI.SUBMODINSTID = P.SITEID(+)
AND P.SITEID = TO_NUMBER(V1.VALUE)
AND P.SUBMODINSTID = TO_NUMBER(V2.VALUE)
SELECT DISTINCT
SI.LABEL SITENAME,
P.SCREENNUM,
P.RANDNUM,
P.LABEL,
P.PATINIT,
P.SFAILREASONID,
P.REASONID,
P.CRITERID,
TO_CHAR(P.SCREENDT, 'DD-MON-YYYY') SCREENDT ,
S.VALUE STATUS,
LTRIM(C.LABEL) SFAILTEXT,
LTRIM(C2.LABEL) PFAILTEXT,
(Case When (c.sfailreasonid is null)
Then ltrim(c2.label)
Else ltrim(c.label)) REASON
FROM
PNPATIENT_VIEW P,
PNSITE SI,
PATIENTSTATUSLIST_VIEW S,
SCREENFAILREASONS_VIEW C,
SCREENFAILREASONS_VIEW C2,
PNSEARCH_VIEW V1,
PNSEARCH_VIEW V2
WHERE P.SFAILREASONID = C.CODE (+)
AND P.REASONID = C2.CODE (+)
AND P.STATUS = S.CODE (+)
AND SI.SUBMODINSTID = P.SITEID(+)
AND P.SITEID = TO_NUMBER(V1.VALUE)
AND P.SUBMODINSTID = TO_NUMBER(V2.VALUE)
ASKER
Hi DanielWilson,
Unfortunately, it does not tell me where the missing "Keyword" is.
The if/then should replace the "REASON" in: C.LABEL ||' '|| P.CRITERID ||' '|| P.SFAILCOMMENT REASON
Thank you
Unfortunately, it does not tell me where the missing "Keyword" is.
The if/then should replace the "REASON" in: C.LABEL ||' '|| P.CRITERID ||' '|| P.SFAILCOMMENT REASON
Thank you
ASKER
Hi DanielWilson,
I tried your second example, however I am still gettin the "Missing Keyword" error. Is there a way I can import the code into SQL Plus? I'm using a 3rd party tool that does not give any line number, etc.
I tried your second example, however I am still gettin the "Missing Keyword" error. Is there a way I can import the code into SQL Plus? I'm using a 3rd party tool that does not give any line number, etc.
ahhh... I don't know the Oracle tools that well. I'm coming at this from the SQL Syntax side.
Did the query as you posted it work (except for the if/then type of idea)?
Did the query as you posted it work (except for the if/then type of idea)?
ASKER
I was able to get it to run in SQL Plus, the error is at Line 16: Else ltrim(c.label)) REASON
ASKER
Also, to answer your question, yes it runs minus the IF/THEN condition.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!
http://www.oracle-base.com/articles/9i/Case9i.php
I think it would go:
SELECT DISTINCT
SI.LABEL SITENAME,
P.SCREENNUM,
P.RANDNUM,
P.LABEL,
P.PATINIT,
P.SFAILREASONID,
P.REASONID,
P.CRITERID,
TO_CHAR(P.SCREENDT, 'DD-MON-YYYY') SCREENDT ,
S.VALUE STATUS,
LTRIM(C.LABEL) SFAILTEXT,
LTRIM(C2.LABEL) PFAILTEXT,
C.LABEL ||' '|| P.CRITERID ||' '|| P.SFAILCOMMENT REASON ,
(Case When (p.reasonid is not null) and (c.sfailreasonid is null)
Then ltrim(c.label)
Else ltrim(c.label)) REASON
FROM
PNPATIENT_VIEW P,
PNSITE SI,
PATIENTSTATUSLIST_VIEW S,
SCREENFAILREASONS_VIEW C,
SCREENFAILREASONS_VIEW C2,
PNSEARCH_VIEW V1,
PNSEARCH_VIEW V2
WHERE P.SFAILREASONID = C.CODE (+)
AND P.REASONID = C2.CODE (+)
AND P.STATUS = S.CODE (+)
AND SI.SUBMODINSTID = P.SITEID(+)
AND P.SITEID = TO_NUMBER(V1.VALUE)
AND P.SUBMODINSTID = TO_NUMBER(V2.VALUE)