Oracle Database Trying to search through a series of tables for a number or varchar
(eventually want to search for string or text as well later on)
The following code runs without errors but does not return results. This code was originally designed to try to search through a database for a set number of tables listed. in the select below. The value "14461.32" is in the table Job_Expenses.Total_Cost. Below this code is an image that proves the data is there I am just trying to figure out why it is not getting the results I thought it would. It seems to work here on SQL Fiddle Test Site
Select * From JAS.JOBS;
Select * From JAS.JOB_CONTACTS;
Select * From JAS.JOB_DETAILS;
Select * From JAS.JOB_DETAIL_REC_TYPES;
Select * From JAS.JOB_EXPENSES;
SELECT 'SELECT '''|| main.TABLE_NAME ||''' AS Source, t.* FROM '
||' t WHERE '
CASE WHEN main.DATA_TYPE = 'VARCHAR2' AND val.IsValueNumeric = 0 THEN main.COLUMN_NAME || ' = ''' || val.SearchValue || ''''
WHEN main.DATA_TYPE = 'NUMBER' AND val.IsValueNumeric = 1 THEN main.COLUMN_NAME || ' = ' || val.SearchValue
, ' OR ' ) WITHIN GROUP (ORDER BY TABLE_NAME)
FROM USER_TAB_COLUMNS main
' AS SearchValue
, CASE WHEN trim(TRANSLATE( '14461.32' , '0123456789-,.' , ' ')) IS NULL
END AS IsValueNumeric
WHERE main.TABLE_NAME IN ( 'JAS.JOBS, JAS.JOB_CONTACTS , JAS.JOB_DETAILS, JAS.JOB_DETAIL_REC_TYPES, JAS.JOB_EXPENSES,' )
AND main.DATA_TYPE IN ( 'VARCHAR2','NUMBER' )
GROUP BY main.TABLE_NAME;
The query directly above simply seems to pull a small sampling of records from the 5 tables above. Nothing more.
Here is proof the record is there but the above code just does not seem to find it. See image attached.