Solved

PLSQL Developer: Proper syntax for an IF statement

Posted on 2009-05-07
12
213 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
  • 6
  • 6
12 Comments
 
LVL 34

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 34

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
 

Author Comment

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

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

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

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 34

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 34

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

Suggested Solutions

Title # Comments Views Activity
grouping on time windows 6 43
How to base a filter depending on fields contents? 15 48
Oracle Listener Not Starting 11 25
Update data using formula 22 21
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.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now