PL/SQL :: How To Get Total Number Of Occurrences Based On Value Of Column

Apr 20, 2013

here on your forum but has been following several threads ever since.So anyway here is the thing, I have a query that should return count the number of rows depending on the value of SLOT. Something like this:

WIPDATAVALUE          SLOT             N            M
1-2                   TRALTEST43S1     1            3
1-2                   TRALTEST43S1     2            3
3                     TRALTEST43S1     3            3
4-6                   TRALTEST43S2     1            4
4-6                   TRALTEST43S2     2            4
4-6                   TRALTEST43S2     3            4
7                     TRALTEST43S2     4            4-----

As you can see above, on the SLOT TRALTEST43S1, there are three occurrences so M (Total number of occurrences) should be three and that column N should count it. Same goes with the SLOT TRALTEST43S2. This is the query that I have so far:

SELECT DISTINCT
WIPDATAVALUE, SLOT
, LEVEL AS n
, m
FROM
(
  [code]...

I think that my current query is basing its M and N results on WIPDATAVALUE and not the SLOT that is why I get the wrong output. I have also tried to use the WITH Statement and it works well but unfortunately, our system cant accept subquery factoring.

View 23 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Get Total Number Of Occurrences Based On Value Of Column

Apr 20, 2013

I have a query that should return count the number of rows depending on the value of SLOT. The expected result will be like this:

WIPDATAVALUE SLOT N M
1-2 TRALTEST43S1 1 3
1-2 TRALTEST43S1 2 3
3 TRALTEST43S1 3 3
4-6 TRALTEST43S2 1 4
4-6 TRALTEST43S2 2 4
4-6 TRALTEST43S2 3 4
7 TRALTEST43S2 4 4

The M column is used to count the total number of occurrences of a SLOT. Now, as for the N field, this is used to count the occurrence of the SLOT. In my example for the SLOT TRALTEST43S1, it has three occurrences so M will be 3. Why 3, is because of the WIPDATAVALUE. The WIPDATAVALUE of TRALTEST43S1 is 1-2 and 3. 1-2 WIPDATAVALUE signifies two occurrences (one to two) and 3 signifies only one occurrence. As for N, it should just count the number of occurrence. To further explain, see below:

WIPDATAVALUE SLOT N M
1-2 TRALTEST43S1 1 3 -> First occurrence in the total of 3
1-2 TRALTEST43S1 2 3 -> Second occurrence in the total of 3
3 TRALTEST43S1 3 3 -> Third occurrence in the total of 3
4-6 TRALTEST43S2 1 4 -> First occurrence in the total of 4
4-6 TRALTEST43S2 2 4 -> Second occurrence in the total of 4
4-6 TRALTEST43S2 3 4 -> Third occurrence in the total of 4
7 TRALTEST43S12 4 4 -> Fourth occurrence in the total of 4

This is the query that I have so far:

SELECT DISTINCT
WIPDATAVALUE, SLOT
, LEVEL AS n
, m
FROM
(
SELECT
WIPDATAVALUE
, SLOT
, (dulo - una) + 1 AS m
[code]....

I think that my current query is basing its M and N results on WIPDATAVALUE and not the SLOT that is why I get the wrong output. I have also tried to use the WITH Statement and it works well but unfortunately, our system cant accept subquery factoring.

View 2 Replies View Related

SQL & PL/SQL :: Update Column With Total Number Of Records

Apr 3, 2012

Table : Customer

Cust_idNameid
227ABC1
227ABC2
227ABC3
228XYZ1
228XYZ2
228XYZ3
228XYZ4

In one oracle table (Customer) we have multiple cust_id based on that, we want to populate id column as cust_id record count.

I have id column right now null and want result same as above.

View 1 Replies View Related

SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy

Oct 15, 2013

I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically

ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')

500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498

View 1 Replies View Related

Text :: How To Get Number Of Occurrences Per Document

Nov 8, 2013

I have a table of documents (excel, word, pdf, txt) stored in blob column. I want that SCORE returns the number of occurrences within each document, so I'm using this piece of code: 

SELECT bdt.*, SCORE(1) AS score_1, DBMS_CRYPTO.HASH(archivo, 3) AS cod_hashFROM crg_ctx_archivos_recibidos bdt

WHERE CONTAINS(archivo,'<query>   <textquery lang="SPANISH" grammar="CONTEXT">DEFINESCORE(paz, OCCURRENCE * 0.1)</textquery>    <score datatype="INTEGER" algorithm="COUNT"/></query>', 1) > 0

ORDER BY SCORE (1) DESC; 

However I'm getting a maximum score of 10, but I know one of these documents have 490 matches. How can i get the exact number of matches per document? I'm using Oracle 11.2

View 3 Replies View Related

SQL & PL/SQL :: Finding Number Of Pattern Occurrences In String?

Feb 4, 2012

The code which I am working on consists of an incoming dynamic string which be in the form of binary digits. The max size of the string will be 12 digits. For example, the string can be '111011000001', '000000000000', '111111011111', etc.

I need to find the number of occurences of '111' in the incoming string. Say in the 1st example, result will be 1, in the 2nd example result will be 0, and in the third example, the result will be 3.

I have been trying to capture the string length and replacing the variables '111' to find the number of occurences, but it isn't giving me the result that I want. This is what I have tried

SQL> conn hr/hr
Connected.
SQL> show user
USER is "HR"

[Code]....

I searched the forum and found a similar topic, and following that guideline, I even tried dividing the string with the length of the pattern. It works in some scenarios (the first and second examples mentioned below), while it fails in some scenarios (third example mentioned below)

SQL> select (length('11101110111') - length(replace('11101110111','111','')))/length('111') as occurences from dual;

OCCURENCES
----------
3

SQL> select (length('110111110111') - length(replace('110111110111','111','')))/length('111') as occurences from dual;

OCCURENCES
----------
2

SQL> select (length('111111111111') - length(replace('111111111111','111','')))/length('111') as occurences from dual;

OCCURENCES
----------

SQL>

View 13 Replies View Related

SQL & PL/SQL :: Dynamically Concatenating Column Values Based Upon Number Of Grouped Columns

Feb 17, 2011

My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.

SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......

What will be the case when I need to concatenate for more number of values.

like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.

View 10 Replies View Related

Reports & Discoverer :: How To Find Page Number And Total Number Of Pages

Jan 26, 2010

i am using oracle developer 6i report builder i required this type of query

example

if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;

return (TRUE);
end;

but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.

View 34 Replies View Related

Forms :: Change Total Based On Checkbox Value

Jun 25, 2013

I have created a custom form which fetches certain records. There is a column called invoice_create_flag which is check box. By default when we query this form, value of check box is 'Y'. Another column is Amount.

Initially when form is called, all the records are selected. Hence it should show the total of all the records. Now If the users unchecks any record, System should deduct the amount of that particular record from the total amount.

Attached is form screenshot for reference.

View 1 Replies View Related

Reports & Discoverer :: How To Compare Current Page Number With Total Page Number

Feb 19, 2013

I have a report created in Reports6i. I have two fields at the bottom

1.Page Total
2.Voucher Total

I do not want to print the Page Total if the total number of pages = 1.

How can I achieve this?

View 1 Replies View Related

SQL & PL/SQL :: How To Show Total Number Of Department

Apr 19, 2013

how to show total number of department with their department name assign to employee table.

View 2 Replies View Related

SQL & PL/SQL :: Get Total Number Of Rows Returned By Query?

Sep 17, 2010

DECLARE
l_query VARCHAR2(4000);
TYPE cursor_type IS REF CURSOR;

[Code].....

How can I get the total number of rows returned by the query?

I want to be able to check omething like c1.ROWS = 0

View 4 Replies View Related

SQL & PL/SQL :: Query To Add Total Number Of Count Of Each Table

Jun 13, 2011

I want to know that whether we can write a query to add total number of count of each table.

for example:

select count(*) from emp;

count(*)
14

select count(*) from emp_1;

count(*)
15

select count(*) from emp_2;

count(*)
16

My question is that is it possible to add this all the three results and get the value as 45.

View 3 Replies View Related

Forms :: How To Get Total Number Of Record In Form

May 20, 2011

how to get total number of record in form

View 3 Replies View Related

SQL & PL/SQL :: Total Number Of Hours And Minutes Of Given Dates

Sep 28, 2011

SELECT emp.emp_id, emp.ename, TRUNC (attendancedatetime),
TRUNC ( ( 86400
* (MAX (attendancedatetime) - MIN (attendancedatetime))
)
/ 60
)
- 60

[Code]...

Following is the out put:

EMP_IDENAME Date Min Hrs
10013Javed Iqbal09/20/2011 00:00:0036.007.00
10013Javed Iqbal09/21/2011 00:00:0027.007.00
10013Javed Iqbal09/22/2011 00:00:0049.007.00
10013Javed Iqbal09/23/2011 00:00:000.000.00
10013Javed Iqbal09/24/2011 00:00:000.000.00

i need the TOtal sum of Minutes and Hrs e.g 7+7+7=21 and also minutes but if minutes total increase from 60 minutes then it should add to hrs .how to get sum.

View 2 Replies View Related

How To Store Total No Of Updated Rows (number) In A Variable

Jun 21, 2011

how to store total no of updated rows (number) in a variable after executing an updation query using script

View 2 Replies View Related

Forms :: Retrieve Total Number In Details Block?

May 21, 2011

I am working on form which consist of two block, now i need to know total record in detail block, but in form structure i have multiple details entry aginst 1 master entry and after going for next master entry the details of privious master entry are going for posting that's why i am unable to use the currnt_record function. function to retrive the total number of records in details tab.

View 2 Replies View Related

Server Administration :: Getting Total Number Of Processes And Currently Used Process In Oracle?

May 4, 2012

is there any view in oracle for getting the total number of processes and currently used process in oracle? i am using oracle 11.2.0.1.0.

View 3 Replies View Related

Forms :: How To Show Total Number Of Records In Text Item

May 3, 2013

I have a multi record field of five rows. And 3 values.

the values are

IT
CSE
ECE

And i have one text item name is COUNT.

how to show the total number of records in Text item .

View 10 Replies View Related

SQL & PL/SQL :: Display Total Number Of Employee Working Under President In Emp Table

Apr 27, 2012

display the total number of employee working under president in emp table

View 5 Replies View Related

Creating A View To Show Employee Names / Age And Total Number Of Projects Assigned

Apr 14, 2009

I have following tables:

EMPLOYEE (E-Number, Name, Department, age)
ASSIGNMENT (E-Number, P-Number )
PROJECT( P-Number, Project, Manager)

Create a view to show employee names, age and total number of projects they are assigned to

View 2 Replies View Related

Reports & Discoverer :: Value In Table Column Based On Some Existing Column Value Automatically Without User Intervention

May 15, 2011

i have two questions.

(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'

(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.

View 3 Replies View Related

SQL & PL/SQL :: Add Word Grand Total Under Tester Column After Roll Up

Sep 17, 2010

Below is the statement I am working with. When I get the result the under the Tester column it is a empty space after the total has been done. How can I add the word "Grand Total" under the tester column after the rollup.

SELECT BG_detected_by Tester, CONCAT(Round(count(bg_target_rcyc) / count(bg_status)* 100), '%') Percentage_Compeleted,count(BG_Target_RCYC) Total_Target_Cycle,count(bg_status) Total_Defects
From Bug
where BG_STATUS = 'Open'
GROUP by Rollup(BG_DETECTED_BY,BG_STATUS)
having (grouping(bg_detected_by) = grouping(bg_status))

Tester Percentage_completed Target Defects
empty space --->Grand Total

View 1 Replies View Related

SQL & PL/SQL :: Trigger Which Changes Content Of One Column Based On Information Within Another Column

Sep 10, 2010

I am trying to create a trigger which changes the content of one column based on the information within another column.

For Example, if the 'ITEMQUANTITY' field drops below 1 then I want the STATUS column to say 'Out Of Stock'.

View 23 Replies View Related

Sort Ascending Or Descending Of Last Total Column Of Matrix Report R6i

Nov 3, 2010

How can I Sort Ascending or Descending of Last Total Column of Matrix Report R6i i.e. F_SumsalPerempno, summing total salary of each empno at end of each row.

I need the employee paid highest amount of total salary during the year to appear on first row, while months to display as per original order.

View 2 Replies View Related

Application Express :: How To Show Column Headers Before Grand Total

May 23, 2013

In a classic report, I'm using the Sum functionality and breaks by First Column to get subtotals and report total. I Repeat Headers on Break which works great for the subtotals but I would like for the report to display the column headers above the report total for easier reading. If the last grouping has a lot of data, the user needs to scroll up to read the column headers when looking at the Grand Total.

Is there a way to Repeat Headers prior to report total?

View 3 Replies View Related

Application Express :: How To Calculate Total Column Dynamically In Tabular Form

May 27, 2013

I am trying to calculating the total column based on two columns, Total=unitprice*no.of items

I wrote this Javascript in page properties>javascrpt>Execute when Page Loads

"$('input[name=f05],input[name=f06]').change( function(){
parent_row = $(this).parents('tr:first');
sal = ( parent_row.find('input[name=f05]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f05]').val() );
comm = ( parent_row.find('input[name=f06]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f06]').val() );
total = sal * comm;
parent_row.find('input[name=f07]').val(total);
});

But its not Giving any result in total column?

View 3 Replies View Related

PL/SQL :: Get DAY Based On Current / Given Territory From Number Between 1 And 7?

Jan 1, 2013

Looks like I am at the end of my wits here but how to get the "DAY" based on current/given territory from a number between 1 and 7? Oracle has functions to extract day of the week but I can't find the other way round.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> alter session set nls_territory=AMERICA;

Session altered.

SQL> select to_char (sysdate, 'D') from dual;

T
-
3

SQL> alter session set nls_territory=GERMANY;

Session altered.

SQL> select to_char (sysdate, 'D') from dual;

T
-
2

I need a way to know that number "3" on database with NLS_TERRITORY = AMERICA means a "Tuesday" or number "2" on a database with NLS_TERRITORY=GERMANY means "Tuesday" as well.

View 9 Replies View Related

PL/SQL :: Insert Number Into Column With Type  NUMBER(10,0)

Sep 28, 2012

my column type is NUMBER(10,0) ,it accept the input value from text field I using TO_NUMBER(?) to insert value into table, is the a way to handle if the input is 'aaaaaaaaaa' not digit?

View 6 Replies View Related

SQL & PL/SQL :: Count Number Of Record Based On Some Condition

Jun 28, 2013

i have two tables one as test_master and other one limit master.

test master table structure

ct_id ct_a ct_b ct_C ct_d ct_e...... etc
1 -- -- -- -- --
1 -- -- -- -- --
2 -- -- -- -- --
limit master structure

limit_id ct_a_limit ct_b_limit ct_c_limit ct_d_limit ct_e_limt ..... etc
1 -- -- -- -- -- -- -- --
2 -- -- -- -- -- -- -- --

ct_id and limit_id is match column used for mapping.

now i want the total count of no of records in test_master in which test_master column value not exceed limit master column limit value if any one column exceeds its limit then the same row will be in my count criteria.

how to achieve the same using sql/oracle query.

View 7 Replies View Related







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