asked on

PLSQL Developer: Proper syntax for an IF statement

This is probably very simple but it's been driving me crazy tring to find a solution. I want to write an IF statement that would look at a date field, and based on the date, would either return a value of "0" or perform a date difference function.

The logic would follow:
IF field_date >= today's date THEN '0'
ELSEIF field_date < today's date THEN today's date - field_date
END IF AS "LOS"

Also, if my field_date is formatted as text, what do I need to do to use it in a caculation function?

johnsone

Assuming this is Oracle, as it is in the PL/SQL forum.

Replace <fmt> with the date format that matches your text field. There is no error checking if the string does not match the format.

if to_date(field_date, '<fmt>') >= trunc(sysdate) then
  los := 0;
else
  los := trunc(sysdate) - trunc(to_date(field_date, '<fmt>'))
end if;

Open in new window

Luis5011

ASKER

Johnsone,

It did work, but this is what I noticed. Within Developer, I was able to correctly sort by the date when I ran my SQL just based on pulling the Admission Date. So it's obviously identifing that field as a true date, not text. So I removed the TO_DATE component but I keep getting errors stating that is expecting a FROM statement and then some other errors. Can you look at what I did and tell me if I missed anything?

IF (O261454."Admission_Date")>= trunc(SYSDATE) THEN
LOSS := 0;
ELSE
LOSS := trunc(SYSDATE) - (O261454."Admission_Date")
END IF;

johnsone

Missing semi-colon?

IF O261454."Admission_Date" >= trunc(SYSDATE) THEN
LOSS := 0;
ELSE
LOSS := trunc(SYSDATE) - O261454."Admission_Date";
END IF;

Luis5011

ASKER

No, still getting the FROM statement not where expected and invalid SQL statement errors.

johnsone

Obviously, there is more code around this. Can you post the code, and where the first error occurs?

Luis5011

ASKER

It's a Select statement with a number of fields. There is more, but I won't burden you with it all. The most advanced statement I have in here is a CASE statement which works fine if I'm not trying to run the IF statement.

Select 
O261454."Admission_Date", 
O261454."LOS", 
IF O261454."Admission_Date" >= trunc(SYSDATE) THEN
  LOSS := 0;
ELSE
  LOSS := trunc(SYSDATE) - O261454."Admission_Date";
END IF;
O261454."Status", 
O261454."Status_Reason"
 
FROM "dbo"."HSO_Census"@NEW_CCAPROD O261454

Open in new window

Luis5011

ASKER

What about a CASE statement?

johnsone

Sorry, I thought you were in PL/SQL, not a select statement. This should work:

Select
O261454."Admission_Date",
O261454."LOS",
case when trun(O261454."Admission_Date") >= trunc(SYSDATE) THEN 0
ELSE trunc(SYSDATE) - trunc(O261454."Admission_Date") end as loss,
O261454."Status",
O261454."Status_Reason"

FROM "dbo"."HSO_Census"@NEW_CCAPROD O261454

ASKER CERTIFIED SOLUTION

johnsone

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Luis5011

ASKER

Now I'm getting an inconsistent data type error. Nothing makes this thing happy!

johnsone

The inconsistent data type is because of the trunc on the string field. The test I was building was working with date fields. Sorry about that.

Select
O261454."Admission_Date",
O261454."LOS",
case when O261454."Admission_Date" >= trunc(SYSDATE) THEN 0
ELSE trunc(SYSDATE) - O261454."Admission_Date" end as loss,
O261454."Status",
O261454."Status_Reason"

FROM "dbo"."HSO_Census"@NEW_CCAPROD O261454

Luis5011

ASKER

It was the single quotes that were throwing me off. Your code worked, I just left the single quotes around the 0 value.