PL/SQL :: Frequency In Output
Jun 18, 2012
I have created scripts as follow
CREATE TABLE STUDENTS(
STD_ID Number,
STD_NAME Varchar2(25 ) CONSTRAINT SYS_C002716 NOT NULL,
PHONE_NUMBER Char(20 ),
FATHER_ADDRESS Varchar2(100 ),
[Code]...
-- Add keys for table STUDENTS
ALTER TABLE STUDENTS ADD CONSTRAINT SYS_C002717 PRIMARY KEY (STD_ID)
/
-- Table CLASSES
CREATE TABLE CLASSES(
CLASS_NAME Char(40 ),
CLSS_NUM Number(38,0) CONSTRAINT SYS_C009724 NOT NULL,
LEV_ID Number(38,0)
)
[Code]....
------------------------- ----------
abk amr
saad amr
abk OSM
saad OSM
I expected the query gives me student_name in what class name and didn't expect to get frequency in output
View 2 Replies
ADVERTISEMENT
Jun 18, 2012
I am having a particular SQL, i want to know the frequency/count of execution of this particular sql in my prod DB. how can i get this.
I get the sql in v$sql but didn't get the same record in dba_hist_sqlstat view.
View 3 Replies
View Related
Dec 18, 2012
I have a following requirement in SQL -
Requirement -
I need to list the purchase Ordrers which are not closed.
my sql query should pull if the PO is not closed in 30,90 and 150 days.
It should be shown only on 3oth,90th and 150th day only even the Purchase order is not closed on 33rd day.
View 1 Replies
View Related
Aug 25, 2011
I am working on a report which shows a summarized data from 4 different tables. I have 2 columns which shows certain amount.
The data in both the columns comes from different table.
I want to compare these 2 columns for which i placed a formula column in data model. the code goes this way:
function CF_RECV_PAYFormula return Character is
V_RESULT VARCHAR2(50);
begin
IF :TOTAL_IOU_AMT >= :EXP_AMT THEN
[code].....
The compilation is successful. but when i am running the report i am getting an error Rep-1517 :Column 'CF_RECV_PAY' references column TOTAL_IOU_AMT and EXP_AMT has incompatible frequency.
View 4 Replies
View Related
Jul 23, 2013
I'm trying to collect histograms for column COL_C of table TAB_A(150K records), So an index "BAD_IDX" will *not* be used in a query when the value is not selective.
This is my query:
SELECT COL_A, COL_B , COL_C , COL_D , COL_E , COL_F
FROM TAB_A
WHERE COL_A = 050
AND COL_B = 13012345
AND COL_C = 0
AND COL_D = 0
AND COL_D >= '07/23/2013 00:00:00'
ORDER BY COL_E ASC;
Now, I have index "BAD_IDX" on columns (COL_C, COL_E ).and the distribution of values looks like this:
select COL_C, count(*)
FROM TAB_A --very not selective for 0, selective for the rest, also no histogram
group by rollup(COL_C)
order by 2 desc;
and the result is 20k row long (20k distincts), So I'll post just the top part of it:
COL_CCOUNT(*)
-------------
158382
086356
695583772
623044169
659558964
680045564
689664263
[code]...
Now, the problem with the query was that "COL_B = 13012345" was the most selective predicate, And an index for it did not exist, so the index "BAD_IDX" is used, and is scanning 86k records (all the "0" value records for column COL_C)!
So, I created an index
Create index GOOD_IDX on TAB_A(COL_B) compute statistics;
However, that BAD_IDX index is still being used! I've thought that maybe it's because the lack of histograms for the column COL_C.I've also understood from documentation I've read that the suitable histogram type is TOP FREQUENCY, Because although I have 20k distincts here for the column COL_C, what does the difference is the 86k records of value 0.
I try to create histograms for the column:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'ANDREY'
, tabname => 'TAB_A'
, method_opt => 'FOR COLUMNS COL_C'
);
END;
/
But then when I check - I see that
1. My query is still using the wrong index
2. My histogram is height-balanced and not FREQUENCY
select histogram from dba_tab_columns where table_name='TAB_A' and column_name='COL_C' and owner=user;
The result is:
HISTOGRAM
----------
HEIGHT BALANCED
What do I need to do in order to
1. Make my query use the correct index
2. Create a FREQUENCY histogram
View 3 Replies
View Related
Jul 1, 2013
DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
View 2 Replies
View Related
Sep 25, 2013
Currently I have a requirement where I need to create 2 more output rows using each result row.
In my requirement I am populating charges table with types of charges, on each line item of charges, I need to apply 2 types of taxes and populate it along with the charge line item. I will be storing charges in table charges and the 2 taxes to be applied in taxes table respectively. For each row of charges, i need to apply these 2 taxes present in taxes table resulting in 3 rows output.
--Create tables charges
create table charges
(
charge_type varchar2(10) ,
charge number
);
[Code]....
My expected output should be like below:
Item_type amount
-------------------- ----------
charge1 100
Charge1_tax1 10
Charge1_tax2 20
charge2 200
Charge2_tax1 20
Charge2_tax2 40
how I can achieve the expected output using a single sql query
View 6 Replies
View Related
Mar 11, 2013
Following query is giving me the required output but i want to use reg_exp from same output.
SELECT TO_NUMBER ((SUBSTR (narr,
INSTR (narr, '#') + 1,
(INSTR (narr, 'DT')) - (INSTR (narr, '#') + 1)
) "Bill No"
FROM (SELECT '25 NOS. BILL BOOK FOR FAISALABAD @80/- TH A.R. PRINTER AGST BILL#21 DT:31-01-2013 TH
[code]...
Required Output using Reg_Exp
SQL>/
Bill No
----------
21
9
View 6 Replies
View Related
Feb 2, 2010
One function returns table metadata in xml form. This works for current schema but not for other schemas. Is it a privilege problem?
my code is..
CREATE OR REPLACE FUNCTION TEST.F_DBEG2
RETURN XMLTYPE
AS
Handle NUMBER;
V_OUTPUT_TOTAL XMLTYPE;
V_CNT NUMBER := 0;
BEGIN
[code]....
but when i select the object of another schema, got output.
SELECT * FROM TEST_NEW.DEPT;
Which privilege i need?
View 9 Replies
View Related
Jan 12, 2012
Here i have one PL/SQL block which will returns the age of an employee using his id.
SET SERVEROUTPUT ON;
DECLARE
v_num NUMBER;
v_days NUMBER;
[code]...
It will returns an output of 27..I tried the same in Forms 6i using a text field and a button with a trigger "when_button_pressed". when i am entering the same id of employee i am getting a totally different answer
DECLARE
v_num NUMBER;
v_days NUMBER;
BEGIN
SELECT to_date(sysdate)-to_date(dob)
INTO v_num
FROM customer_details
WHERE application_id=:block3.day;
v_days :=floor(v_num/366);
MESSAGE(v_days);
MESSAGE(v_days);
END;
It is giving me a result of -73...i cleared my problem. but i cound not understand the internal work happening inside the forms and pl sql machine.
View 10 Replies
View Related
Jun 13, 2013
percentage_pointsmarksage95336.590346.575376.550496.595347903777549750337 if i fired the statementselect percentage_points from table1 where marks=33 and age=6.5 then output should beuser has score percentage between 90 and 95i can write case condition between 1 to 100 percentages but any other condition which easy
View 1 Replies
View Related
Dec 4, 2012
When I run the below procedure it does not return any data. But it returns stating Procedure created. When I just run the query it returns the result.
So what am I going wrong here.
set serveroutput on
CREATE OR REPLACE PROCEDURE PROD.STATUS_COUNTS
(
p_count IN OUT number,
p_status IN OUT varchar2,
p_mpp IN OUT number
)
AS
BEGIN
[Code]....
View 7 Replies
View Related
Jan 2, 2013
I am getting an error while printing a sql query which is in a string inside a stored proc. I used
dbms_output.enable(1000000);
dbms_output.put_line(v_query);
and error "I get ORU-10028: line length overflow, limit of 255 bytes per line"
How do I print everything?
View 3 Replies
View Related
Mar 15, 2007
I have the following fields:
Addressln1
Addressln2
Suburb
Town
I know how to concatenate them
trim(Addressln1) || ',' || trim(Addressln2) || ',' || trim(Suburb) || ',' || trim(Town) as Address
1. I would like to know if any of the fields are empty I would like to eliminate the comma character from the string.
2. Can I replace the comma with a new line character and what character to be used in the syntax.
View 2 Replies
View Related
Feb 11, 2013
I am joining two oracle provided basic tables emp and dept and want to show result as a department detail and followed by employee detail belong to that.
Standard result of joining two tables looks like below:
deptno dname empname
10 ACCT SCOTT
10 ACCT MILLER
20 SALES JOHN
20 SALES XYZ
30 FINANCE AAA
Now I need the output as below as per a report requirement.
entity_type name/no
DEPT 10
EMP SCOTT
EMP MILLER
DEPT 20
EMP JOHN
EMP XYZ
I am using oracle 10g release 10.2.0.1.0. Can I use oracle analytic function here?
View 2 Replies
View Related
Jun 25, 2013
I have a table with data as follows
Source name address city
File Y N N
File N N Y
DB Y N Y
DB N Y N
XML Y Y N
I am trying to get output as follows
Source Y/N CountName CountAddress
File Y 1 0
File N 1 2
DB Y 1 1
DB N 1 1
XML Y 1 1
XML N 0 0
View 2 Replies
View Related
Sep 17, 2013
this PROCEDURE for Paging.
PROCEDURE cursor_example
IS
p_id NUMBER;
p_status number;
p_rownum number;
[code]...
View 1 Replies
View Related
Mar 6, 2012
To get output from a below mentioned table? can we combine a row like that?
Table: Test_Fruits
ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit
2Vegitable
4 Apple
3 Potato
-----------------------------------------
Expecting output:
Pls guid me how to get like this?
ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit 4 Apple
2Vegitable 3 Potato
-----------------------------------------
View 6 Replies
View Related
Mar 2, 2010
A function returns the metadata of named objects (Directories, Users, Tablespace....) in the form of DDL. When i execute the function in the schema having the privileges of CONNECT, RESOURCE, DBA, SELECT ANY TABLE, UNDER ANY VIEW AND EXECUTE ANY PROCEDURE, function returns the empty clob without any error. But he same function created and executed in the User having SYSDBA privilege, we get output.how to get output without SYSDBA privilege ?
CREATE OR REPLACE FUNCTION SCHEMA.DBLINK
RETURN CLOB
AS
v_meta_handle NUMBER;
v_meta_handle_trans NUMBER;
V_DOC CLOB;
V_LOB CLOB;
[code]....
View 7 Replies
View Related
Feb 15, 2012
How to add space between columns in the dbms output statement.I tried to do so in the following way which i attached.
View 8 Replies
View Related
May 26, 2012
Formatting the mail message sent using utl_mail, i have created one table and it has around 17 rows inside as sample and it may increase as well, my present email format is like very distorted and i have to have it as readable in single line .Please find below the code i have written along with test case, you may substitute your emailid and test, especially my main focus is on the lable aff.mark number where all the mark numbers are distorted and want to be arranged in line.I have attached the email format that i am getting at present also.
CREATE TABLE FAB_LOG ( MRK VARCHAR2(30))
INSERT ALL
INTO FAB_LOG VALUES ('1018017M-6001')
INTO FAB_LOG VALUES ('1018017M-6002')
INTO FAB_LOG VALUES ('1018017M-6003')
INTO FAB_LOG VALUES ('1018017M-6004')
[code]......
View 7 Replies
View Related
Nov 22, 2012
i need to extract data in excel file through sqlplus .
View 2 Replies
View Related
Dec 4, 2011
How do I modify SQL to sum and also add 2 columns to hold calculation results?
In functional terms, we need to sum two pay periods' retirement deduction amounts for a BDCA deduction code R03 to arrive at the monthly total for those deduction amounts.
In other words, to sum payroll deduction data for 2 pay periods PAYNO = 21 & 22 for (EMPLOYEE_AMT and EMPLOYER_AMT) and have the totals in new columns named EENOVR03 and ERNOVR03. Column names representing EE for employee, ER for employer, NOV for November, and R03 for deduction code.
Here's the basic SQL without calculations. I need to modify SQL to produce both EENOVR03 and ERNOVR03.
select SPRIDEN.SPRIDEN_LAST_NAME "LastName",
SPRIDEN.SPRIDEN_FIRST_NAME "FirstName",
SPRIDEN.SPRIDEN_MI "MiddleName",
[Code]...
View 12 Replies
View Related
Aug 13, 2010
If I have below two sql statment.
select count(*) from table_a;
select count(*) from table_b;
How can I output it in one row together like below
Table_a | Table_b|
10 | 20|
give me detailed example ....
View 2 Replies
View Related
Mar 8, 2010
I have attached an SQL script.I would like to have the output in the fashion given below -
PERIOD_START_DATEPERIOD_END_DATEEMPLOYEE_NUMBERFULL_NAMELoan AmountLoan TypePay Value Outstanding Amount
1-Jul-0931-Jul-0924200Jonathan48000048004000
1-Jul-0931-Jul-0924200Jonathan32600022702990
the one in bold and underlined is the Column heading.
View 5 Replies
View Related
Mar 23, 2012
we have a table which name is empl have two column (comapnyname,jobdate) i want output after passing parameter which name is month which value is like(feb 2011,or mar 2011 or apr 2011 or jun 2011 etc) output should come the nameof company with jobdate whose jobdate is equal to 3 or greate than three
name of company,jobdate whose job date in particular month is greater than three or equal to three the purpose of this we want to find only the name of company where we visit morethan three times in particular month table structure is
create table empl(companyname varchar2(10),jobdate date)
insert into empl values('ABC','1-feb-2011')
insert into empl values('ABC','10-feb-2011')
insert into empl values('ABC','21-feb-2011')
insert into empl values('xyz','18-feb-2011')
insert into empl values('xyz','1-feb-2012')
insert into empl values('xyz','1-apr-2011')
insert into empl values('ABC','28-feb-2012')
output:
after passing parameter month=feb 2011
companyname jobdate
abc 1-feb-2011
abc 10-feb-2011
abc 21-feb-2011
View 3 Replies
View Related
May 10, 2011
i have created a table called table2,with only one column as text,
SQL> select regexp_replace(text,'[:]',chr(10)) text from table2;
TEXT
--------------------------------------------------------------------------------
1
amar
11-jan-2011
15000
2
manju
22-feb-2011
20000
and i have to get output like this,
TEXT
--------------------------------------------------------------------------------
ID
1
NAME
amar
DATEOFJOINING
11-jan-2011
SALARY
150000
is it possible to get output like above?
View 20 Replies
View Related
Jun 10, 2011
I have output from select something like :
A_PID B_TEXT id_f
-------------- ----------------------------- -----------
23 B_text sample 9888888
and my question is how can I format the output to have it thinner .
I can do it for text: COLUMN FORMAT B_TEXT A5, but i dont know to apply it for fields with number e.g. A_PID column.
View 4 Replies
View Related
Nov 7, 2010
I'm trying to output 1/31/06 into January 31, 2006.Here is what I have so far
declare
v_dt DATE;
v_tx VARCHAR2(2000);
begin
[code]...
But it still won't display month as January.
View 11 Replies
View Related
Oct 15, 2013
I need to have columns dynamically defined in my SQL based on the column values in the table.
Selct emp_no,dept_no,grade from emp;
emp_no dept_no grade
----------------------------------
1 10 A
2 20 B
3 20 A
4 10 C
5 10 A
6 20 C
7 10 B
8 20 C
I need the output as
dept_no Count_A Count_B Count_C
---------------------------------------------
10 2 1 1
20 1 1 2
I can write hard coded sql to get this output but I need the count columns to be generated dynamically as there could be new entries in the grade column later. For example if there is a entry as D for the grade column my SQL should have a column count_D.
View 3 Replies
View Related
