SQL & PL/SQL :: Search B If Not In A - But Only Use Results From A If In Both Tables?
Nov 11, 2010
I'm trying to select from table "A" where value1 and value2 match. If the values are not in table "A", try table "B". If the values exist in BOTH tables - only look at the results from table "A".
Table "A" may or may not have "new" data coming into the system.
Table "B" may or may not have "existing" data.
The code I am writing needs to find the "newest" data row for value1 and value2. Eventually the data in table "A" gets "Merged" into table "B" further on in the process.
Supposedly this can be done in a single query using a left outer join and the NVL function, but the person I inherited this from isn't available.
I've been able to get the row back if it exists in "A" or "B", but end up with two rows or no rows if the row exists in both... Arrrg...
I would need a SQL script or a command for PLSQL that would search for a keyword %keyword% in all tables of a database instance and in a sepcified schema of a user.
how to do this interactively, without preparing a SQL script with all exisiting tables?
How can I search in Nested Tables ex: (pr_travel_date_range,pr_bo_arr) using the SQL below and insert the result into a new Nested Table: ex:g_splited_range_arr.
Here are the DDL and DML SQLs;*Don't worry about the NUMBER( 8 )*
CREATE OR REPLACE TYPE DATE_RANGE IS OBJECT ( start_date NUMBER( 8 ), end_date NUMBER( 8 ) ); CREATE OR REPLACE TYPE DATE_RANGE_ARR IS TABLE OF DATE_RANGE; DECLARE g_splited_range_arr DATE_RANGE_ARR := DATE_RANGE_ARR( );
[code]...
Or can I create a VIEW with parameters of Nested Tables in it so I can simply call
SELECT * BULK COLLECT INTO g_splited_range_arr FROM view_split_date(g_travel_range,g_bo_arr);
I'm trying to leave out results of a query that match certain conditions, basically I'm looking at two tables that each have a status field. If the status of both is equal to zero then I want the result to be left out of the search results. I'm struggling with the logic though. In the where clause I've tried:
(ACCO.STATUS != '0' AND CCO.STATUS != '0')
this, however, omits everything where the status combination isn't 1 and 1. I've also tried: ( (ACCO.STATUS = '1' AND CCO.STATUS = '1') OR (ACCO.STATUS = '1' AND CCO.STATUS = '0') OR (ACCO.STATUS = '0' AND CCO.STATUS = '1') )
with Courses as ( select 100 courseID from dual union all select 200 from dual union all select 300 from dual union all select 400 from dual union all select 500 from dual ), Assigns as (
[code]...
I need each assignment to get a color assigned to it according to it's courseID. So my theory is that I'd have a temporary table with a list of colors; each courseID would get assigned a color but my question is how do I assign each courseID a color without anything to join the temporary table on? Once each courseID has a color, I'd join in Assigns so that each assignment has a color associated with it according to it's courseID.
--Color table with 6 colors with Colors as ( select 'red' color from dual union all select 'blue' from dual union all select 'yellow' from dual union all select 'green' from dual union all select 'purple' from dual union all select 'teal' from dual )
I want to create a block of code that would search in all tables in a schema for a column_name where its data_length is like 4000 let's say. This data_length is actually dedicated for a comment column. If found, all not null column with like 4000 data_length will be changed by string "Comment has been removed". I have the following script below but it seemed lacking.
begin for rec in (select table_name, column_name from user_tab_columns where column_name like ?data_length? = 4000 order by 1,2) loop begin execute immediate 'update '||rec.table_name||' set '||rec.column_name||' = ''Comment has been removed'' where '||rec.column_name||' is not null'; commit; [code]........
I am using apex 4.1. I must hide phone number columns in my IR report, but at the same time the values of that columns should be available to search for using IR Search Field. is there a way to do this ?
if not, that means I have to :
1- Add a text filed P1_PHONE 2- edit my report query to something similar to > Select * from Table where :P1_phone in (mobile1,mobile2) or :p1_phone is null 3- add button to refresh the report.
but the item P1_PHONE should be on the header of the Report region. is there a way to do this. I am using theme 23 page template without sidebars Report template : Reports Region.
how to put the item P1_PHONE on the tab of the page. Just similar to the Search item of in the Application Builder.
Which is better to use of Views and Materialized views so as to have good performance ? Also I need to search for a particular value in the view. Any function in sql to implement this ?
I'm wanting a query that will look up a named field and return all of the tables that it is held in. I've tried looking through manually but the database has hundreds of tables
i have a text field search_part1 in which i put the item for search another text field i send_by which contains items a single item comes in list many times.i have to find the item one by one.i have make this statement for seraching items
Declare typ varchar2(200); L_NAME VARCHAR2(50) := :BLOCK3.SEARCH_PART1; begin go_block('BLOCK3'); first_record; loop if :BLOCK3.SEND_BY = L_NAME then [code]....
bt the problem is that if a single items comes more than one time then it search only one time and next time it exit.
i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?
I have an application which deploys the data to Oracle database. It has more than 25 tables and many columns. It does not have any document explaining the deployment, so I am kind of doing reverse engineering here.
I need a script which will fetch the column name or at least table names which will match with either some string or number? I found few examples on net to find out number. But I am struggling to make it work for string.
I can not work on stored proc, as I do not have access to create that on server. So, any script will work.
I have a table emp where empid,employee name,job,salary are three columns.I need to retrieve empid,count of emp,emp name,emp salary of job = manager and total salary of all employees with grouping by job in one query.
i hav a table in database with name TBH.in that table there are 25 to 30 columns.its a material details table.by using add material form i insert all the details of the material in the table.2 colums have primary key(material id and material code).
now iam making search and update form.on material code item i have WHEN-MOUSE-CLICK-TRIGGER and calling LOV and displaying record.
but the problem is when iam making changes in some fields its giving error ...UNABLE TO INSERT....FRM-40508:ORACLE error:unable to insert record.
How can I do a selective search on "description" column for any word that starts with the "$" sign? In the end, this is the resultset that I am expecting:
I ran this following query and somehow i feel the results are wrong.
SQL> select to_char(starttime,'dd-mm-YYYY hh24:mi:ss') from report where dateofmonth between to_timestamp_tz('22-Apr-2013 12:00:00','dd-mm-YYYY hh24:mi:ss') and to_timestamp_tz ('23-Apr-2013 14:00:00','dd-mm-YYYY hh24:mi:ss');
SQL> select to_timestamp_tz(starttime,'dd-mm-YYYY hh24:mi:ss') from report where dateofmonth between to_timestamp_tz('22-Apr-2013 12:00:00','dd-mm-YYYY hh24:mi:ss') and to_timestamp_tz ('23-Apr-2013 14:00:00','dd-mm-YYYY hh24:mi:ss');
We are having this problem of entering duplicated inventory parts in the system which physically refers to the same part. Need to create a select query to search through the list of inventory parts to find out existing parts with similar names.
Requirement is to have a search on a specific character set and it should list even records with 50% of the characters they have entered. Characters can be anywhere in the part name with any case.
For example: input of 'abcd1234' should return the parts with the following name... A nut 234 R49SE B12 A22Hub 156 65 a2 But not... abc 89998AK 47Xbox 360Mauser K98b
I want to filter only 123455 and 123456 from this list, is it possible to get a range as i want to use it as report parameters , i can do it by using like operator as below
create table prd_mast ( prd_cd varchar2(12), prd_desc varchar2(3000)); insert into prd_mast values ('1','arif,123455,123456,77777'); insert into prd_mast values ('2','sait,123455,123456,66666');
I have table having a single column a, which contain the values "HP Laptop", can search it in either way , i mean to say either user input the HP Laptop or Laptop HP? is it possible via SQL query ?
I am testing a supposedly very simple stored procedure in XE. It compiles with no errors but when I enter the following command in the SQL Command window I get an ORA-00911: invalid character.
call get_all_customer_orgs;
The get_all_customer_orgs procedure is defined as.
create or replace procedure get_all_customer_orgs (p_recordset out sys_refcursor) is begin open p_recordset for select * from customerorgs; end get_all_customer_orgs;
I don't understand what the invalid character could be.
I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.
lets see .. so a count of records where widget = widget or something along those lines.
I would like to give back to the our application user a page of results for a given query along with the total result count, something like: "Showing 1-25 of 650 total results".
Currently I am doing this by submitting a second query:
select count(*) from (<previous query criteria>)
Is there a better performing approach I could be using?
I am trying to create a query that displays the given error message if the result of my COUNT(*) is smaller than 1, but displays the result of my first query (data) if the total count is bigger than 1 (read: the query found data, so it needs to display the rows according to the search).
What do I need to do to display 'data' if 'data2' contains rows?
WITH data AS (SELECT a.order_id, a.session_id, a.log_id, b.date_of_order, a.operation, b.funct_prod_code, b.sts_status_code, b.ost_order_situation_code, c.order_situation_oms,
