PL/SQL :: How To Display Required Data From Emp Table
Dec 26, 2012
This is my first post in this portal. I want display the details of emp table.. for that I am using this SQL statement.
select * from emp where mgr=nvl(:mgr,mgr);
when I give the input as 7698 it is displaying the corresponding records... and also when I won't give any input then it isdisplaying all the records except the mgr with null values.
1)I want to display all the records when I won't give any input including nulls
2)I want to display all the records who's mgr is null
Is there any way to incorporate to include all these in a single query..
i like to display a Table data like the below format,
Output:
EMPNO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 00094 122 153 145 224 245 545 114 544 444 111 555 222 00095 233 123 145 224 205 545 114 444 444 111 555 222 00096 163 123 145 224 215 545 114 551 444 111 555 222 00097 163 0 145 224 215 545 114 551 444 111 555 222 conditions:
where condition: where year = 2007
Table Structure:
create table HR_PAYSLIP ( EMP_NO VARCHAR2(6) not null, YEAR NUMBER(4) not null, MONTH NUMBER(2) not null, BASIC_PAY NUMBER(9,2), )
Insert Command;
INSERT INTO PAYSLIP (EMP_NO, YEAR,MONTH,BASIC_PAY) VALUES(00046, 2007, 1, 2314);
Pls Note: The above table data i have mentioned is an example with employee numbers and the basic_pay for all months in the particular year 2007, the employee no may be more and that must be displayed only one time like above for year 2007, and if the basic salary is zero for a month then it should be displayed as zero for a particular month
CREATE TABLE "TEST_JET" ("K1" NUMBER, "K2" NUMBER, "K3" NUMBER, "K4" VARCHAR2(1)) ; REM INSERTING into TEST_JET Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'I'); Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'U'); Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'D'); Insert into TEST_JET (K1,K2,K3,K4) values (1,2,2,'U'); Insert into TEST_JET (K1,K2,K3,K4) values (1,2,2,'D'); Insert into TEST_JET (K1,K2,K3,K4) values (1,3,5,'I'); Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'U'); Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'D'); Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'T'); [code]....
based on the above result set , for a particular group ,only that op will be retained which comes out in the query . say for example , we have got 1,2,3,'D' for group 1,2,3
now since we have got the D Operation from the above query , i don't need the other two rows .i.e. (1,2,3,'I'); (1,2,3,'U');
what is the best way to delete the data we don't want retaining the rows we want ,using a single sql statement . Also , for the result set row 7,7,7,T I first need to delete the group containing T operation, and insert two new rows .i.e. 7,7,7,D and 7,7,7,I .
I have a sample table where I am arranging data in required format using 'decode' by hardcoding all values, how can we do same using pivot method, can pivot be only used when we want aggregate values in oracle?Also can't we do what i did, using generic max(decode) with select query, if it were a large table (i don't know which would be pivot column here?) ?
I have a contact table that will be used for more than one entity (Customers, Sites, Suppliers, etc.) Using APEX, when we define the master-detail, we need to add a FK to the Detail table -- otherwise the Master-Detail Forms can not be implemented.
I am looking for a way to defined/implemented tables so that I can utilize the same table (Contacts) for more than one Master-details relationship.
I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.
Consider EMPLOYEES table and If an employee 'A' joined in Jan month then he should come under JAN, if employee B and C joined in MARCH month than both has to come under MAR and so on..
OUTPUT: JAN FEB MAR APR MAY JUNE JULY AUG SEP OCT NOV DEC A B D E C
I have a query on displaying data as per my requirement. I have created a table called sales it has four columns
create table sales(country,state,district,sales); and am inserting some same data
insert into sales('india','TN','Chennai',100); insert into sales('india','TN','KPURAM',120); insert into sales('india','TN','Bangalore',35); insert into sales('india','ANDR','Guinder',100); insert into sales('india','ANDR','Nellai',76); insert into sales('london','city-a','xstreet',89); insert into sales('london','city-a','binroad',100);
select * from sales;
country state district sales india TN chennai 100 india TN KPURAM 120 india TN Bangalore 35 india ANDR Guinder 100 india ANDR Nellai 76 london city-a xstreet 89 london city-a binroad 100
the data is displayed in this format. How i am trying to display data.
Web page that retrives data from SQL database and display it to the user in well formatted manner. Data retrieved depend upon criterias selected by the user. But sometimes data retrieved is very large. I want to display records to the user page wise, i.e. 100 records on first page and next 100 records displayed when user clicks next button. This means only 100 records should be retrieved when user first select search criteria, next 100 records retrived when he clicks next button and so on, as to reduce data transferred from server to client. how to achieve this by using single sql query as soon as possible.
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 in single line..find below the code i have written along with test case, you may substitute your emailid for test, especially my main focus is on the column mrk , where all the mrk are comming in one line but i want to be arranged in a line not more thant 10 rows, if it exceeds it should go to next line.
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') INTO FAB_LOG VALUES ('1018017M-6005') INTO FAB_LOG VALUES ('1018017M-6006') [code]....
how to display the data which is shown below without duplicate records in compid and compname and all policy_id's should be there while excuting this query iam getting this data.
select distinct comp_id as compid, comp_disp_name as company, plcy_id as policyid,
19734 Save the Children 9013 GPA 19734 Save the Children 9012 GMC 20097 JMT 9486 GTL 10890 Steelco Gujarat Ltd. 9727 CAR 17330 Golden Jubilee Hotels Limited 8915 CGL 23117 NBHC 9093 GMC 17542 Heinz India 10693 Fire 19821 KSK Fabricators 10341 D&O 3769 Jones Lang Lasalle India 9199 WC 19821 KSK Fabricators 10340 WC
I have a table where multiple combination of records are store and i want to display data in range format as below, there is any way to group data as below.
I need a query to display the birth date of all emp in such a way that if i run a query today the dob which lies between today & today+15 days data must be displayed.
I am stuck somewhere in my project, situation is that we are trying to display data gathered from 3 different tables, we used cursors for this, here is the cursor declaration.
DECLARE procedure DocInfoCur(myWhere IN varchar2) IS
init_dept_id number; init_dept_name varchar2(40);
rcvg_dept_id number; rcvg_dept_name varchar2(40);
CURSORCUR_DOCINFO IS SELECT DISTINCT I.CTN, I.DIARYNUMBER, I.INITIATINGDATE, T.TYPENAME, I.INITIATINGDEPT, I.DOCUMENTSUBJECT, I.DESTINATIONDEPT FROM DOCUMENTINFO I, DOCUMENTTYPE T, DEPARTMENT D
[code]...
This displays all the records out there.
Now, I have a search form where I need to filter the same data with different parameters, I have compiled the entered data in one string, now i need to join that string at the end of where clause of my cursor, how can I do that, i have tried concatenating both but received compile error, is there a way out in the current scenario???
I have to write a proc to display data based on user input for t_grp, and it should exclude grp0
So below is the proc I have written, and I want to display the report for all t_grp when user gives user_input as All or gives null. How can I acheieve this is in procedure.
i have a problem to view tens of millions of data in oracle 11g i have table Book_Issue that have tens of millions of data but when i execute query to see all the data select * from Book_Issue
it only view 5.000 data
what query should i execute to view million data in oracle 11g
I have the following requirement, where I have to display the data based on the group and links when input is given as month. I have written the following code, which is good to display for group. But I want to display for all the groups. CREATE TABLE target_data ( T_LINK VARCHAR2(50) , t_mon varchar2(6) , t_grp varchar2(30) , t_views NUMBER
select * from test where col2=123 will give me null because It doesnt have any values, But can we display some harcoded value when I do not have anything ?
I am bit confused to display all the rows in a column using stored procedure like
In sqlserver: create procedure test as begin select * from table; end in sqlserver it is working perfectly.
In oracle: create or replace procedure test is begin select * from table; end;
But in oracle i am getting an error like select * into something like that.I understand the error. i.e. i need to move the column values into variables.if it a single row then there will be no problem.i want to display all the rows in the table at one time.i can not use cursors.
how to display all the records in a table ,i am passing the table name as in param to the procedure/function suppose if i pass emp table name it will display 14 rec, if i pass dept it will display 4 records.
