Forum Stats

  • 3,782,589 Users
  • 2,254,669 Discussions
  • 7,880,131 Comments

Discussions

Data validations

user-caintegra
user-caintegra Member Posts: 26 Blue Ribbon
edited Apr 20, 2015 2:51PM in SQL & PL/SQL

Hi I kind of newbie in this stuff, I have a situation I'd like to be helped.

I have a table A with columns name, id, phone, etc.. I need to make validation for earch columns in order to prove tha the data exist in another table B with reference information, I need to do this in order to send the data from table A to a new table C.

For example, In table A in row 1 in the column name there is a "John" , column id "12345", column phone "0180012345". I need to check in column B if John exists, if it doesn´t exist then stop the next validations and display tha John doesn´t exist in Table B, When "John" be corrected, the first validation will be passed with no problem so the next validation can be released and it goes the same situation: check if the "123455" from table A exists in the ID column of Table B and if it doesn´t the stop the next validations and so on...The point is: I need to check that all data from table A exist in table B and they pass all the validations in order to be fill in the new table C.

I have the validations in separate ways, I know I need to create a package with all the validations, but I don´t know how to make a "break" in one validation and STOP the next validation and display all the records from the column that don´t comply with the validation.

I hope I can be supported with this matter.

John

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,401 Red Diamond
    edited Apr 20, 2015 2:51PM

    Hi, John,

    Sorry, it's not clear what you want.

    You may not need any PL/SQL at all: a SQL INSERT statement can check for referential integrity, and, if some rows fail, it can still INSERT the valid rows.

    Whenever you have a problem, please post a complete test script that the people who want to help you can run to re-create the problem and test their ideas.  In this case, include CREATE TABLE and INSERT statements for all the tables involved, including the look-up tables, to show what they are before table C gets populated.  Also post what you want table C to look like after it's populated.  Explain why each row of A did or did not get copied into C.

    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

    See the forum FAQ: 

This discussion has been closed.