SQL & PL/SQL :: Count Values From TOTAL_fault Column From Fact Table
Nov 22, 2012
I have two tables with below structures.
desc Fault_Dim
Name Null Type
------------------- -------- ------------------
KEY NOT NULL NUMBER(38)
SYSTEM_NAME VARCHAR2(3 CHAR)
PRIORITY VARCHAR2(40 CHAR)
SEVERITY VARCHAR2(40 CHAR)
TYPE VARCHAR2(40 CHAR)
STATUS VARCHAR2(40 CHAR)
CREATE_DT DATE
SUBTYPE VARCHAR2(255 CHAR)
[code]...
The Severity column has values as below 1,2,3, My requirement is to show results as in the attatched image.Where the count values are values from TOTAL_fault column from the fact table.
I believe this is possible in hierarchial queries and pivorting
View 1 Replies
ADVERTISEMENT
May 13, 2013
I'm going to do some testing, and for that I require to retrieve some data based on a single column e.g test_data_col, which -
1. Has 3 or more count(test_data_col) for a given set of group by columns e.g grp_col1, grp_col2, grp_col3
2. Within the set of rows retrieved, that particular column holds some duplicate values. I don't need the duplicates displayed, just know if duplicates exist or not.
This might explain what I'm trying to do -
grp_col1, grp_col2, grp_col3, test_data_col
1, A, xyz, HELLO
1, A, xyz, HELLO
1, A, xyz, BYE
1, A, xyz, GOODBYE
2, C, pqr, WELCOME
2, C, pqr, GOOD MORNING
2, C, pqr, BAD MORNING
So for condition 1, I do something like this -
SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3
FROM test_tab
GROUP BY grp_col_1, grp_col2, grp_col3
HAVING COUNT(test_data_col) >= 3;
In this same query, I want to do something that will tell me if the aggregate COUNT(test_data_col) has any duplicate values within it. Again, displaying the duplicates is not important here.
SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3,
/*some logic*/ dup_val
FROM test_tab
GROUP BY grp_col_1, grp_col2, grp_col3
HAVING COUNT(test_data_col) >= 3;With the proper coding to replace /*some logic*/, I get following values -
cnt, grp_col_1, grp_col2, grp_col3, dup_val
4, 1, A, xyz, Y
3, 2, C, pqr, N
I just gave dup_val column to explain what I'm trying to achieve.. any other way to know the existence of duplicates in the count aggregate will be fine.My Oracle version is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
View 2 Replies
View Related
Aug 30, 2011
I have two fact table and both are partitioned on same key and have same columns. Is it possible to move the partition of one fact table to another.
View 2 Replies
View Related
Sep 27, 2010
I created the 32 hash partition on a fact table. Based on hash parititon technique it should evenly distribute data accross the different partition.But when i analyze the table and check the distribution its not at all even.
P_Name Tabspace num_Rows
SYS_P21TBS_TBS0
SYS_P22TBS_TBS0
SYS_P23TBS_TBS0
[Code]....
View 5 Replies
View Related
Dec 23, 2010
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
View 9 Replies
View Related
Aug 9, 2010
I need to transfer 6 million records from fact tables to history table .. What is the better and fast process to do that.
View 3 Replies
View Related
Nov 14, 2013
im trying to insert these results to my fact table (fact_apartments) with the following:
INSERT INTO fact_apartments(avg_price, segmentid, projectid)
SELECT (avg(price), segmentid, projectid)
FROM projectdetails
WHERE projectid = '1';
but it return with the missing right parenthesis.
View 11 Replies
View Related
Dec 3, 2008
I'm having a table which has a column which has values given inside square brackets.
[[[123412]]] ,[[[[werer34]]]],'[[qw2_w3rt]]
Now, the requirement is to get the values which are inside the innermost square brackets.Such as in
First case : 123412
Second one: werer34
Third : qw2_w3rt
Only the values inside are needed. Is there any way to achieve it using Pl-Sql or just using Sql ?
View 3 Replies
View Related
Apr 13, 2011
I have table with values :
PROV_IDMEASURE_IDPERCENTAGE
Z0000221P114 45
Z0000135P115 68
For the column all possible values are ( P102,P101,P103 etc toP124). I want to create a view ( if possible ) from the above with data output as :
PROV_ID P101 P102 P103 ............................P124
z000234 23 45 60 72
basically this view has columns based on the previous tables column ( MEASURE_ID) values and the values will be corresponding value in column Percentage.
View 2 Replies
View Related
Aug 16, 2013
I have a SP for comparing 80 diff column values in 8 table pairs and it is taking a huge lot of time to process as I have to process around 10k records.
View 5 Replies
View Related
Jun 17, 2010
There is a table with column holding 3 NULL values one each in every record. When a count function is applied to the column with a filter on NULLs,it returns 0 instead of 3.
But when count(*)is applied,expected result is returned. Would be interested in knowing about this strange behavior of NULL/Count.
View 3 Replies
View Related
Oct 16, 2012
I am using left outer join to fetch PRSN_KEY .I need to find null values in B.PRSN_KEY. I am using below query but its giving me 0 count.
select count(*) from (
Select A.PRSN_KEY AS AKEY,B.PRSN_KEY AS BKEY from CD03955P.H_CM_EEST_EEOR A LEFT JOIN CD03955P.H_CM_EEST_EEOR B
ON
A.PRSN_KEY =B.PRSN_KEY
where
A.CAT_ID=111
AND
A.DATA_SOURCE='PEN_CO'
AND
B.CAT_ID = 1 and B.DATA_SOURCE ='PEN_EEST'
AND B.CAT_CD IN ('ACTIVE','LOA','LOAWP','LOAMLP','LOAMLN')
AND B.EFBEGDT < A.EFBEGDT
)
where BKEY IS NULL
View 8 Replies
View Related
Jun 30, 2010
I have a porblem. I have one table with the following strucuture:
col1 varchar2(30)
col2 varchar2(30)
col3 varchar2(30)
col4 varchar2(30)
In each record of the table some of the columns have 'UNDEFINED' as column value.
I need to count COLUMN WITH 'UNDEFINED' as column value in each record.
View 11 Replies
View Related
Jul 25, 2013
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
View 2 Replies
View Related
Apr 10, 2013
On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.
I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.
So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:
a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?
The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.
---
"Application Express 4.2.1.00.08"
View 2 Replies
View Related
Jun 19, 2012
I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?
View 3 Replies
View Related
Jul 19, 2013
CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
View 4 Replies
View Related
Mar 1, 2012
I have a table with the following column and data is like this.
SQL>CREATE TABLE test (
column1 varchar2(50));
SQL>INSERT INTO test VALUES('ABC XYZ');
SQL>INSERT INTO test VALUES('MNO PQR');
SQL>INSERT INTO test VALUES('ABCD ABC');
SQL>INSERT INTO test VALUES('PQR MNOP');
[code]....
View 13 Replies
View Related
Aug 5, 2010
I have to create a hash partition on fact tables.. we can use temp tablespace or permanent tablespace.
View 10 Replies
View Related
Jun 29, 2010
Need a query to find the count of presence of delimiter "," in column VAL for below mentioned table.
Table:
CREATE TABLE N_B_S_1
(
NUM NUMBER,
VAL VARCHAR2(20)
)
Data:
INSERT INTO N_B_S_1 VALUES(1,'A,B,C');
INSERT INTO N_B_S_1 VALUES(2,'D');
INSERT INTO N_B_S_1 VALUES(3,'ER,NF,G,H,XK');
INSERT INTO N_B_S_1 VALUES(4,'LQW,MBV');
INSERT INTO N_B_S_1 VALUES(5,'KS,YJ,WE,RQ,PM');
COMMIT;
Required output:
num, count of delimiters
===== ===========
1 2
2 1
3 4
4 2
5 4
View 5 Replies
View Related
Jun 25, 2013
i want to count group above report records! want to count department group employees there in time is not null!
my report query
SELECT div.division,
DEP.DEPARTMENT,
DEP.STRENGTH,
E.EMPLOYEECODE,
E.NAME,
DES.DESIGNATION,
[code].....
im doing it with formula column!
function CF_PRESENTFormula return Number is
v_emps number;
begin
select count(1)
into v_emps
[code]......
View 18 Replies
View Related
Oct 28, 2013
I am searching the simplest way for ad hoc MINUS.I do:
SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
View 6 Replies
View Related
May 20, 2011
I had a table( T1). Five columns exists in the table. The table data is like below
C1C2C3 C4C5
--- ----- ---- ----- ----
JAN FEB MAR APR MAY
One Two Three Four Five
I need the output like below one
JANFEBMARAPRMAY
---- ----- ----- ---- ----
OneTwoThreeFourFive
View 13 Replies
View Related
Sep 24, 2012
I have a table employee
name title
A manager
B sales
c manager
D engineer
E marketing
I want to know how many people has title as manager?
the answer is 2 how to do the count, I can't do count(title), right ?
View 2 Replies
View Related
Apr 23, 2012
I want the number of UNITS with the Status EN/PL for AREA 3/6
ID 1 has area 3 and 6 but only EN so count 1 (no PL)
ID 2 has area 6 but DR so no count
ID 3 has area 3 and 6 for EN and PL so 1 for each
ID 4 has area 6 three times and 3 once so count.
CREATE TABLE DAN_T1
(
ID varchar(8),
AREA varchar(8),
UNIT varchar(8),
STATUS varchar(8)
)
[Code]...
WANT:
IDENROLLEDPLANNED
11 0
20 0
31 1
41 3
View 2 Replies
View Related
May 4, 2010
find the Test Case below.
--Creation of Table
create table tb1
(ID number(4),
event varchar2(20),
vdate date);
--Inserting Values into the Table.
INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')
[Code]...
--Selecting data from Table.
SELECT * FROM TB1;
ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10
10 rows selected.
how can i display the data as below format using Oracle 9i SQL.
IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10
View 4 Replies
View Related
May 6, 2009
I have written a script to get count of each tables of a schema.
DECLARE CURSOR c1 IS
select distinct table_name from all_tab_columns where OWNER='PLAY';
countrows NUMBER;
[Code].....
View 1 Replies
View Related
Mar 31, 2009
SQL> select count(*) from tab;
COUNT(*)
----------
63
I need to find out how many rows in each table.I've tried with cursor.
View 9 Replies
View Related
Sep 9, 2012
One of my colleaque gives the below informations. it displays table count information while gathering stats. But I dont know from which view he picked the historical information. pls let me know the view name to get this detail.
NUM_ROWSANALYZETIMESAMPLESIZE
79736508-Aug-121594730
82169559-Aug-121643391
81993579-Aug-128199357
1035866010-Aug-122071732
[Code]....
View 3 Replies
View Related
Mar 11, 2013
We have table, which maintain log record of gl table. I don't know how much data exist in that table but problem is taking too much time while counting whole records.
View 11 Replies
View Related
