SQL & PL/SQL :: Query For Eliminating Union

Apr 24, 2011

I have one scenario in which i want to write the sql,but not able to write correct qry,

tables
1-emp
2-emp_hist
3-dept
4-dept_hist.

i want to retrieve data from the emp and hist able based on some conditions,but if data is not present in emp and dept tables.then fetch data from emp_hist and dept_hist.I have written below qry which is working fine,but my prob is i want to provide my emp_id only one place.
else i have to change my java code.

select * from emp,dept where emp.emp_id=dept.dept_id and emp_id=5
union
select * from emp_hist,dept_hist where emp_hist.emp_id=dept_hist.dept_id and emp_id=5

View 20 Replies


ADVERTISEMENT

SQL & PL/SQL :: How DISTINCT And UNION Eliminating NULL Values

May 3, 2012

As per NULL values concept

One NULL value is not equal to other NULL value.

But how DISTINCT and UNION eliminating NULL values.

Then how UNIQUE key constraint accepting more than one NULL value..

View 1 Replies View Related

SQL & PL/SQL :: Union Query Need To Add Extra Dummy Field To One Side Of Query

Dec 8, 2005

I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a

select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)

however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.

View 6 Replies View Related

SQL & PL/SQL :: Query Without Using A UNION?

Aug 17, 2011

I'm just trying to see if there is another way of doing this query without using a UNION. The only way I can see is using a UNION but maybe I'm missing something or a way to do it without a UNION.

Result:

Select any customer within the user_states table who lives in "MO"
Select any customer within the user_cars table who lives in "MO" and has a "White" car
Select any customer within the user_plates table who lives in "MO" and has a plate of "A" or "B"
Join to the user_names table to display the customers name

So, the result would be any customer who lives in "MO" who owns a "White" car and any customer who lives in "MO" who has a plate of "A" or "B".

SQL

select b.customer_id,
b.first_name,
b.last_name

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: Union Query For Oracle

Feb 29, 2012

I am new to SQL Oracle programming and have a question on a union query. I am trying to get results (example) for the following:

Org # Full_date Run_date
5 2/20/2012 2/20/2012
5 2/21/2012 2/21/2012
5 2/22/2012 null (there is not a record for this)
6 2/20/2012 2/20/2012
6 2/21/2012 null
6 2/22/2012 2/22/2012
7 2/20/2012 2/20/2012
7 2/21/2012 2/21/2012
7 2/22/2012 2/22/2012

The dw_time table would have the listing of all dates, (Full_date) and the dw_capacity_daily table would have the run_date. Here is my

select * from (
select distinct a.Organization_Nbr, d.full_date
from CMBHS_DW.DW_ORGANIZATION a, cmbhs_DW.DW_Organization_Identifier b, cmbhs_DW.DW_Contract c, cmbhs_dw.dw_time d
where a.ORGANIZATION_NBR = b.ORGANIZATION_NBR
[code]....

View 7 Replies View Related

SQL & PL/SQL :: Query Performance In Union

Aug 10, 2010

which one is better?

unloading 5 tables of same structure using a ETL tool then merging the data

using Union operator to unload 5 tables then do transformations in ETL tool

View 4 Replies View Related

Parallel Query Block Of Union All Set

Sep 24, 2010

We have very large table having data more than 1000 millions rows. We divide this table into four physical tables say A, B, C and D. The physical horizontal partition of data of this original table is done based upon their business policy.

Each partitioned table has contained data of particular business entity. Further each table has partition and sub partitions based upon business rule.

We have to retrieve data from all these tables as follows:

select a1, a2, a3, a4, a5, a6
from A
where < logical filter condition>
union all
select b1, b2, b3, b4, b5, b6
[code].....

We observed that above each query block execute in serial one after another and individual each query block capable to process data in parallel from respective table.

How does this above query able to execute each query block in parallel?

View 14 Replies View Related

Materialized Views Not Used With UNION ALL In Query

Oct 13, 2010

I am an Oracle newbie.

We have 2 fact tables and one lookup table in this structure:

FACTTABLE1 (C1_ID, C2, SALE)
FACTTABLE2 (C1_ID, C3, SALE)
LOOKUPTABLE (C1_ID, C1_NAME)

The DBAs have built 2 Materialized Views, which aggregates data in the fact tables at column C1 level

MAT_VIEW1 :SELECT C1_ID, SUM(SALE) SALES from FACTTABLE1 join LOOKUPTABLE on C1_ID
MAT_VIEW2: SELECT C1_ID, SUM(SALE) SALE from FACTTABLE2 join LOOKUPTABLE on C1_ID

We are using an old BI tool that can ONLY generate Inline Views in these formats.

CASE1:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE1)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME

