Solved

PLSQL Developer: Proper syntax for an IF statement

Posted on 2009-05-07
12
214 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
DevOps Toolchain Recommendations

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

 

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
 

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

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ā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

778 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