?
Solved

PLSQL Developer: Proper syntax for an IF statement

Posted on 2009-05-07
12
Medium Priority
?
218 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
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 

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 1600 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

741 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