CASE2:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(select C1_ID, null C2_ID, C3_ID, SALE from FACTTABLE2)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME

CASE3:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(
select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE1
union all
select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE2
)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME

Oracle 11g rewrites Case 1 and Case 2 to use the correct materialized views. But for case 3, it goes to the base fact tables 1 and 2. Is there a way to make oracle use the MVs even if there is a UNION ALL in the inline view? There is a 1:M Foreign Key relationship between LOOKUPTABLE.C1_ID and the 2 fact tables.

View 2 Replies View Related

SQL & PL/SQL :: Improve Query So That UNION Can Be Removed

Mar 6, 2012

select
a.empno,a.ename,a.job,
B.DNAME
from scott.emp a,scott.dept b
where ( a.ename like 'S%' and a.deptno=b.deptno)
union
select
a.empno,a.ename,a.job,
'aaa' AS DNAME
from scott.emp a,scott.dept b
where ( a.ename like 'S%' and a.job not like 'SALES%');

Output:

7369SMITHCLERKRESEARCH
7369SMITHCLERKaaa
7788SCOTTANALYSTRESEARCH
7788SCOTTANALYSTaaa

Quote:There is other way to improve the query so that UNION can be removed.

View 3 Replies View Related

Use Oracle Hint To Use Index In Query Which Uses UNION?

Mar 31, 2011

I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.

Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.

The select statements inside the inline view returns huge number of row (like 50 million rows).

The whole query fails with time out.

How can I optimize this query further?

Is there a way to pass Oracle Hints so that Oracle uses indexes?

View 4 Replies View Related

Reports & Discoverer :: How To Add Union To Report Query

Feb 16, 2012

I am making a report in hrd regarding gross, deduction and there difference. I hv an attribute in the table as indicator whose value is addition and deduction. i want the sum of both in two diff column in a single rep. i want the report dept wise.

but i m getting fatal error at run time. which i don't understand.

View 3 Replies View Related

Performance Tuning :: Can Use Oracle Hint To Use Index In A Query Which Uses UNION

Mar 31, 2011

I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.

Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.

The select statements inside the inline view returns huge number of row (like 50 million rows).

The whole query fails with time out.

Is there a way to pass Oracle Hints so that Oracle uses indexes?

View 1 Replies View Related

Reports & Discoverer :: SQL Union All - Query Block Has Incorrect Number Of Result Columns

Feb 1, 2012

I have the following Union All query. It throws the following error in SQL plus

ERROR at line 27: ORA-01789: query block has incorrect number of result columns

After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like

