Solved

Case Statement with less than or greater than date

Posted on 2013-10-24
7
588 Views
Last Modified: 2013-10-29
Hello,

I am having trouble with the statement below.  It was working fine before I added the date portions  trunc(pay.dos) < '04/01/13' .

(case 
when trunc(pay.dos) < '04/01/13' and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is not null THEN mfs.ruralfee*.8
when trunc(pay.dos) < '04/01/13' and bbd.procedure in ('A0435','A0436') and zip.ruralind is not null THEN mfs.ruralfee*bbd.units*.8
when trunc(pay.dos) < '04/01/13' and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is null THEN mfs.urbanfee*.8
when trunc(pay.dos) < '04/01/13' and bbd.procedure in ('A0435','A0436','A0425') and zip.ruralind is null THEN mfs.urbanfee*bbd.units*.8
when trunc(pay.dos) < '04/01/13' and bbd.procedure in ('A0425') and bbd.units > 17 and zip.ruralind is not null THEN (mfs.ruralgrmil1_17 *17 + (bbd.units-17)*mfs.ruralfee)*.8
when trunc(pay.dos) < '04/01/13' and bbd.procedure in ('A0425') and bbd.units <= 17 and zip.ruralind is not null THEN mfs.ruralgrmil1_17 * bbd.units*.8
when trunc(pay.dos) >= '04/01/13' and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is not null THEN mfs.ruralfee*.784
when trunc(pay.dos) >= '04/01/13' and bbd.procedure in ('A0435','A0436') and zip.ruralind is not null THEN mfs.ruralfee*bbd.units*.784
when trunc(pay.dos) >= '04/01/13' and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is null THEN mfs.urbanfee*.784
when trunc(pay.dos) >= '04/01/13' and bbd.procedure in ('A0435','A0436','A0425') and zip.ruralind is null THEN mfs.urbanfee*bbd.units*.784
when trunc(pay.dos) >= '04/01/13' and bbd.procedure in ('A0425') and bbd.units > 17 and zip.ruralind is not null THEN (mfs.ruralgrmil1_17 *17 + (bbd.units-17)*mfs.ruralfee)*.784
when trunc(pay.dos) >= '04/01/13' and bbd.procedure in ('A0425') and bbd.units <= 17 and zip.ruralind is not null THEN mfs.ruralgrmil1_17 * bbd.units*.784
end) MCRPortion,

Open in new window

0
Comment
Question by:jvera524
7 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
If pay.dos is a date you may need to explicitly convert your string to a date:

to_date('04/01/13','MM/DD/YY')

If that isn't it, what problems is it causing?  Errors, incorrect results, what?
0
 

Author Comment

by:jvera524
Comment Utility
pay.dos is a date field formatted as MM/DD/YY HH:MM:SS AM(PM)

I tried to_date(pay.dos,'MM/DD/YY') and this did not work either.  It states ORA-00198 I think.  I will get specifics.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Sorry but ORA-00198 is not a valid code.

At least based on the docs:
http://docs.oracle.com/cd/E11882_01/server.112/e17766/e0.htm

What is the exact code?

>>pay.dos is a date field formatted as MM/DD/YY HH:MM:SS AM(PM)

In Oracle dates don't have formats until they are displayed.  Then they are sort of converted to strings based on configuration settings.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:jvera524
Comment Utility
ORA-01898
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
Comment Utility
Based on the error code:
http://ora-01898.ora-code.com/

There is a date format issue.  This is common when letting Oracle do implicit string to date conversions or messing up the format mask given the string you provided.

There is a simple example to generate that error here:
http://www.dbmotive.com/ora-01898-too-many-precision-specifiers/

select trunc(sysdate, 'DDDD') from dual;

Double check that pay.dos is a date and not a 'string' in date format.

From sqlplus:
desc pay.dos


If that isn't it, the syntax error may be elsewhere in the statement.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 150 total points
Comment Utility
ORA-01898: too many precision specifiers
Cause:       While trying to truncate or round dates, extra data was found in the date format picture
Action:       Check the syntax of the date format picture and retry.

Please re-check carefully how you inserted the to_date() function into your code, I think you might have done something like this:

(case
when trunc(pay.dos) < to_date('04/01/13','MM/DD/YYYY') and ...

Note that the date string only contains 2 digits as the year,
but the "format picture" has 4 placeholders (YYYY) and is expecting digits for each one of those; this mismatch would produce that error message I believe

I would generally recommend always using 4 digit years, so the way I would go about solving this would be to change 13 to 2013 and ensure that all "format pictures" used YYYY
(case 
when trunc(pay.dos) < to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is not null THEN mfs.ruralfee*.8
when trunc(pay.dos) < to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0435','A0436') and zip.ruralind is not null THEN mfs.ruralfee*bbd.units*.8
when trunc(pay.dos) < to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is null THEN mfs.urbanfee*.8
when trunc(pay.dos) < to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0435','A0436','A0425') and zip.ruralind is null THEN mfs.urbanfee*bbd.units*.8
when trunc(pay.dos) < to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0425') and bbd.units > 17 and zip.ruralind is not null THEN (mfs.ruralgrmil1_17 *17 + (bbd.units-17)*mfs.ruralfee)*.8
when trunc(pay.dos) < to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0425') and bbd.units <= 17 and zip.ruralind is not null THEN mfs.ruralgrmil1_17 * bbd.units*.8
when trunc(pay.dos) >= to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is not null THEN mfs.ruralfee*.784
when trunc(pay.dos) >= to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0435','A0436') and zip.ruralind is not null THEN mfs.ruralfee*bbd.units*.784
when trunc(pay.dos) >= to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is null THEN mfs.urbanfee*.784
when trunc(pay.dos) >= to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0435','A0436','A0425') and zip.ruralind is null THEN mfs.urbanfee*bbd.units*.784
when trunc(pay.dos) >= to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0425') and bbd.units > 17 and zip.ruralind is not null THEN (mfs.ruralgrmil1_17 *17 + (bbd.units-17)*mfs.ruralfee)*.784
when trunc(pay.dos) >= to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0425') and bbd.units <= 17 and zip.ruralind is not null THEN mfs.ruralgrmil1_17 * bbd.units*.784
end) MCRPortion,

Open in new window

By the way, it confuses us when we hear "xyz is a date field stored in format ..."e.g.
>>"pay.dos is a date field formatted as MM/DD/YY HH:MM:SS AM(PM)"

IF  pay.dos is a date field or a timestamp field, then "format" is not relevant to storage
(date/timestamp information is NOT stored in a format at all,
they are stored in a binary manner made-up of numbers,
"format" is only required by humans so that we can input them and read them at output)

IF pay.dos was a string field (e.g. varchar/varchar2) THEN format really does matter

& the confusion is we then don't know if we need to advise for a date or for a string.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Can you do a describe of the pay table so we can see the datatype of the dos attribute?
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

771 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

9 Experts available now in Live!

Get 1:1 Help Now