Solved

PLSQL Developer: Proper syntax for an IF statement

Posted on 2009-05-07
12
216 Views
Last Modified: 2013-12-07
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?
0
Comment
Question by:Luis5011
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 24329889
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

0
 

Author Comment

by:Luis5011
ID: 24330392
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;
0
 
LVL 35

Expert Comment

by:johnsone
ID: 24330422
Missing semi-colon?

IF O261454."Admission_Date" >= trunc(SYSDATE) THEN
  LOSS := 0;
ELSE
  LOSS := trunc(SYSDATE) - O261454."Admission_Date";
END IF;
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:Luis5011
ID: 24330468
No, still getting the FROM statement not where expected and invalid SQL statement errors.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 24330482
Obviously, there is more code around this.  Can you post the code, and where the first error occurs?
0
 

Author Comment

by:Luis5011
ID: 24330575
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

0
 

Author Comment

by:Luis5011
ID: 24330612
What about a CASE statement?
0
 
LVL 35

Expert Comment

by:johnsone
ID: 24330690
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
0
 
LVL 35

Accepted Solution

by:
johnsone earned 400 total points
ID: 24330694
Misspelled trunc once.

Select
O261454."Admission_Date",
O261454."LOS",
case when trunc(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
0
 

Author Comment

by:Luis5011
ID: 24330743
Now I'm getting an inconsistent data type error.  Nothing makes this thing happy!
0
 
LVL 35

Expert Comment

by:johnsone
ID: 24330849
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
0
 

Author Comment

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

0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question