(Select distinct c.contact_code

Following is the SQL query

Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......

View 1 Replies View Related

SQL & PL/SQL :: Eliminating Timestamp From Third Party Input

Sep 13, 2012

I had third party reporting system,in which i could not avoid time selection in the screen. So if a user selets date and time or date alone .i should get only in date foarmat i.e(DD-MM-YYYY)

i had tried from third party tool liket0_char (P_date,'DD-MM-YYYY').But i get error ORA-01841: (full) year must be between -4713 and +9999, and not be 0

View 1 Replies View Related

SQL & PL/SQL :: View Of Item Hierarchy By Eliminating NULL Levels

Aug 22, 2013

Here is the table structure I will be speaking of below:

SELECT lvl_1_id, lvl_1_dsc,
lvl_2_id, lvl_2_dsc,
lvl_3_id, lvl_3_dsc,
lvl_4_id, lvl_4_dsc,
lvl_5_id, lvl_5_dsc,
lvl_6_id, lvl_6_dsc,
lvl_7_id, lvl_7_dsc,
lvl_8_id, lvl_8_dsc
FROM item_hier_tbl;

I have a table that mimics an hierarchy of items (parent-child relationship). The top level (level 1) never has a NULL value, but it is possible for any of the lower values to have a NULL value. For example, levels 1, 2, 5, 7, & 8 may have values, but 3, 4, & 6 may all be NULL.

I need a View that can return the same data, but with out NULL values. The View would move the NOT NULL values up the levels, so there is consecutive data on each level starting at level. So using the example, the View would move value in 5 up to 3, 7 up to 4, and 8 up to 5. Then level 6 to 8 would have NULL.

I have done this already by creating a 2nd table and using a stored proc to copy the data over to the new table they I want it. But I was told a View or Materialized view "may" work, and perform quicker during the queries. I looked at the Oracle functions LAG and LEAD, but by definition they work by rows, I need to work by columns on the same row.

LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

Is what I am asking possible in a View or Materialized View?

View 2 Replies View Related

SQL & PL/SQL :: Is It Better To Use UNION (or) UNION ALL With DISTINCT

Apr 22, 2010

Is it better to use UNION (or) UNION ALL with DISTINCT,Which one will improve performance.

Query1:

SELECT deptno FROM emp
UNION
SELECT deptno FROM dept

Query2:

SELECT DISTINCT * FROM(SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept)

I mean to say query1 is better (or) query2 is better,which query improves the performance.

View 1 Replies View Related

UNION ALL Vs 2 Queries

Nov 12, 2012

is there any difference between

- returning from the procedure 2 ref cursors containing result set of 2 queries
- returning from the procedure 1 ref cursor containing result set of that 2 queries as one (with UNION ALL)?

Will 2nd option be faster or similar to 1st?

View 1 Replies View Related

SQL & PL/SQL :: Union Run In Parallel

Sep 30, 2010

I have a query which has 5 unions, each clause of the union takes 1 hr to run and query results come back in 5 hrs, Is there any way I can make these clause to run in parallel?

View 13 Replies View Related

SQL & PL/SQL :: Union Condition?

Aug 23, 2012

I am facing an issue in union clause

Below is my query Issue faced is that based on union I want to limit the result and does not want second query to give duplicate result as both queries deal with same table

SELECT TAB1.ID, TAB1.CRNCY_CODE, TAB2.SCHEME_CODE, DECODE( 'INFENG',CAST( '' AS VARCHAR(20)),
NVL(TAB2.ALT1_SCHEME_DESC,TAB2.SCHEME_DESC),TAB2.SCHEME_DESC),
TAB1.SUB_HEAD_CODE, DECODE( 'INFENG' ,CAST( '' AS VARCHAR(20)),NVL(TAB1.ALT1_SUB_HEAD_DESC ,
TAB1.SUB_HEAD_DESC),TAB1.SUB_HEAD_DESC)

[code]...

select null ID,null crncy_code,TAB2.SCHEME_CODE ,
NVL(TAB2.ALT1_SCHEME_DESC,TAB2.SCHEME_DESC),TAB2.SCHEME_DESC,null SUB_HEAD_CODE from TAB2 where TAB2.SCHEME_TYPE ='SCHEME1';

[CODE]

View 4 Replies View Related

SQL & PL/SQL :: Using 2 Union Operator

Jun 28, 2010

I have an query i.e.

I want 3 lines input in 1 line using 2 union operator like

Input:-
'i love playing
football and
volleyball'

i want the output like:-

"i love playing football and volleyball"

solve query using 2 union operator?

View 9 Replies View Related

SQL & PL/SQL :: Union Of Two Tables?

Jul 1, 2010

how can i get a union of two pl/sql tables. ??

View 2 Replies View Related

PL/SQL :: Unpivot Or Union

Dec 11, 2012

Cant find any artice on what is better to use unpivot or union all. I can achive same results using either but cant decide what would be better. For example a table has columns:

Name 1, Phone1, Name 2, Phone 2. The result I want would be:

Name 1, Phone 1
Name 2, Phone 2.

View 5 Replies View Related

PL/SQL :: Order By With Union?

Aug 24, 2012

I have the following sql

select htf.escape_sc(su.sukey) a, htf.escape_sc(su.sukey) b
from udm_su su, udm_lde lde
where su.ldeid = lde.ldeid
and su.sukey in (select su_generic
 
[code]...

View 3 Replies View Related

Alternative To Union Of Two Different Table?

Dec 21, 2010

Is there any alternative to Union or Union all of two different table. Example would be great.

View 5 Replies View Related

SQL & PL/SQL :: Two Requests Instead Of Union All In Function?

May 24, 2011

I've got function with combined sql statement (union all), how do I split it into two sql requests? This is to avoid time increasing of sql executing. IMHO good solution is to write two functions, how do you think?

FUNCTION f_LintOVRDUAH
(mydate date,
cid number )

[Code]....

View 18 Replies View Related

SQL & PL/SQL :: Combine 2 Queries Into 1 Without Using UNION

Jun 5, 2012

SELECT b.KPCNO
,b.KPC_FULL_NAME
,min(c.time) in_time
FROM xxkpc_hr_personnel_v2 b
,xxkpc_fingerscan_data_v c
[code]........

View 5 Replies View Related

SQL & PL/SQL :: XML - Union Of 2 Select Queries

Jan 15, 2013

How I can get the union result of 2 queries and put them in xml result, but I want each query to be in seperate xml element. I don t want to put 1 single xmlelement and do a From then construct a virtual table uniting the 2 subqueries

I mean I don t want something like the following:

(Select
XMLAGG(
XMLELEMENT("credit",

[Code]...

Except the 2nd alternative is not working I get error message: "SQL command not properly ended"

View 4 Replies View Related

SQL & PL/SQL :: UNION While Selecting From Dual?

Sep 19, 2013

I would like to SELECT these 3 hardcoded titles from DUAL, and have a blank line under each, on the output in this order from the SQL. But the result does not end up that way

SQL> set heading off;
1 select '#ENCODING WINDOWS-1252' from dual
2 union
3 select ' ' from dual
4 union
5 select 'Language Section EN-US' from dual
6 union
7 select ' ' from dual
8 union
9* select 'Catalog Section Title Date Source' from dual
SQL> /

#ENCODING WINDOWS-1252
Catalog Section Title Date Source
Language Section EN-US

- - - - - - - - - - - - - - - - -

Desired Output:
#ENCODING WINDOWS-1252
Language Section EN-US
Catalog Section Title Date Source

View 12 Replies View Related

PL/SQL :: Table With Union All And Using Having Statement?

Sep 18, 2012

I am writing a query and I did get it to work but it shouldn't be this hard, I feel like I am doing something wrong, and there has to be a more elegent solution.

This query works:

SELECT a.d
FROM (
     SELECT S_ID a, LOOKUP_DESC d, S_CODE f
     FROM SSS
     JOIN LOOKUP ON S_CODE LIKE LOOKUP_CODE
     UNION ALL
  
[Code]...

I feel like I should be able to execute the query like this: This query doesn't work:

SELECT a.d
FROM (
     SELECT S_ID a, LOOKUP_DESC d, S_CODE f
     FROM SSS
     JOIN LOOKUP ON S_CODE LIKE LOOKUP_CODE
 
[Code]...

-- not some huge query

View 4 Replies View Related

PL/SQL :: Rownum In UNION Operator

Feb 18, 2013

I have a requirement in SQL that I have to number each row. Hence I thought of using ROWNUM. But the sql query I'm using uses UNION operator. Hence I used like this

select a,b,rownum as 'field1' from table1
union
select c,d,1 as 'field1' from table2

Will the above query solve my purpose?

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved