SQL & PL/SQL :: 70% / 30% From High And Low Priority Element

Oct 27, 2011

I have a priority column(possible values are 1 or 0) in a table where

i need to get 70% of high(1) and 30% of low (0) and max i can fetch for select is 50 records.

Eg1: Total if i have 60 in which 20 high and 40 low then 70% of 20 = 14 and remaining should be taken from Low i.e. 36 from low. so total will be 50 transactions.

Eg2: Total if i have 60 in which 40 high and 20 low then 70% of 40 28 + remaining should be taken from Low i.e. 22 from Low.

Eg 3: If i don't have any high then total should be picked from low vise versa.

I have below query but it is having problem when there is no low priority.

SELECT ID,PRI FROM temp tbl WHERE pri = '1' AND ROWNUM < ((70/100)*50)+1
UNION ALL SELECT * FROM temp WHERE pri = '0'
AND ROWNUM < 50-(SELECT COUNT(*) FROM temp WHERE pri = '1' AND ROWNUM < ((70/100)*50)+1)

View 3 Replies


ADVERTISEMENT

Order By In Oracle With Priority?

Nov 23, 2012

I have a table (emp) with three columns in ORACLE:

COMPANY OFFICE EMP
------------------------
9999 00001 emp1
9999 00001 emp2
9999 00002 emp3
9999 00002 emp4

[code]...

I want to get value in EMP column with following statement:
-------------
SELECT
EMP
FROM
emp
ORDER BY COMPANY (But 9999 is first), OFFICE (But 99999 is first).
-------------

I don't know how to do.

View 2 Replies View Related

SQL & PL/SQL :: Fetch Records Based On Status Priority

Dec 20, 2012

I have a table with below syntax and insert statements.

-- Table creation
-----------------------
create table order_test (oid number,
orderno number,
ordername varchar2(100),
orderstatus varchar2(50),
orderseq number);
----------------------------------
-- Insert statements

[Code]....

The Output looks like below

OID|ORDERNO|ORDERNAME|ORDERSTATUS|ORDERSEQ
1 |100 |ORD1 |INPROGRESS |1
7 |100 |ORD1 |START |4
3 |100 |ORD1 |START |2
4 |100 |ORD1 |INPROGRESS |3
6 |101 |ORD2 |CANCELLED |3
2 |101 |ORD2 |INPROGRESS |2
5 |101 |ORD2 |COMPLETE |1
8 |102 |ORD3 |COMPLETE |1
9 |103 |ORD4 |CANCELLED |1
10 |103 |ORD4 |CANCELLED |2

Now, the below rules should be applied to get the output..

1) If status is same, then fetch max(seq) record
2) If status is different, then apply below priority to fetch the records

Start - Priority 1 (High)
In Progress - Priority 2
Cancelled - Priority 3
Complete - Priority 4 (Lowest)

Means, the output for each order should look like below.

OID|ORDERNO|ORDERNAME|ORDERSTATUS|ORDERSEQ
3 |100 |ORD1 |START |2
2 |101 |ORD2 |INPROGRESS |2
8 |102 |ORD3 |COMPLETE |1
10 |103 |ORD4 |CANCELLED |2

how to do it.

View 1 Replies View Related

SQL & PL/SQL :: Query To Return Not Null Column Values With Priority

Dec 22, 2011

I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.

I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.

WITH TMP_TBL AS
(
SELECT * FROM (
SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual
UNION ALL
SELECT 'A','2','A122','A23',NULL,NULL FROM dual
UNION ALL
SELECT 'A','3','A32','A33',NULL,'A35' FROM dual
[code].......

DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes

The results I am expecting is

DUNCOL1 COL2 COL3 COL4
AA21 A23 A41 A35
BB12 B23 B15
CC12 C13 C33 C14

I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.

SELECT DUN,
row_nvl(rownvl_param_type(RNK,col1)),
row_nvl(rownvl_param_type(RNK,col2)),
row_nvl(rownvl_param_type(RNK,col3)),
row_nvl(rownvl_param_type(RNK,col4))
FROM TMP_TBL
GROUP BY DUN

But I don't think my manager will allow me to deploy a Multi Row function .

View 2 Replies View Related

CLOB In Xml Element

Oct 18, 2011

I have to generate a big xml. Each part of the xml needs data from different tables. So I have created two clob to hold the portion of the big xml. How can i have the concatenated clob values as an argument for xml element.

View 1 Replies View Related

SQL & PL/SQL :: How To Add XML Element In Oracle 10gr1 Without Using XDB

Jun 24, 2013

how to add an xml element in oracle 10gr1 without using Oracle XDB?

View 1 Replies View Related

Reports & Discoverer :: Use Of Xml Element In 6i?

Nov 6, 2012

i want to display values of same column with comma seperation like this

SELECT RTRIM(XMLAGG(XMLELEMENT(e,DESCR_INDI || ',')).EXTRACT('//text()'),',') INTO DESCR FROM
STUDENT_SKILL_PER WHERE GROUP_NAME=:GROUP_NAME
AND SKILL_NAME=:SKILL_NAME AND SUB_SKILL_NAME=:SUB_SKILL_NAME
AND TERM=:TERM AND STUDENT_CODE=:STUDENT_CODE AND YEAR_CD=:YEAR_CD;

but the error shown to me that xmlelement must be declared...but when i deploy same query at sql editor it gives me correct result..how i can use it on oracle report 6i... i am using oracle 9i as database.

View 1 Replies View Related

Forms :: Empty Element In Item_list

Jan 13, 2013

I used a dynamic item_list. This list should contain only the result of the query but I found an empty element in the list. So, how to eliminate this element empty list?

see the picture:

[URL]........

View 4 Replies View Related

SQL & PL/SQL :: ORA-22160 / Element At Index (1) Does Not Exist

Mar 27, 2007

i am trying to update using bulk processing.am facing this ORA-22160: element at index [1] does not exist.

ORA-22160: element at index [string] does not exist

Cause: Collection element at the given index does not exist.

Action: Specify the index of an element which exists.

how to avoid this.

1 create or replace procedure dept_new
2 is
3 type dept_type is table of dept.deptno%type;
4 type tab_type is table of dept%rowtype;
5 --variable declaration
6 dept_t dept_type;
7 tab_t tab_type;

[code]....

my dept and dept1 tables are both of same structure.

View 7 Replies View Related

PL/SQL :: Referencing Element In Nested Collection

Jun 27, 2013

I have a table called dept table with nested collection as below. Nested Table Collection:

create or replace
type courselist as table of varchar2(64) Table Using Nested Table Collection: 
CREATE TABLE "FCSDWH_STG"."DEPT"
   ( "NAME" VARCHAR2(20 BYTE),
  "DIRECTOR" VARCHAR2(20 BYTE),
  "OFFICE" VARCHAR2(20 BYTE),
  "COURSES" "FCSDWH_STG"."COURSELIST"
   )  Content Of Table:NameDirectorofficecoursesAccountingJames CharlesUNOFCSDWH_STG.COURSELIST('natural science','chemistry','Computer Science','Computer Science') 

I am trying to select and print an element from nested table collection using below plsql block.

MY_COURSE VARCHAR2(64 CHAR);
BEGIN
SELECT courses INTO MY_COURSE FROM TABLE(SELECT COURSES FROM DEPT) where courses='chemistry';
DBMS_OUTPUT.PUT_LINE(MY_COURSE);
END; Error Message:
PL/SQL: ORA-00904: "COURSES": invalid identifier

View 3 Replies View Related

XML DB :: Un-nest XML With Single Child Element

May 20, 2013

I'm trying to write a generic function for un-nesting child nodes where nesting is deemed unnecessary. Typically when the element only has one child element.

For example, given the following source....
<ROOT>
  <ITEM>
    <DESCRIPTION>TEST1</DESCRIPTION>
  </ITEM>
  <ITEM>
    <DESCRIPTION>TEST2</DESCRIPTION>
  </ITEM>
</ROOT>I actually want.....
<ROOT>
  <DESCRIPTION>TEST1</DESCRIPTION>
  <DESCRIPTION>TEST2</DESCRIPTION>
</ROOT>

because we think ITEM isn't really required before we deliver XML data.I've been trying to achieve this with a function, where I pass in the XPath to the node I want flattening, something like

function UnNest(pXMLData XMLType, pXPath varchar2) return XMLType...Called like the following...
declare
begin
  vXMLData := UnNest(SomeXMLData, '/ROOT/ITEM')
end;I tried using XQuery Update (11gR2), as follows,
[code]....

My destination platform is 11gR2 64 bit but going forward I may need a solution for 10gR2 too. Perhaps there is another way without using XQuery Update?

View 2 Replies View Related

JDeveloper, Java & XML :: Procedure For Reading Xml Element Value

May 25, 2011

In my pl/sql procedure I'm calling a webservice and it is returning me a varchar2 which contains xml tags.I want to pull values for each element. any one can provide me procedure/function to pull data from it.Below is the output from webservice.

<ResponseEnvelope xmlns:xsd="URL....xmlns="URL....> <ResponseHeader xmlns="URL......
[code]....

ResponseId, MessageVersion etc are element name. the value between element names are to be retrived.

View 13 Replies View Related

SQL & PL/SQL :: Check If Element Present In Defined Column Of Cursor?

Apr 29, 2010

i want to produce a function who return XmlType element if she checks that a parameter (that i passed to here is contained in a row in a defined table)

for example

create table sec_form(
form_code VARCHAR2(8)
, sec_code VARCHAR2(5)
) ;
insert into sec_form values ('CSA','section1');
insert into sec_form values ('CSA','section2');
insert into sec_form values ('CSA','section3');

then i got my function

create or replace function checkIfSectionEnabled (elem XmlType,name_section varchar2)
return XmlType
is v XmlType;
CURSOR myCursor IS select * from sec_form where form_code = 'CSA';
begin
open myCursor;
loop

[code]....

now i'm gonna call the function (elem XmlType,name_section varchar2) and pass here "section1" for example ; and she must return me my xmltype element if she find "section1" finds section1 present;

when i try t compile i got:

Error(18,13): PL/SQL: Statement ignored
Error(18,26): PLS-00225: subprogram or cursor 'MYCURSOR' reference is out of scope

View 3 Replies View Related

JDeveloper, Java & XML :: Updating Element Text In Document?

Nov 15, 2012

I have table called shipper with document_xml as CLOB column

The sample content of document_xml is below

<placemark id = "5467">
<name> 415-CI-78492</name>
<description><![CDATA[<//!!!!//15249599]]></description>
<ICON><href>http://localhost:63077/Icons/Markers/pushpin.png</href></Icon>

and there are other attributes continued

Objective: I want to replace "href" element in each of the record with "[URL] ......" value.

After I update the row the data should look as follows

<placemark id = "5467">
<name> 415-CI-78492</name>
<description><![CDATA[<//!!!!//15249599]]></description>
<ICON><href>http://localhost:63077/Icons/Markers/ship.png</href></Icon>.

I have to do this for bunch of records in a table(table_name = shipper).

Is there an oracle function to replace/update the href element in a CLOB column?

View 4 Replies View Related

XML DB :: Insert Element Into Data Stored In CLOB Column?

Dec 14, 2012

My ORACLE DB version is:

('Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production');
('PL/SQL Release 11.2.0.2.0 - Production');
('CORE 11.2.0.2.0 Production');
('TNS for Linux: Version 11.2.0.2.0 - Production');
('NLSRTL Version 11.2.0.2.0 - Production');

I have this XML data stored in a CLOB column:

<Activity>
<Changes>     
</Changes>
<Inserts>     
</Inserts>
<Definition>     

[code]....

I need creating an update that will insert another <Activity>SomeGUID</Activity> into the <Spawned> parent.

View 3 Replies View Related

Application Express :: Button With Pre-element Text Is Given A Container Div?

Aug 8, 2013

Just installed 4.2.2.00.09 and imported a workspace and an application. Worked fine.I noted a page display difference when compared to the exported application (4.1.0.00.32). I have (so far) found this:If I have a button with Pre Element Text set to <div class="dummy"> and Post Element Text set to </div>, Apex generates an extra div around it all.If my button was named P1_SUBMIT I end up with 

<div id="P1_SUBMIT_CONTAINER">
<div class="dummy"> <input type="button" value="Search" id="P1_SUBMIT" /></div>
</div> 

Is it possible for me to turn this extra div off?

View 0 Replies View Related

JDeveloper, Java & XML :: Convert XML Message To CSV And Assign It To (anyType) Element

Mar 28, 2012

I have a requirement that is to convert XML message to CSV format and assign this to a "anyType" element. How would I convert to CSV?

View 1 Replies View Related

XML DB :: Add Attribute Modification_date For Root Element Process For Field Xml_data?

Mar 6, 2013

I'm studying the guide Oracle XML DB Best practices and it is ok to add, modify or delete nodes. Also to change the value of an attribute, but I'm looking for adding an attribute.What is exactly the statement, for example to add the attribute modification_date for the root element process for a field xml_data ?

View 1 Replies View Related

Application Express :: How To Get Unique Element IDs In Manual Tabular Form

Oct 24, 2012

I'm trying to get a unique sequential element id for every field in a manual tabular form. Here is the code to generate the form:

SELECT apex_item.hidden(1, lpad(rownum,4,0)) row_num,
apex_item.hidden(2, skillset_demand_id) sdi,    
apex_item.text(3, domain, 20, 20, null, 'f03_#row_num#') Domain,
apex_item.text(4, target, 20, 20) Specialization,
apex_item.text(5, skill, 20, 20) Skill
FROM RI_SKILLSET_DEMAND
WHERE WORK_ID = :P511_WORK_IDAccording to the APEX API manual I should be able to set the attribute id with p_item_id*, which in the example of a text item, is the sixth parameter. In line 3 of the code above, I set the p_item_id to 'f03_#row_num#' after having created row_num in line 1.

The HTML I get is:
<input id="f03_#row_num#" type="text" value="xxxxxxxxx" maxlength="20" size="20" name="f03">
...
<input id="f03_#row_num#" type="text" value="xxxxxxxxx" maxlength="20" size="20" name="f03">What I want is:
<input id="f03_0001" type="text" value="xxxxxxxxx" maxlength="20" size="20" name="f03">
...
<input id="f03_0002" type="text" value="xxxxxxxxx" maxlength="20" size="20" name="f03">
...
etc.

I've tried a lot of things but I can't seem to get parameter substitution inserted into the id attribute so I can get each element uniquely with javascript later.

View 1 Replies View Related

JDeveloper, Java & XML :: ORA-30951 / Element Or Attribute At Xpath Exceeds Maximum Length

Apr 29, 2013

I am getting the error when trying to run this test block, this is code is to simulate the production error we are getting...

DECLARE
g_xdoc XMLTYPE;
g_trans_type CHAR (20) := NULL;
BEGIN
SELECT xmltype(c) -- column is a clob column
INTO g_xdoc
FROM test_gil

[code]....

View 12 Replies View Related

SQL & PL/SQL :: Adding High And Low To Zip Plus Four Values?

Jan 19, 2011

I have a table with zip codes and their plus four values. For ex: zip code of 10000, which has corresponding plus four values of 001, 002, 003, and 008, 009, 010. The issue is just that--a zip code can have sequential plus four values, and then it will skip several potential plus four values, and then start again. I would like to assign a low plus 4 value and high plus four value to a zip code, keeping in mind that the plus four values are not always sequential. So, it would be similar to this:

zip plus4 low plus4 high
10000 001 003
10000 008 010

View 2 Replies View Related

SQL & PL/SQL :: COBOL High / Low Values

Jun 24, 2010

My requirement is while sending a data file from oracle to mainframe, first 3 bytes for the header row should contain low values and trailer should contain high value.

How to pass oracle values to mainframe high and low values ?

View 7 Replies View Related

High Availability Of EM-Database Control

Oct 22, 2010

I have setup of two node (prod-db1, prod-db2) clustered database 11gR2 on windows 2008 R2 server. Everything is working fine at this setup.

My question is: Is there a way to make the Enterprise manager Database control run and be available at both the nodes independently. What I see that even at node 2 (which is prod-db2) the EM-DBControl is (https://prod-db1:1158/em) - which means the agent is running at node 1 (prod-db1) only.

My question is that how to make the EM-DBControl also run separately at prod-db2. My idea is to make the high availability of EM-DBControl (in case Prod-db1 machine is down).

View 2 Replies View Related

SQL & PL/SQL :: High Volume Similarity Grouping

Nov 15, 2010

I am in the very early planning stages of a project the goal of which is to identify separate organizations which may in fact be the same organization.

Our first implementation of this task was a process designed to look for a few thousand organizations in a pool of a few hundred thousand organizations. To accomplish this we made heavy use of Oracle's Text index as well as a custom index type we created which utilized n-grams. This approach worked quite well for on-demand editing of the organizations, in which a user might log in and say in addition to what we already know about organization A we also know x, y and z does that change anything and worked acceptably well for the bulk processing we did on our "known" information once a week running for a couple of hours on the weekend.

We have now been tasked with reworking this initial implementation only now we want to look at a set consisting of several million organizations for potential matches which exist within the set. As in our initial implementation we will be breaking what we know about organizations into groupings so we aren't comparing a phone number to an email address and normalizing the data as much as we can so we ignore things like case and punctuation. Even after all this we are still talking about looking for similar values in a group which might be in the tens of millions (some types of data will have more than one value per organization).

My initial thought on the problem is to use n-grams though not in the way we did in the past. The basic idea here is that we break the search values up into all the substrings it is made of and look for other values which have a high number of those substrings in common.

SQL & PL/SQL was the best place for the question, but I could not think of a better one.

View 10 Replies View Related

Performance Tuning :: Low Cost And High Cpu?

Jul 23, 2010

I am facing one performance issue, in which the query cost is very low compare to cpu cost and as a result the cpu always show the high graph.I am also attaching the gv$sql and gv$sql_plan data of this query.

This is the query:

SELECT PTLS.ITEMTYPE , PTLS.ITEMID , PTLS.STAGEID, TS.USERID, SUM(PREVIOUSHOURS) AS PREVIOUSHOURS, MIN(STARTDATE) AS STARTDATE, MAX(STARTDATE) AS ENDDATE FROM PROJECTTIMELOGSSTAGE PTLS, PROJECTTIMESHEETITEM PTSI, TIMESHEET TS WHERE PTLS.PROJECTID = :B2 AND TS.TIMESHEETID = PTSI.TIMESHEETID AND TS.USERID = :B1 AND PTSI.TIMESHEETID = PTLS.TIMESHEETID AND PTSI.ITEMTYPE = PTLS.ITEMTYPE AND PTSI.ITEMID = PTLS.ITEMID AND (PTSI.ISPWFITEM = 'N' OR PTSI.ISPWFITEM IS NULL) AND PTLS.ITEMTYPE NOT IN ('OtherTsk','NewTsk','Loc','Glb') AND (PTLS.ITEMTYPE, PTLS.ITEMID ) IN (SELECT ITEMTYPE, ITEMID FROM PROJECTTIMELOGSSTAGE PTLS1 WHERE PTLS1.PROJECTID = :B2 AND PTLS1.TIMESHEETID = :B3 ) GROUP BY PTLS.ITEMTYPE, PTLS.ITEMID, PTLS.STAGEID, TS.USERID

View 17 Replies View Related

Find High CPU User In Oracle 10g

Jan 25, 2013

clould you sand me sqary for find high cpu user in oarcle 10g

View 3 Replies View Related

CPU Time Per User Call / High Or Low?

Aug 10, 2012

I've plan to use "CPU Time Per User Call" metrics.

The thresholds are:

Warning: 8000
Critical: 10000

But this alarm raise every minute.

I think it's too low.But which is the correct value to identify performance problems?

View 4 Replies View Related

SQL & PL/SQL :: Range Comparison - Low And High Pair

Mar 11, 2011

I am trying to compare the ranges of low pair and high pair,if they are within the range then source_conn_id should remain same,else it should be updated to null,which i had written it in else block.How can I implement the IF block and what to write in that block so that source_conn_id can remain the same.

SQL> CREATE OR REPLACE PROCEDURE fp_complements_src(p_id varchar2,ftr_con_id varchar2)
2 AS
3 BEGIN
4 FOR i IN(SELECT SOURCE_CONN_ID,LOW_PAIR,HIGH_PAIR FROM COMP_TEMP1 WHERE SOURCE_CONN_ID=ftr_con_id)

[Code]....

View 2 Replies View Related

JDeveloper, Java & XML :: Update Xml Element In Oracle 10gr1 Without Using Oracle XDB

Jun 27, 2013

I would like to update an XML element without using the function APPENDCHILDXML or INSERTCHILDXML because they are not available in Oracle 10GR1.
In my database, Oracle XDB is not installed.

The following query fail with the following error : ORA-00904: "INSERTCHILDXML" : identificateur non valide

update scl_profile
set profile_data =
insertChildXML(profile_data,'/exportImportMarcheCriteria','colonnesExport',
XMLType('<colonnesExport>ENTETE_GESTIONNAIRES_AUTORISES</colonnesExport>'))
where profile_xmltype =
'fr.mipih.marches.marche.criteres.ExportImportMarcheCriteria'
and profile_type =
'eMagh2.MRGS.AccesMarche.ListeMarche.Export.OptionsExportImport';
[code]........

If i try to use the package DBMS_XMLDOM, i have the following error :

ORA-06550: Ligne 3, colonne 11 :
PLS-00201: l'identificateur 'DBMS_XMLDOM.DOMDOCUMENT' doit etre declare
ORA-06550: Ligne 3, colonne 11 :
PL/SQL: Item ignored

I think it's because ORACLE XDB component is not installed in my database.

View 1 Replies View Related

High Cluster Waits During RMAN Backup

Jul 1, 2010

We had a massive jump in cluster waits specifically; gc buffer busy acquire during an RMAN backup. We identified the cause of the waits to a few hot blocks with a table that may well need re-building in terms of ITLs and PCTFREE (Although I thorght ASSM would manage PCTFREE and PCTUSED..)

What happens during an RMAN backup that may cause huge cluster waits on hot tables? Is there some crazy redo issue going on or maybe flashback?

Either way the spike is there and we can pinpoint the activity on the database but just dont understand why RMAN would cause the issue.We have just found that the LARGE_POOL has not been set, in fact there is no SGA_TARGET either! Could this have an effect on RMAN and cluster waits?

View 3 Replies View Related







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