BANNER Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production "CORE10.2.0.5.0Production" TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production [code]....
Requirements for the result set:
1. List the root division as column headers 2. Start date is March 15, 2012 3. End date is June 30, 2012 4. Do not skip or omit dates 5. The values under each column are percentages where completed = 'Y' 5a. Example, D5_Total = 277, D5_Completed(Y) = 254, D5_Percent(Y) = 254/277 = 92% 5b. Rounded to whole percentage like 92% and NOT 91.696%
The result set will be used to create a line graph. The graph will show which root division is closer to reaching 100% before the deadline of June 30, 2012.
and the following select statement results in multiple lines:
SQL> select INSTANCE_NAME,STATUS from v$instance; INSTANCE_NAME ------------------------------------------------ STATUS ------------------------------------ BUP OPEN
Another database has following characterset settings:
SQL> select INSTANCE_NAME,STATUS from v$instance; INSTANCE_NAME STATUS ---------------- ------------------------------------ HPSMP OPEN
Both databases are version 10.2.0.4 and both are running on HPUX 11.31.Why do we get this differents in showing the result?
Our problem is that the package-start scripts from HP, to start a database, check the result of the select statement above and if we have the multiline result the check gives an error and stops the package again.
select distinct event_number from events_total WHERE event_id = 16395493 minus select distinct event_number from event_details_ford WHERE event_id = 16395493
result of which is : 6L2Z-7861693-AAC 6L2Z-7862187-CAC
i want to put this in dynamic sql where clause :
where event_number in ('6L2Z-7861693-AAC','6L2Z-7862187-CAC'). and if the result of the query is only one number, then where event_number in (6L2Z-7861693-AAC) result of the query can be NULL also. but i think i can use IF condition for "SELECT ..WHERE event_number in " query
so how can i put the results of query in one line, so that i can use it in where clause.
I want to run below query to get the result set that I am after. But It takes long time even with the indexes...Here in IM_Mapping table is having 1.7 mio records and T_Extract table about 35000 records. All the other tables having below 1000 records
I need to write a query which will get me output: 233445, i.e. all the three rows concatenated. How can it be done? I want to do it through sql only and not to use PL/SQL. Is this possible?
I have a sql query which fetch the data from 4 different tables. I want to write the output of that query into a excel or a CSV file without using TOad and all. Let me know is it possible via creating function or procedure.
I have a strange problem with query with like and %.
When I run this script:
ALTER SESSION SET NLS_SORT = 'BINARY_CI'; ALTER SESSION SET NLS_COMP = 'LINGUISTIC'; -- drop table test1; CREATE TABLE TEST1(K1 NVARCHAR2(80)); INSERT INTO TEST1 VALUES ('gsdk');
When i change datatype in column to varchar2 this code work correct.
The execution plan:
PLAN_TABLE_OUTPUT SQL_ID d3d64aupz4bb5, child number 2 select * from TEST1 where k1 like N'Ł%' Plan hash value: 4122059633 Id Operation Name Rows Bytes Cost (%CPU) Time 0 SELECT STATEMENT 2 (100) * 1 TABLE ACCESS FULL TEST1 1 82 2 (0) 00:00:01
I have a strange problem with query with like and %.
When I run this script:
ALTER SESSION SET NLS_SORT = 'BINARY_CI'; ALTER SESSION SET NLS_COMP = 'LINGUISTIC'; -- SELECT * FROM NLS_SESSION_PARAMETERS; -- drop table test1; CREATE TABLE TEST1(K1 NVARCHAR2(80));
[code]....
When i change datatype to varchar2 this code work correct.
The execution plan:
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID d3d64aupz4bb5, child number 2 ------------------------------------- select * from TEST1 where k1 like N'Ł%'
[code]....
Note - dynamic sampling used for this statement (level=2)
Is there any way to Create Form in which i give a query and in result it return the query result in detail block in form of fields just like we return in toad or pl/sql developer.
SELECT wmsys.wm_concat (gp.prog_acronym) FROM inf_program gp, ea_audit_program ap WHERE ap.sys_prog_id = gp.sys_prog_id AND ap.sys_audit_id =484
is there any way I can check the datatype of the result of the above query ? ,my dba added some patch to oracle , after the patch this query is returning a clob in java , it should return string and it used to return string before patch and in other databases it returns string, I can check the return type only from java side , is there any way oracle can say me the datatype ?
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.
My problem is I have 3 tables (TEST_TBL1, TEST_TBL2, TEST_TBL3). TEST_TBL2 and TEST_TBL3 are in remote database and I use database link to join them. The following query returns incorrect result (I seems that it ignore the where clause)
SELECT * FROM TEST_TBL1 JOIN TEST_TBL2@db_remote USING (KEY1) JOIN TEST_TBL3@db_remote USING (KEY2) WHERE KEY1=XXX OR KEY2=YYY;
I am on 11R1 (11.1.0.7)
FOR EXAMPLE:
Local database: CREATE TABLE TEST_TBL1 ( KEY1 NUMBER(5) NOT NULL,
