CODEWITHSUNDEEP
sqloracle-database
Wil50n
Wil50n

Reputation: 23

IF/ELSE Condition in SQL

I am trying to do an IF/ELSE statement for PLSQL but I am not sure if I am doing it the right way as it is always having an error: Missing right parenthesis on IF (TO_NUMBER(SUBSTR(ATTR_VALUE, 6, 2)) != TO_NUMBER(SUBSTR(ATTR_VALUE, 1, 2))) THEN but the parenthesis seems balance to me.

SELECT *
FROM
(
IF (TO_NUMBER(SUBSTR(ATTR_VALUE, 6, 2)) != TO_NUMBER(SUBSTR(ATTR_VALUE, 1, 2))) THEN
  SELECT ID, DATE, ATTR_VALUE, (TO_NUMBER(SUBSTR(ATTR_VALUE, 6, 4))-TO_NUMBER(SUBSTR(ATTR_VALUE, 1, 4))-48) DIFF
ELSE
  SELECT ID, DATE, ATTR_VALUE, (TO_NUMBER(SUBSTR(ATTR_VALUE, 6, 4))-TO_NUMBER(SUBSTR(ATTR_VALUE, 1, 4))) DIFF
END IF
FROM Table A
ORDER BY TIME
)
WHERE DIFF>26

Please kindly assist me on this issue.

Upvotes: 1

Views: 1614

Answers (2)

Orangecrush
Orangecrush

Reputation: 1990

You can modify your query to something on the lines as shown below,

SELECT *
  FROM (SELECT ID,
               DATE,
               ATTR_VALUE,
               decode(TO_NUMBER(SUBSTR(ATTR_VALUE, 6, 2)),
                      TO_NUMBER(SUBSTR(ATTR_VALUE, 1, 2)),
                      TO_NUMBER(SUBSTR(ATTR_VALUE, 6, 4)) -
                      TO_NUMBER(SUBSTR(ATTR_VALUE, 1, 4)),
                      TO_NUMBER(SUBSTR(ATTR_VALUE, 6, 4)) -
                      TO_NUMBER(SUBSTR(ATTR_VALUE, 1, 4))) DIFF
          FROM A)
 WHERE diff > 26

Upvotes: 0

Taryn
Taryn

Reputation: 247710

You should be able to use a CASE statement to get the result:

SELECT *
FROM
(
  SELECT ID, 
    DATE, 
    ATTR_VALUE,
    CASE 
      WHEN TO_NUMBER(SUBSTR(ATTR_VALUE, 6, 2)) != TO_NUMBER(SUBSTR(ATTR_VALUE, 1, 2))
      THEN (TO_NUMBER(SUBSTR(ATTR_VALUE, 6, 4))-TO_NUMBER(SUBSTR(ATTR_VALUE, 1, 4))-48)
      ELSE (TO_NUMBER(SUBSTR(ATTR_VALUE, 6, 4))-TO_NUMBER(SUBSTR(ATTR_VALUE, 1, 4)))
    END DIFF
  FROM Table A
  ORDER BY TIME
)
WHERE DIFF>26

Upvotes: 5

Related Questions