We help IT Professionals succeed at work.

join actual table rows based on the column

sam_2012
sam_2012 asked
on
105 Views
Last Modified: 2017-03-07
I have an requirement, I have the below table , I need to write a generic query or a procedure which will join the actual table with the dba_tab_cols table and provide me the data against the column names

what Iam trying to achieve is

Actual Table
T_data
ID        name
1           abc
2          xyz


target table
id 1
id 2
name abc
name xyz

any help in this regard is really appreciated
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Just take the code from the other question and instead of returning the length, just return the value?

https://www.experts-exchange.com/questions/29006436/join-a-table-with-user-tab-columns-in-oracle.html

You will have problems with data types like dates and numbers.  You'll need to convert them to string values.

Author

Commented:
any other alternative solution other than the one mentioned in the solution
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
There are almost always many ways to perform a task.  What other ways are you thinking?

I'm sure I could write a lot of PL/SQL code to pretty much perform the same thing as the select.

Author

Commented:
any help on the plsql code will be  fine.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Why do you want to write code to do the same thing a query will do?

If you want to do loops like some of your other recent questions, just incorporate the select form the other question into the loops.

Author

Commented:
ok , my objective is , I want to write a code which will check for a list columns  is having null or not , check if the columns having valid  numbers
then I need to update the staging status like below . To achieve this iam trying to the column names , so that I can achieve with a single simple update statement
This table has some additional fields like status , errorcode and errordesc , I need to validate the fields and update these columns .
Can this be achieved using sql statement without complex case statements or pipeline function can achive this

Stagingtable
id               name        date                     status            error_Code         error_desc
1                   xyz             01-01-2011     NULL              NULL                   NULL
NULL          abc               01-01-2011    E                   M                           MISSING FIELDS: 'ID'
2                  NULL             NULL            E                    M                            MISSING FIELDS: 'ID , name'

Author

Commented:
sorry the value in the date column  should be null
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
This is the logic

      CREATE TABLE TEMP_DATA
      as
      Select *
      from
              (
                              select rowid rid, 'ID' column_name, length(ID) column_len, profile_id column_value , 1 column_no from staging_data
                              UNION ALL
                              select rowid rid, 'Name' column_name, length(Name) column_len, name column_value , 2 column_no from staging_data
          UNION ALL
          select rowid rid, 'create_date' column_name, length(create_Date) column_len, create_date column_value , 3 column_no from staging_data
          UNION ALL
          select rowid rid, 'Total_Sales' column_name, length(total_sales) column_len, total_sales column_value , 4 column_no from staging_data
               )



      Update
      staging_data a
      set
      STATUS ='E',
      ERROR_CODE='REQ-MSNG'
      ERROR_DESC=Select rtim(',', concat('Missing Field:'||column_name ||',')) from TEMP_DATA b where b.rowid=a.rowid and    b.column_no=a.column_no)  
      where a.id is null or  a.name is null or a.create_date is null or a.total_sales is null;
     

but update is not working

Author

Commented:
I have around 80 columns , if I write the code to check for all those 80 columns , the code length will become more , there is a chance that I will miss out one of the columns hence I want to achive it using simple sql statements
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>I want to achive it using simple sql statements

Sorry but I don't think the code required to do what you want will ever be considered "simple".

Author

Commented:
will pipeline function help me in this scenario
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Code is code.  There is no "magic" to do what you are trying to do.

As I mentioned above, I'm not sure how a pipelined function will help.  What are you thinking they might help with?

Author

Commented:
1. No of if conditions for each column , today I have 80 , tomorrow I might have 150 columns , then I cannot have 150 if conditions in the code
2. I can use the pipeline function as a virtual table which will help me to filter the records that have errors give me the output as
id
error_Code
error_Desc
status
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Only speaking on the pipelined function aspect:
You have the error values stored in columns in the table so I'm not sure a pipelined function gives you anything a simple query of those columns cannot.

>>then I cannot have 150 if conditions in the code

I think we all understand what you are trying to achieve.  Can you do it, probably but as mentioned above:  It is a LOT of code and work to make sure it does everything you need it to do.

You've still never responded to my question about numbers being 'valid' and being able to be inserted.  That is just one example of the problems you'll have as you write the code.

Author

Commented:
the table may have around 10 lakh records , out of which 10 or 20 percentage of it may fail.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I'm not sure how the number of rows affects the project.  If you have 1 row or 100 million rows, the effort to do the validation is the same.  The only thing that changes is the actual run-time.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
when you say "10 lakh"  do you mean 1 million?

Author

Commented:
ys

Author

Commented:
iam putting roughly count , it may vary for each data load into the staging table

Author

Commented:
Some times 10 , 000 , 1,00000 , 1000000 etc
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Again, I don't see how the number of records matters.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
yes, the number of rows doesn't change the functionality

I was asking simply for clarification, EE is an English-language site, so I wanted to be sure the information intended was conveyed.

Author

Commented:
awesome
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.