SQL & PL/SQL :: How To Use (select Statement) Rather Than (in Clause) In Pivot Query
May 27, 2011
I'm using pivot query feature of oracle 11g and came across a strange situation where i need to pass a "select statement" in a "in clause" of pivot query.
I have tried with pivot xml but it not giving desired output in sql*plus session.It is giving unreadable output.
select * from
(select uin,testing_id,pfa_result from test1)
pivot xml (max(pfa_result) as result
for (testing_id) in (select distinct testing_id from test1));
[code]....
Here actually i want to use "select distinct id from test1" instead of "in (11,12,13,14,15)". Because i don't know how many id's will be there and of which values. e.g. 11 or 21 or 25.
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
currently i m going through some dumps for my OCA-11g prep.I came across one sentence :A view cannot have an ORDER BY clause in the SELECT statement.well this statement is false and the explanation given was :
Query operations containing ORDER BY clause are also permitted, so long as the ORDER BY clause appears outside the parentheses.
The following is an example of what I mean: CREATE VIEW my_view AS (SELECT*FROM emp) ORDER BYempno.
but when i tried running the query like this :CREATE VIEW my_view AS SELECT*FROM emp ORDER BYempno ,it worked w/o giving parentheses.
I have created domain indexes on text columns of a materialised view to use "contains" clause when searching for data. The select query with "contains" clause does not return any records, however I was able to retrive data using via regular query using a like search.
-> will exec ctx_ddl.sync_index('index_name')'resolve my problem? -> since the view is a materialized view, how can i make sure that the latest data added are also picked up?
I am trying to create a tabular form based on a SQL query, that has a query-based select list with a where clause that references a column in the originating SQL query.
The situation is, I have a table that stores client_id, source_id and build_id, lets call it client_source. I have a second table, build_source, that contains source_id and build_id, with a one to many relationship between the two (source_id of 1 could have build_id of 1-7).
Using a tabular form, I want to select the corresponding build_id to be used in client_source, but the select list must only contain the build_id's for that rows particular source_id.
Here is an example of the SQL source of my tabular form;
select s.ROWID, s.CLIENT_ID, s.SOURCE_ID, APEX_ITEM.SELECT_LIST_FROM_QUERY(1, s.BUILD_ID, 'select b.build_id display, b.build_id return from build_source b where b.source_id = s.SOURCE_ID ') lst from client_source s
... what I am trying to achieve is that the source_id fields in bold match. When the query is built this way I get an 'invalid identifier' Oracle error on s.SOURCE_ID at runtime.
Is there some special tags that need to be used to reference the outside column?I am running on Application Express 4.1.0.00.32, on a Oracle 10g release 10.2.0.4.0 database.
#select name from v$database; #select log_mode from v$database; #select count(*)"INVALID_OBJECTS" from dba_objects where status='INVALID'; #select count(*) "INVALID_N/A_INDEXES" from dba_indexes where status!='VALID'; #select count(*)"Invalid Triggers" from user_objects where OBJECT_NAME like '%TRIGGERS%' and status='VALID'; #select count(*) "Broken Jobs" from dba_jobs where broken!='Y'; #select count(*) "Block Corruption" from v$database_block_corruption;
i want a table which can be generated just by select cmd and it will list the result of all the above queires as follow:-
I mean to say i want multiple select queries into 1 table (note:- i m not saying to create a tables and then insert,update(using select from other tables), its just a sheel script that will fetch these record into a txt file)
I have a requirement to write a single sql query where i can generate the pivot report. Found some of the examples in Google search. But here we are hard coding the values if it is limited like month in this example.
i want to write similar query to represent the amount based on product type , i have around 200 types of products. I can't write case/ decode statement those many times.
query which will produce the output in pivot format , dynamically depending the number of values.
select Product, sum(case when Month=�Jan� then Amount else 0 end) Jan, sum(case when Month=�Feb� then Amount else 0 end) Feb, sum(case when Month=�Mar� then Amount else 0 end) Mar from Sales group by Product
I've tried for pivot query feature of Oracle 11g, but I'm trying for pivot result on multiple column.
Herewith I'm displaying my try on single column pivot query.
SQL> select * from 2 (select deptno,job,sal 3 --,comm 4 from emp) 5 pivot (sum(sal) as payment for job in('CLERK','SALESMAN','MANAGER')) 6 order by 1;
[code]....
I've tried this one also, but it didn't seems to be working.
SQL> select * from 2 (select deptno,job,sal,comm 3 from emp) 4 pivot (sum(sal) as payment_sal,sum(comm) as payment_comm for job in('CLERK','SALESMAN','MANAGER')) 5 order by 1;
me in building a query. I want to show the result of the query just like pivot table. Test case CREATE TABLE CPF_YEAR_PAYCODE ( CPF_NO NUMBER(5), INC_DATE DATE, PAYCODE_TYPE CHAR(1 BYTE),
[code]...
I want that my query should look like the format as attached in the xls sheet.
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') 2 || ' User '||s1.username || '@' || s1.machine 3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text ||' is blocking the SQL statement on '|| s2.username || '@' 4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> ' 6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 , 7 v$session s2,v$sql sqlt1, v$sql sqlt2 8 WHERE s1.sid =l1.sid 9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1 10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2; [code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
select empno,ename,deptno,employee_status from emp,dept where emp.deptno=dept.deptno and ( employee_status in(Case employee_status when {?Status}=1 then 'A' when {?Status}= 2 then 'T' When {?Status}= 3 then 'A'||','||'T')) OR ( end_date >= {?START_DATE} AND end_date <= {?END_DATE} ) )
Since when i pass employee_status as input 1 it have given me 4 records. When I pass employee_status as input 2 it have given me 3 records. When I pass employee_status as input 3 it should give me 4 records + 3 records=7 records.
4 records for employee_status 'A' 3 RECORDS for employee_status 'T' 7 records for employee_status 'A' AND 'T'
creating Oracle SQL query to fetch the information using PIVOT option.We are populating audit table using triggers. For every update, there will be two rows into audit table, one row with all OLD values and another with all NEW values. Also every updated is uniquely identified by Sequence No. Example for phone audit is mentioned below :
CREATE TABLE test_audit_phone ( emplid VARCHAR2(10), seqno NUMBER, action VARCHAR2(3), office NUMBER, mobile NUMBER );
Insert some rows into table.
INSERT INTO test_audit_phone VALUES ('100',1,'OLD',1111,9999) / INSERT INTO test_audit_phone VALUES ('100',1,'NEW',2222,9999) / INSERT INTO test_audit_phone VALUES ('100',2,'OLD',2222,9999) / INSERT INTO test_audit_phone VALUES ('100',2,'NEW',2222,8888) /
Table will look like the following :
SQL> SELECT * FROM sysadm.test_audit_phone ;
EMPLID SEQNO ACT OFFICE MOBILE ---------- ---------- --- ---------- ---------- 100 1 OLD 1111 9999 100 1 NEW 2222 9999 100 2 OLD 2222 9999 100 2 NEW 2222 8888
Now we have to present data in different format. For each field, display OLD and NEW values in column format.
Want to filter a data using CASE statement in WHERE clause for the following scenario.
Need to Filter tb1.fallback_keyword if the fallback_flag is "Y' or 'N' and pg_number is null.Else no partial search of keyword.
where CASE WHEN (fallback_flg = 'Y' OR fallback_flg = 'N') and (pg_number is NULL ) THEN tb1.fallback_keyword = SUBSTR(key_word,1, INSTR(key_word,'#',-2)) ELSE (tb1.keyword = key_word ) AND (tb1.keyword like regexp_replace(key_word, '[*]+', '%')) END
Are some posibilities to exclude duplicate values do not using sql aggregate functions in main select statement? Priview SQL statement
SELECT * FROM ( select id,hin_id,name,code,valid_date_from,valid_date_to from diaries )
[Code]....
In this case i got duplicate of entry TT2 id 50513 In main select statement cant use agregate functions are even posible to exclude this value from result modifying only the QLRST WHERE clause (TRUNC need to be here)
