Solved

PLSQL Developer: Proper syntax for an IF statement

Posted on 2009-05-07
12
217 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
Stressed Out?

Watch some penguins on the livecam!

 

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

Technology Partners: 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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

724 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