Home » SQL & PL/SQL » SQL & PL/SQL » Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i (oracle)
Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668300] |
Sat, 17 February 2018 06:43 |
|
hareesh996
Messages: 4 Registered: February 2018
|
Junior Member |
|
|
I am very much new to the DB world, so wanted to review whether I am following right approach or not. I have two tables, table A --> is a table with 40 columns table B --> is a table with the 2 columns (each column of table A is represented as row in this table.)
Example:
A:
column_1 | column_2 | column_3 ......... | column_40
-----------------------------------------------------------
value1_1 | value1_2 | value1_3...... | value1_40
B:
column_name |column_value | column_errorKey
----------------------------------------------------
column_1 | value1_1 | value1_1_errorKey
column_2 | value1_2 | value1_2_errorKey
What am I doing?
Validate each value of a row from table A and insert into the table B with its value, error key and corresponding column name.
My PL SQL code is as below for, (Note: SQL code has considered only two columns to minimize the code here)
INSERT WHEN (LENGTH(column_1) <=7) THEN
into table_B values(
'column_1',column_1,'NoError')
WHEN (LENGTH(column_1) >7) THEN
into table_B values(
'column_1',column_1,'invalidLength')
WHEN (LENGTH(column_2) <= 75) THEN
into table_B values(
'column_2',column_2,'NoError')
WHEN (LENGTH(column_2) > 75) THEN
into table_B values(
'column_2',column_2,'invalidLength')
( select column_1,column_2,...,column_40
from table_A );
The validation that is happening within When the condition has only one validation but we have more validation like this for the value of each cell. I wanted to know is I am in the right approach or is another better way we have.
|
|
|
|
|
|
|
Re: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668309 is a reply to message #668300] |
Sat, 17 February 2018 11:04 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The term "cell" is really not applicable to a relational database. An RDBMS manages tables of rows (or "tuples" if you want to be precise) consisting of columns.
You seem to be processing table A such that each column of each row becomes its own row in table B. Is that right? So if table A has one million rows of forty columns, table B will end up with forty million rows? If that is what you are doing (I shall not ask why!) you will need to add a column to your table B: some sort of row identifier, so that that you can tell which forty rows in table B were generated from any row in table A.
|
|
|
|
|
Re: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i [message #668341 is a reply to message #668331] |
Mon, 19 February 2018 03:02 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
Just in case you insist in doing it in your DB:
First lets prepare some test data (because you did not provide some:
WITH
TESTDATA(COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5, COLUMN_6) AS
(SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL
UNION ALL
SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL
UNION ALL
SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL
UNION ALL
SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL
UNION ALL
SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL
UNION ALL
SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL
UNION ALL
SELECT DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL)
SELECT *
FROM TESTDATA
This statement generates random Strings each time you execute it. Now I can use the "SELECT * FROM TESTDATA" part to explain how it works.
First you need to unpivot the data:SELECT *
FROM TESTDATA
UNPIVOT
(COLUMN_VALUE FOR COLUMN_NAME IN ("COLUMN_1", "COLUMN_2", "COLUMN_3", "COLUMN_4", "COLUMN_5", "COLUMN_6"))
You can do pivot and unpivot in excel too, so I assume you are familiar with the concept.
Now, that you have for each "Cell" a seperate row you can apply the check rules.SELECT COLUMN_NAME
,COLUMN_VALUE
,CASE
WHEN COLUMN_NAME = 'COLUMN_1' THEN
CASE WHEN (LENGTH(COLUMN_VALUE) <= 7) THEN 'NoError' ELSE 'invalidLength' END
WHEN COLUMN_NAME = 'COLUMN_2' THEN
CASE WHEN (LENGTH(COLUMN_VALUE) <= 75) THEN 'NoError' ELSE 'invalidLength' END
WHEN COLUMN_NAME = 'COLUMN_3' THEN
CASE WHEN (LENGTH(COLUMN_VALUE) <= 20) THEN 'NoError' ELSE 'invalidLength' END
WHEN COLUMN_NAME = 'COLUMN_4' THEN
CASE WHEN (LENGTH(COLUMN_VALUE) <= 3) THEN 'NoError' ELSE 'invalidLength' END
WHEN COLUMN_NAME = 'COLUMN_5' THEN
CASE WHEN (LENGTH(COLUMN_VALUE) <= 12) THEN 'NoError' ELSE 'invalidLength' END
WHEN COLUMN_NAME = 'COLUMN_6' THEN
CASE WHEN (LENGTH(COLUMN_VALUE) <= 9) THEN 'NoError' ELSE 'invalidLength' END
END
AS COLUMN_ERRORKEY
FROM TESTDATA
UNPIVOT
(COLUMN_VALUE FOR COLUMN_NAME IN ("COLUMN_1", "COLUMN_2", "COLUMN_3", "COLUMN_4", "COLUMN_5", "COLUMN_6"))
As you see, you can not exactly determine which row is giving an error. So I insert a row identifier (if you follow John Watsons advice to go through some basic tutorials you'll learn that these identifiers are called "primary keys"). In the final statement I include some. So You can figure out (by referencing the Identifier and the column name) which "cell" is malicious.WITH
TESTDATA(PRIMARY_KEY, COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5, COLUMN_6) AS
(SELECT 1
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL
UNION ALL
SELECT 2
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL
UNION ALL
SELECT 3
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL
UNION ALL
SELECT 4
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL
UNION ALL
SELECT 5
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL
UNION ALL
SELECT 6
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL
UNION ALL
SELECT 7
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
,DBMS_RANDOM.STRING('U', DBMS_RANDOM.VALUE(1, 20))
FROM DUAL)
SELECT PRIMARY_KEY AS TESTDATA_ID
,COLUMN_NAME
,COLUMN_VALUE
,CASE
WHEN COLUMN_NAME = 'COLUMN_1' THEN
CASE WHEN (LENGTH(COLUMN_VALUE) <= 7) THEN 'NoError' ELSE 'invalidLength' END
WHEN COLUMN_NAME = 'COLUMN_2' THEN
CASE WHEN (LENGTH(COLUMN_VALUE) <= 75) THEN 'NoError' ELSE 'invalidLength' END
WHEN COLUMN_NAME = 'COLUMN_3' THEN
CASE WHEN (LENGTH(COLUMN_VALUE) <= 20) THEN 'NoError' ELSE 'invalidLength' END
WHEN COLUMN_NAME = 'COLUMN_4' THEN
CASE WHEN (LENGTH(COLUMN_VALUE) <= 3) THEN 'NoError' ELSE 'invalidLength' END
WHEN COLUMN_NAME = 'COLUMN_5' THEN
CASE WHEN (LENGTH(COLUMN_VALUE) <= 12) THEN 'NoError' ELSE 'invalidLength' END
WHEN COLUMN_NAME = 'COLUMN_6' THEN
CASE WHEN (LENGTH(COLUMN_VALUE) <= 9) THEN 'NoError' ELSE 'invalidLength' END
END
AS COLUMN_ERRORKEY
FROM TESTDATA
UNPIVOT
(COLUMN_VALUE FOR COLUMN_NAME IN ("COLUMN_1", "COLUMN_2", "COLUMN_3", "COLUMN_4", "COLUMN_5", "COLUMN_6"))
Now you only need to INSERT these values in your "Table B". I'm confident that you can google this.
Welcome to the DB world. The learning curve is steep, but you won't regret it.
[Updated on: Mon, 19 February 2018 03:06] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:07:33 CDT 2024
|