BANNER
--------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
[code]....
Middle EastThe table contains more than a million records, pivoting using a group by clause will have performance issues. Would it be possible to do this with Xquery or any other method?
i have to use few columns from multiple with clause how can i get output like below
Project_Name Product_Name Project_Number
205.p1 Tio HandiHaler (SGRQ sNDA) Tio 205.p1 205.p1 Tio HandiHaler (SGRQ sNDA) Tio 205.p1 205.p1 Tio HandiHaler (SGRQ sNDA) Tio 205.p1 Testing Project Description Milestone Report Testing 1 1,00,101
I got a two tables, table the_table_1 consist of customers and the other one the_table_2 consist of channels.
And I need to get list of channels by contract which are an assigned to customers.
The select below give me a only assigned channels but I need to get a contract_key too from the_table_1.
select o.channel_name from the_table_2 o minus select o.channel_name from the_table_1 a, the_table_2 o where a.contract_key=237092201 and a.offer_channel_key=o.OFFER_CHANNEL_KEY
And the select under (after minus) gives a list of non assigned channels by contract_key:
select o.channel_name from the_table_1 a, the_table_2 o where a.contract_key=237092201 and a.offer_channel_key=o.OFFER_CHANNEL_KEY
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 return the stored result in a record by ref cursor. In the below code I am displaying the result through DBMS_OUTPUT.PUT_LINE but i need to do it through ref cursor to display the result.
declare type r_cursor is REF CURSOR; c_emp r_cursor; type rec_emp is record ( name varchar2(20), sal number(6) [code].....
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 the following data (columns IDx, STARTx and DURATION) and want to create the column RESULT. Example:
create table zTEST ( IDx number, STARTx number, DURATION number, RESULT number ); insert into zTEST (IDx, STARTx, DURATION, RESULT) values ( 1 , null , null , null );
[code]...
The logic behind the table is like this: when there is a Start-ID and a Duration, the field Result shall show the Start-ID for the next records (Duration period). Even if there is a new Start-ID in row 9, it is ignored since threre is already a current signal from row 6 which is still running.
select dem.NUM_PCE, memo.comment from demand dem, dem_comment memo where CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE) = memo.parentId (+) and memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO) FROM dem_comment memo WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))
I should have a result with this request but there's nothing and the problem come from the fact that when the following condition is not met, there are no resulty even if there's an outer join
and memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO) FROM dem_comment memo WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))
This condition makes Oracle ignores the outer join..I have tried to add this:
select dem.NUM_PCE, memo.comment from demand dem, dem_comment memo where CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE) = memo.parentId (+) and (memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO) FROM dem_comment memo WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE)) or memo.dateCreation is null)
but there are too much records and the result is not consistent
For obscure test purposes I need to modify an existing SQL query to emit the rows N times instead once. I'm aware of the possibility to "UNION ALL" the query with itself to get the all rows twice.
But as I require the resulting rows to be emitted around ten to hundred times this approach doesn't seem sensible to me. Not to speak of the missing possibility to parametrize the number of "repetitions".
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 want that when i run above query every time bas_cod column should start with difference value. now it is starting from 1, i need when i run next time it should start with another number except 1,
i mean whenever i run above query,order of bas_cod must be change from last.
SELECT V.VEHICLE_CODE VEHICLE_CODE,MAX(L.LICENSE_EXPIRE_DATE) L_EXP_DATE ,L.LICENSE_PLACE FROM VEHICLES V,VEHICLE_LICENSE L WHERE V.VEHICLE_CODE = L.VEHICLE_CODE and V.VEHICLE_CODE='14' GROUP BY V.VEHICLE_CODE ,L.LICENSE_PLACE
that should result the max "LICENSE_EXPIRE_DATE" for this "VEHICLE_CODE" it gives me two record
Is there any way to return a result set (using a Database Link) from one Database to another Database? Database A needs to call Database B and Database B needs to return a result set via a stored package/function. The stored procedure/function on Database B also accepts 1 input parameter. Something like "select test.test('some input parameter')@database_b from dual'.
What is the easiest way to add 2 rows together? I need this results set below as is except I need rows with activity codes 0 and 30 to be merged together into Activity Code 0. So, Regular hours would be 7466.75. Do I need to do a Union or is there a more elegant solution?
My current sql results set looks something like this:
Activity Code Activity Code Desc REGULAR OT NONPROD DEPARTMENT 0 0: Business As Usual 7409.25 305.5 1603.25 Information Technology 10 10: Special Projects 190.75 17 0 Information Technology 20 20: Strategic Initiatives 1067 52 0 Information Technology 30 0: Business As Usual 57.5 0 0 Information Technology
and I want my new results to look like this:
Activity Code Activity Code Desc REGULAR OT NONPROD DEPARTMENT 0 0: Business As Usual 7466.75 305.5 1603.25 Information Technology 10 10: Special Projects 190.75 17 0 Information Technology 20 20: Strategic Initiatives 1067 52 0 Information Technology
