How to implement these validations using SQL query

Question

0.00/5 (No votes)

See more:

I have a scenario Today where I am stucked Up little bit. I have data like this below in my Source Oracle Source Table(Sample Data)

ID,NAME,SALARY,BIRTHDAY
1,ABHIJIT,2000,17/12/1990
2,ROHIT,-2000,13/11/1988
3,MOHIT,500,2075-575-43

Now Salary in the 2nd Row is negetive and BIRTHDAY in the 3rd Row is an Invalid Format(Valid is MM/dd/yyyy). Both 2nd and 3rd Row should go to INVALID_EMPLOYEE and 1st record should go to VALID_EMPLOYEE.

In the Source File Date Format is coming as dd/mm/yyyy. Which I have to convert to MM/dd/yyyy format and also have to check whether Date Format incoming in Source File is dd/mm/yyyy or not.

Salary should not be less than 0. Source Table All columns are in String and in Target Table ID is Integer,NAME as VARCHAR2(255), SALARY is NUMBER and BIRTHDAY is DATE.

I have handled all this in my Project ETL Tool. So I am trying to push all this in Query to improve performance.Any help is much appreciated

What I have tried:

I have done already those using my ETL.

Posted 17-May-18 0:56am

Abhijit Ghosh

Updated 17-May-18 3:13am

CHill60

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-05-17T01:25:00

Don't try to push this to the DB - it almost certainly won't improve performance, but actually make it worse, because you need to set up a round trip to the DB to validate it, and that's going to be slower than doing validations locally when you insert values.

In particular, your date is a problem, because it shouldn't be possible to get an invalid date as far as the DB - all your queries should be using parameterised queries which transfer a validated DateTime object - the only way to get a "bad date" to the DB is to pass it as a string, and that implies string concatenation which leaves your whole system vulnerable to SQL Injection problems which can damage your whole DB.

And then there is the separation of concerns POV: a negative salary is a business layer concern, not a DB concern, and again, should be validated and dealt with at the business or even presentation layer, not fed to the data layer, by which time it's too late to do much about it.

I think you need to rethink this - and probably a fair amount of the rest of your application if SQL Injection is possible.