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
ADVERTISEMENT
Aug 5, 2010
this statement is taking 1hr , can we reduce the timing?
CREATE TABLE DGT_ITEMEFFORTDATA (ENTERPRISEID, OWNERTYPE, OWNERID, SUPEROWNERTYPE, SUPEROWNERID,
ITEMTYPE, ITEMID, STAGEID, USERID, DATEIDENTIFIED,
DATECLOSED, ACTIVITYCODEID, PHASEID, RELEASEID, MONTHID,
QUARTERID, INITIALEFFORT, BASELINEDEFFORT,
ACTUALEFFORT, ITEMSTATUS, ALLOCATIONSTATUS, STAGESTATUS,
OCCURANCETYPE, DSLPROJECTTYPE, METRICCALCRUNID,
[code].....
This is the explain plan of the above query
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11M| 4137M| 46149 (
| 1 | UNION-ALL | | | |
| 2 | TABLE ACCESS FULL| DGT_ITEMEFFORTDATA_DAILY | 3455K| 428M| 14575
[code].....
This is the index details
1DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDITEMID4
2DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDITEMTYPE3
3DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDOWNERID2
4DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDOWNERTYPE1
There is no index on DGT_ITEMEFFORTDATA_TEMP table
[code].....
View 27 Replies
View Related
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
May 24, 2010
I have a view, which has a union. (Union is required because of the nature of the data fetched). THis view is later joined with a global temp table which holds the -say employee Id the user selects.
So at runtime there is a join with the global temp table and the view. But the performance is really bad. I have tried using various hints, like materialize, /*+ CARDINALITY(gtmp 1) */ etc.
When i query the view alone,. the performance is good. When I remove the union, the performance is good. Some how with the union- there is a full table scan on one of the joining tables.
View 7 Replies
View Related
Jul 5, 2012
I have this sql (generated by discoverer plus), and work fine:
SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
[code]...
We need change the table stock_por_sucursal for a view like this, with similar indexes each table
select * from stock_por_sucursal_old
union all
select * from stock_por_sucursal_new
and the result is not good:
SQL Statement from editor:
SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
GAR_DW.V_STOCK_POR_SUCURSAL o10475761,
[code]...
finally add a HINT /*+ gather_plan_statistics push_pred(TABLE) */ and the result was not very good in this case, but improved the resolution of the view, the rest got worse
SQL Statement from editor:
SELECT /*+ gather_plan_statistics push_pred(o10475761) */ COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
[code]...
View 2 Replies
View Related
Aug 10, 2011
HOW to use variable P_TMPLID in following statement
TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;
because its throwing error while compiling
and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION
View 2 Replies
View Related
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
May 19, 2011
I want to know the DDL statements executed on a table. Example:
i have a table test1 with structure as below: test1 (aa number, bb varchar2(10))
After some period of time, one DDL statement to alter the column is issued.
alter table test1 modify (bb varchar2(30));
so new table structure is: test1(aa number, bb varchar2(30))
Basically i would like to find these 2 DDL statements executed on this table - test1, so that i compare which column get modified and what modification done.
Is this information stored in any data dictionary table?
note - audit, flashback option not enabled in this database.
View 3 Replies
View Related
May 14, 2013
I have a table TEMP_TEST_1 to be loaded with multiple rows which contains the available items for a store with their sequence numbers. I also have another table TEMP_INV_1 which holds a column for inventory count of the items, and this column is to be updated after loading the first table TEMP_TEST_1.
The table TEMP_TEST_1 is updated through all possible channels in OLTP. So while updating the inventory, which is the best way. My update should either add the total number of inserts per item into the TEMP_TEST_1 table's INVENTORY_CNT column i.e. existing value + count of new rows OR a full update by taking the complete row count (Grouped by item id) and update the INVENTORY_CNT column
--------------------------------------------------------------------------------
-- Initial setup SQL's, TEMP_TEST_1 is updates on OLTP through multiple channels
--------------------------------------------------------------------------------
CREATE TABLE TEMP_TEST_1 ( ITEMID NUMBER ( 4 ),
INVENTORY_CNT NUMBER ( 2 ) );
CREATE TABLE TEMP_INV_1 ( ITEMID NUMBER ( 4 ),
ITEMSEQ NUMBER ( 5 ) );
INSERT INTO
[code].......
--------------------------------------------------------------------------------
-- Inserts for new items in the store
--------------------------------------------------------------------------------
INSERT INTO
TEMP_INV_1
VALUES
( 1,
11 );
INSERT INTO
TEMP_INV_1
[code].......
View 4 Replies
View Related
May 26, 2011
i'm trying to use a collection in a select statement as a table, but i've got an error which i don't understand.
This exemple is very simple. I'm trying to validate the solution. The object will contain the result of a query from a big table, and i have 4 request to make on it. I don't want to select 4 times the big table to get the result, but i need the whole result to make my requests (intersect, minus and union)
CREATE OR REPLACE
TYPE zy_w AS OBJECT(
CODE_INFORMATIONVARCHAR2(4),
[Code]....
****************
Rapport d'erreur :
ORA-06550: Ligne 12, colonne 3 :
PL/SQL: ORA-00947: nombre de valeurs insuffisant
ORA-06550: Ligne 5, colonne 3 :
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
View 9 Replies
View Related
Nov 8, 2011
this is the correct syntax for updating a table with a select statement included. Table created easily and the alter table ran fine, but the update is running quite a log time.
My ultimate goal is to populate the "children" field with a count of children for each household id.
create table NON_GBC_Members nologging as
select distinct hcp.household_master_ID
from mrtcustomer.household_child_profile hcp
where hcp.child_birth_dt between '31-OCT-2000' and '30-OCT-2011'
group by hcp.household_master_id
minus
[code]....
View 5 Replies
View Related
Oct 29, 2012
{code{
i want to apply merge stmt on single table.
CREATE TABLE TEST11(TNO NUMBER(5), TVAL VARCHAR2(100), TID VARCHAR2(10));
INSERT INTO TEST11 VALUES(1,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(2,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(3,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(4,'VIJAYA','');
INSERT INTO TEST11 VALUES(5,'VIJAYA','');
[Code]....
My requiremen is if record is exists then i wan to update some value, if record not existes the i wan to insert new record
View 3 Replies
View Related
Sep 1, 2011
there is a possible to use a select statement on collection table?
View 1 Replies
View Related
Sep 30, 2010
I have a table with a column type long raw. Now i want to insert an image from directory by an insert statement. Is it possible?
View 2 Replies
View Related
Feb 17, 2010
I have the following create table statement:
create table SELECTION_BOOKMARK(
INSTALLATION_ID SMALLINT NOT NULL,
BOOKMARK_ID SMALLINT NOT NULL,
NAME VARCHAR2(50) NOT NULL,
SORT_ORDER SMALLINT NULL
);
When I execute this statement as part of a SQL script, it throws the error "ORA-01003: no statement parsed". If I execute it as a stand alone sql command, it returns no error.
Here's the strange thing, though. Even when it throws the error message the table is created. Later on in the script, I execute the following command:
alter table SELECTION_BOOKMARK add primary key(INSTALLATION_ID,BOOKMARK_ID);
This command also throws the above mentioned error, but once again the command executes - the primary key is created.Well, I don't like having a script that throws errors. Eventually I'll have to give this script to a dba in another organization and I don't want my script to be throwing errors - even if it's apparently working correctly despite the errors.
View 12 Replies
View Related
Jun 18, 2013
I want to grant a privilege through an insert statement into a sys table.Why do not grant the privilege through the classic way : grant select on t to l_user; ?
Because I want to do it remotely.I am connected to db1.I want to grant select on t2 to u2_b from u2_a.I assume that all DDL are DML. So a grant is equivalent "somewhere" to an insert.I tried to do my requirement locally, and here is the output.
SQL> conn scott/aa
Connecté.
SQL> -- step 1 : try to grant "normally" a select on dept to hr from scott
SQL> grant select on dept to hr;
Autorisation de privilèges (GRANT) acceptée.
SQL>
SQL> conn sys/a as sysdba
Connecté.
SQL> -- step 2 : Then, we connect to sys to see the row inserted in dba_tab_privs
SQL>
SQL> col GRANTEE format A10
SQL> col OWNER format A10
[code]...
Then if I can do it locally, I can do it remotely through a db link.
View 2 Replies
View Related
Jul 19, 2013
How do I collapse all nulls for every table column in a SQL Statement?
View 15 Replies
View Related
Aug 10, 2010
Can I have all the rows in a table updated at once in the merge statement?
MERGE INTO providermaster a
using
(
SELECT * FROM PROVIDERMASTER@INGEST) b
ON (b.MASTERPROVIDERID=a.MASTERPROVIDERID)
WHEN MATCHED THEN
UPDATE ....... I want to update all the other rows at once..
View 24 Replies
View Related
Sep 20, 2010
We are firing a normal Drop command on our database and the database version is 10.2.0.4.The database is running on AIX v5.The command is taking more time than usual .
When i am monitoring the session i can see that a call is being made to procedure "aw_drop_proc".Could i ask you if this is something that is taking more time than usual.
We are not having any partitions on the nested tables .We have a pack of tables and we are dropping this pack through a procedure.The pack comprises of nested tables & normal tables.To drop a nested table it is taking around 6 seconds(Table with no rows) and a normal table(With no rows) it is taking 17 milli seconds.We have a partition on Normal table.
The same operation in windows is taking very less time when compared to AIX.
View 5 Replies
View Related
Sep 2, 2011
I have a large table and want to calculate just a few values. Therefore, I don't want to create a new table, I want to update the table. Here an example:
I want to calculate the VALUE_LAG with ID = 4 only (-> two values).
create table zTEST
( PRODUCT number,
ID number,
VALUE number,
VALUE_L1 number );
[Code]..
I tried this, but obviously, windows functions are not allowed in the update statement.
update zTEST
set VALUE_L1 = lag(VALUE) over (partition by PRODUCT, order by ID)
where ID = 4
How can I do this?
View 12 Replies
View Related
Oct 8, 2012
I've to create a table which has 650 fields and the total length of CREATE TABLE statement got to be more than 4000 characters.I've to create the table by inserting the CREATE TABLE statment in a variable (V1) then by using EXECUTE IMMEDIATE V1 Since VARCHAR2 only supports upto 4000 characters length string, how can I create such table??
DECLARE
V1 VARCHAR2(4000);
BEGIN
V1 :=
-- CREATE TALBE STATEMENT WITH LENGTH MORE THAN 4000
EXECUTE IMMEDIATE V1;
END;
Quote:got the error -- PL/SQL: numeric or value error: character string buffer too small
How can I create such table??
View 7 Replies
View Related
Nov 30, 2011
how to play around with NDS dynamic sql and I'm trying to add a column on the fly.Basically the procedure is trying to take a table name, column name, and eventually a data type and adds it to a table.
It works fine without the bind variable for the column name, accepting the table name on the fly.As soon as it tries to use the column name I get an ORA-00904 invalid identifier exception.
Here is the procedure I'm using
CODEcreate or replace
procedure test(tbl_name varchar2, col_name varchar2) IS
qry varchar2(500);
begin
[code]....
Here is how I'm executing it.
CODEexecute test(tbl_name => 'BB_SHOPPER', col_name => 'MEMEBER');
View 3 Replies
View Related
Jun 6, 2010
In a trigger(on update of a table t1) I am trying to write, I am doing an insert on t2 accessing ':new' values of the update on t1.
But in my Insert statement, I am having get one of the column values from another table. How can I write my insert statement in such a way as to insert values contained in ':new' pseudo columns and a select from another table. Below is my insert statement in the trigger :
-------
IF (:old.GROUP_YELLOW <> :new.GROUP_YELLOW) THEN
INSERT INTO TEST.W_THRESHOLD_LOG
(THRESHOLD_LOG_WID, CHANGE_DATE, MEASURE_TYPE_WID, MEASURE_NAME, CUSTOMER_WID, CUSTOMER_NAME, USER_ID, CHANGED_ITEM, PREV_VALUE, NEW_VALUE)
VALUES(TEST.W_THRESHOLD_LOG_SEQ.NEXTVAL, SYSDATE, :new.MEASURE_TYPE_WID, 'Rolling Stabilty' , :new.CUSTOMER_WID, 'Customer1', 'User1', 'GROUP_YELLOW', :old.GROUP_YELLOW , :new.GROUP_YELLOW);
END IF;
-------
In the above code if the hardcoded value 'Customer1' need to be picked from another table,
i.e .
SELECT NAME FROM W_CUSTOMER_DIM WHERE CUSTOMER_WID = THRESHOLD.CUSTOMER_WID
how can I rewrite my query to the above value from the select into my insert statement..?
View 24 Replies
View Related
Aug 14, 2012
I have been trying to use case statements with oracle table type by really not sure how to go about it. I know it might be simple but it been giving me hard time.
Here is my Cursor:
CURSOR c_chk_style IS
SELECT DISTINCT 1
FROM TABLE(CAST(I_message.ExtOfXOrderDesc_TBL(1).ExtOfXOrderSkuDesc_TBL AS "RIB_ExtOfXOrderSkuDesc_TBL")) item_diff,
[code]...
Now i know that the table type "RIB_ExtOfXOrderSkuDesc_TBL" will be always populated but the table type "RIB_ExtOfXOrderPackDesc_TBL" may not be populate and can be null. So i want to run the exists against the "RIB_ExtOfXOrderPackDesc_TBL" aliased pack_diff only if it is populated. If its null i dont want to run the exists clause.
View 15 Replies
View Related
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
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
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
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
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
Jul 1, 2010
how can i get a union of two pl/sql tables. ??
View 2 Replies
View Related
