1

I'm programming a Java software that uses an Oracle Database for storage. I can probably best describe it as an audit trail (?) of sorts, recording what my test audience did, on what date, and on what computer. These are all manual input, so the people entered this information manually, which is then saved onto the database, and then view-able by the administrator.

Here's the SQL statement I use for retrieving the data:

SELECT fld 1 as EmpNumber, 
fld2 as EmpName, 
fld6 as Date
FROM tblReportTemp  
LEFT JOIN tblPersonalInfo b ON $P{hrStoreOp}= b.ID_PERSONAL
WHERE computerName = $P{COMPNAME}
ORDER BY fld6,fld2 asc

My team mate decided to make all the fields a VARCHAR type, and then decided to have the users type in the date, as opposed to, using a date picker. So a lot of test users, the lazy ones, simply put in whatever it is that's easiest to reach, like Q, or 1, or even space. So first of all, it won't arrange properly by date, and second, my attempts to convert the data into a date via TO_DATE(fld6, 'MM/dd/yy') before arranging it has been met with failure, since it can't convert 'Q' to a date.

I've since replaced the textbox with a date picker, however, we are not allowed to modify the database.

So my proposed solution is to modify my SQL statement above to filter out those that can't be converted to date. Is there an SQL query for Oracle that can do this?

Thanks.

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
zack_falcon
  • 4,186
  • 20
  • 62
  • 108
  • My personal suggestion would be always use controls like date picker, which prevents user from entering invalid dates and also reduces your effort of checking like whether it is a date or not etc etc.. – Pradeep Simha Apr 26 '13 at 03:28
  • You can use a date picker if you want, but the best defense is to make the column a `DATE` type. A date picker will take care of dates entered on the page that has the date picker, but that's not the only way to get date values into the database. – Ed Gibbs Apr 26 '13 at 03:31
  • Yes, well, I've already modified that part of the module. Every thing the user enters from now on is a Date. However, as I've said, the database can't be modified, as per our instructor's instructions, since "in the real world, you can't modify the database of companies, etc. etc.,". We'll have to live with our mistake and think of a way around it. – zack_falcon Apr 26 '13 at 03:38
  • @zack_falcon - there's no neat way to do this. A regex could screen for ##/##/## but unless it's *really* involved it won't handle things like "month = 13" or "2/31/2015". Since this is an assignment rather than a "real world" application, write an Oracle user-defined function to convert the date, and have it return NULL if there's a problem converting. Let me know if you need help with that. Also, your instructor is wrong about the real world: companies have applications to support their business, and if the app's database doesn't support the business it's changed. – Ed Gibbs Apr 26 '13 at 03:46
  • 2
    See http://stackoverflow.com/questions/5966274/how-to-handle-to-date-exceptions-in-a-select-statment-to-ignore-those-rows – PM 77-1 Apr 26 '13 at 03:51
  • Zack: definitely look at the link @PM77-1 provided above. The code and advice from the accepted answer will solve your problem. – Ed Gibbs Apr 26 '13 at 04:03
  • Be careful implementing the solution from the link that @PM77-1 provided. There is the possibility of causing a full table scan. – Steven Apr 26 '13 at 04:17

2 Answers2

1

First, slap your team mate about the head for storing dates in a varchar2 column.

Secondly, write a PL/SQL Pipelined Function to filter out the non-dates rows. I don't think there is an easy way to do this with plain SQL.

Basically pipelined functions allow you to perform programmatic operations on one or more data sets, returning custom PL/SQL collection objects as table rows. These functions are then able to be used in a SELECT statement:

SELECT col1, col2, col3, ...
FROM TABLE(my_pipelined_function(param1, param2, param3, ...))

Further details on these functions (including example code) can be found here.

Steven
  • 1,564
  • 1
  • 22
  • 34
  • 1
    Steven - I think a pipelined function may be overkill here. A UDF such as `ConvertIdiotTeammateDate(string)` would work OK, especially as this is just an assignment. Then @zack_falcon can just call the function from the query. The function could try to convert to date, and if it works return the date or if it throws return null. Also, I think slapping's too good for the teammate :) – Ed Gibbs Apr 26 '13 at 03:50
1

You can use a function to do the conversion and discard bad data as NULL:

CREATE OR REPLACE FUNCTION my_to_date(p_date_string IN VARCHAR2) RETURNS DATE
IS
BEGIN
  RETURN TO_DATE(p_date_string, 'MM/DD/YY');
EXCEPTION WHEN OTHERS THEN
  RETURN NULL;
END;

After creation use it:

SELECT some_column
     , my_to_date(my_date_string_column) as real_date
FROM some_table
WHERE ...

Also, you really should change the data type to a DATE and force the user's to input only valid dates. Based on the column names in your question looks like your coworker is trying to use generic "flex fields" for everything. That's usually a really bad idea unless you really plan on storing arbitrary types of data in the same row (which is also usually a bad idea).

sehrope
  • 1,777
  • 13
  • 16