SQL & PL/SQL :: Display Data In Required Format By Select Query?

Dec 12, 2012

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

So how to write a Query for that?

View 9 Replies


ADVERTISEMENT

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..

View 9 Replies View Related

SQL & PL/SQL :: Query To Select Required Translators

Jul 13, 2010

i have a good query but I thought i know the solution but actually I didn't it's very simple and straight forward but i didn't catch the rope terminal to follow now I have the following code

CREATE TABLE TRANSLATORS
(Tr_code VARCHAR2(8),
Tr_name VARCHAR2(50),
Tr_age number(2),
Tr_location varchar2(25),
constraint PK_TR_CODE PRIMARY KEY(Tr_code)
);
[code]....

NOW I NEED A QUERY which will select the only translator who knows the languages written in where clause like if i specify two languages like 'italy' and 'english' the query should retrieve to me GH and SE also if i passed languages 'spainsh' and 'persia' it should return JH only

but if i passed languages like 'Italy' and 'Dutch' it should not return any thing (just : no rows selected)

View 14 Replies View Related

SQL & PL/SQL :: Arranging Data In Required Format Using Decode By Hardcoding All Values

Feb 26, 2012

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?) ?

Table is
SQL> CREATE TABLE NOWT
2 (COURSE VARCHAR2(20),MEMBER VARCHAR2(20));
[code]....

how can we do that using pivot in Oracle?

View 6 Replies View Related

SQL & PL/SQL :: Display Data In Grouping Format

Oct 13, 2010

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.

View 5 Replies View Related

SQL & PL/SQL :: To Display Data In Range Format

Apr 25, 2012

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.

create table ot_shop_Rec ( item varchar2(12), it_name varchar2(20),rev number, qty number)
drop table ot_shop_rec

insert into ot_shop_rec values ( '1018001-1001', 'COL',0,10);
insert into ot_shop_rec values ( '1018001-1002', 'COL',0,10);
insert into ot_shop_rec values ( '1018001-1001', 'GRID',0,10);
insert into ot_Shop_rec values ('1018001-1003','COL',0,10);

I WANT THE OUTPUT IN REPORT LIKE

ITEM RANGE DESC QTY REV

1018001-1001 - 1018001-1003 COL 30 0
1018001-1001 GRID 10 0

View 5 Replies View Related

SQL & PL/SQL :: How To View Rows In Table Format Using Select Query

May 28, 2012

I need to view the rows of the result of a select query in table format in GUI application.I have used XMLELEMENT to somewhat match the scenario to display as ','(comma) separate values as b belwo

SELECT RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
',')
AS empid,
RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),

[code]...

But the case is I need to display the value in table format Horizontally as below

EMPIDemployee nameDEPID
778
278CLARK10
397MILLER
934KING

[code]...

View 14 Replies View Related

Reports & Discoverer :: Display Data Vertically (landscape Format)?

Apr 19, 2013

I need the data in this below format.

I added the image...

View 2 Replies View Related

Fetch Records In Single Select Query And Display

Mar 15, 2011

I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.

Name id dept_no
Ram 101 10
20
30
Ani 201 10
20

View 1 Replies View Related

SQL & PL/SQL :: Split A String As Per Required Format

Dec 23, 2010

Split a column with values like 1-2-21-3 into 001-002-21-003 (ie format required is 000-000-00-000) using sql.

View 3 Replies View Related

SQL & PL/SQL :: Query To Get Data In Desired Format?

May 29, 2012

CREATE TABLE DATA1
(
ID NUMBER(6),
DAT_ID NUMBER(6),
RNK NUMBER(2)
);
Insert into DATA1
(ID, DAT_ID, RNK)

[code]....

now after having this data from "data1" table , we need to get the row from "data2" table.in "data2" table there are total 6 combination on basis of "POS,ORDER" [ there are only 2 "ORDER" i.e. 'F' and 'S' , where as POS value can be changed, BUT THE "POS,ORDER" COMBINATION WILL HAVE ONLY 6 UNIQUE COMBINATION. ] so, for "POS and DAT_ID" combination we need to get the lowest rank data first, if that is not present then get the other rank given in "DATA1" table and so on and if no rank is present then select the NULL row row data from "DATA2" table for ex: in DATA1 table for count(*)>1 and id=1, we have data as

--------------
IDDAT_IDRNK

11231
11242
11253
--------------

so, in "DATA2" table, first we will see for "POS and ORDR" combination which DAT_ID is present, i.e. in case od POS=11 and ordr=F, we will select

111231FD1

as it is having lowest rank in "DATA1" table and it is present in "DATA2" table,

for POS=12 and ordr=F, we will select

121242FD1

as we don't have "123 and 1" in "DATA2" table so we will select the next rank given in "DATA1" ( i.e. 124 and 2 ), similarly , for POs=31 and ordr=S, we will select, as this is the next available rank and DAT_ID present in "DATA2" table

1D131S1253

and if there is no rank present from "DATA1" table in "DATA2" table then we will select the NULL row, i.e. for POS=21 and ordr=F, we need to select :

1D121F

"there will be 6 row for each id"

the output we want is :
-----------------------------------------
IDNAMEPOSORDER DAT_IDRNK
1D111F1231
1D112F1242
1D121F
1D12321S1231
1D2322S1242

[code]....

View 4 Replies View Related

PL/SQL :: Checking Correct Data Format Using Sql Query?

Aug 30, 2013

1) I got column date of joining which accepts date in below format DD-MON-YYYYDD-MON-YYMON-DD-YYYYMON-DD-YYMonth DD,YYYY Question:-

how do i check whether all dates in Date of joining column are in above format or not using sql query? 2) I got one more date column which accepts date in below formatMMDDYYYYYYYYMMDDMM/DD/YYYYMM/DD/YYYYYY/DD/MM Question:-

how do i check whether all dates in date column are in above format or not using sql query?

View 26 Replies View Related

SQL & PL/SQL :: PLS-00402 - Alias Required In SELECT List Of Cursor To Avoid Duplicate Column

Oct 25, 2013

I am getting [Error] PLS-00402 (182: 1): PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names error in my SP.I have created alias for each column and still i am getting the error.

for my_rec_lot in
(SELECT LLP.BOOK_VALUE LLP_BOOK_VALUE,LLP.COMMISSION LLP_COMMISSION,LLP.CURRENCY LLP_CURRENCY,LLP.EXCHANGE_RATE LLP_EXCHANGE_RATE,LLP.EXPENSES LLP_EXPENSES,

[Code].....

View 6 Replies View Related

PL/SQL Hierarchical Query - Select Data In Specific Way

Apr 19, 2013

I have a table that has hierarchical data within it. I need to select this data in a specific way, showing the hierarchy.

E.g. Data in table (Key is unique)

Lvl KeyParKey HasChild
1k101
1k200
1k301
1k401
2k34k10
2k22k11
2k24k10
2k13k30
2k52k30
2k35k30
2k13k30
2k11k40
3k56k220
3k109k221
3k67k220
4k61 k1090
Etc etc....

That�s generally the format the values would appear in the table if I just did a standard select. I want it displayed in a more hierarchical Parent � child way.

The format I need to get out is as follows:
Lvl KeyParKey hasCh
1k101
2k34k10
2k22k11
3k56k220
3k109k221
4k61 k1090
3k67k220
2k24k10
1k200
1k301
2k13k30
2k52k30
2k35k30
2k13k30
1k401
2k11k40

View 1 Replies View Related

SQL & PL/SQL :: Select Statement - Inner Query Returns Data

Aug 23, 2012

I am new to oracle. I am creating a view, where in the inner query returns below kind data.

column1 column2
-------------------
a y
a y
b y
b n
c y
d n

I want to extract all column1 values which always has "y" in column2.

In this case, output has to be

output
------
a
c

View 10 Replies View Related

PL/SQL :: Hierarchical Query To Select Data From One Table?

Sep 7, 2012

Here is my case,

create table t (id number,row_id number primary key ,value number);ID   row_id value
1     1     10
1     2     11
1     3     1
2     4     11
3     5     11
3     6     12
4     7     12
4     8     12
4     9     13
4     10     11
4     11     10Requirement is

1) To get all the ID that have the value 10

2) The row_ids for the ID I got in I

Can I do this with Hierarchical query. If not which one of below will be fast?

select * from t where id  in (select id from t where value=10);

or

select T2.* from t T1, t T2 where T1.ID=t2.id and T1.VALUE=10;

I have billion of rows so I am looking for either Hierarchical query to solve it or some other way . All the three column the index.

View 5 Replies View Related

SQL & PL/SQL :: Select Query Like Operator Getting Wrong Data

Dec 25, 2012

I am using the following query with like 'T_%', i am getting 80 rows out of which the first table_name doesn't even have a beginning part 'T_%'.

the first table name has not started with 'T_', why is it appearing.

*********************************************************************
SELECT 'Truncate table epic500.'||table_name
FROM user_tables where table_name like 'T_%' order by table_name;
*********************************************************************
output:
Truncate table epic500.TEMP_ENC_DEL
Truncate table epic500.T_ACCOMMODATION_CODE

View 4 Replies View Related

Query Return Results That Contain Duplicates - Only First Or Last Required

May 27, 2008

I have this query that returns results that contain duplicates(somewhat). I only want either the FIRST or LAST (either one is fine). Here is the query:

select unique PLLA.attribute4, PLA.item_description from po_lines_all PLA, po_line_locations_all PLLA
where PLLA.po_line_id = PLA.po_line_id
and PLLA.attribute4 is not null

So my output is something like this:

RCE12 This is an item for AUL1
RCE13 This is an item for PWEILL
RCE14 This is an item for AUL1

I just want either the RCE12 or RCE14 record and not both since they both have the same description.

View 2 Replies View Related

SQL & PL/SQL :: Update Display Name Based On Format?

May 3, 2013

I have a table which has columns like First_Name,Last_Name and Display_Name. Now the every entry in the table have first name and last name populated .

I would like to populate the display name based on the format Display Name = First Name+ " " + Last Name.

For eg. If the first name John and last name is Doe , then the display name should be John Doe.write such a queries which dynamically picks up all the rows and populate the display name.

View 12 Replies View Related

SQL & PL/SQL :: Create Select Query That Will Get Data From 18 Months Ago Based On Sysdate?

Sep 9, 2013

I have a table with columns job_id, jan, feb, mar ... , and year

I need to create a select query that will get the data from 18 months ago based on sysdate.

So something like:

Select to_char(add_months(sysdate, -18),'MON') from table1 where job_id = 56947 and year = to_char(add_months(sysdate, -18),'YYYY');

However I need the result of to_char(add_months(sysdate, -18),'MON') to actually act as a column name, not a string result.

View 23 Replies View Related

Wrong Number Format Display Using DBLink

Jan 7, 2013

I have an issue trying to execute some queries using a dblink. When i run any query with numeric fields only display 4 digit and int the source database the fields have 5 digit. The dblink work between ans MSSQL database to an Oracle Database

Example:
MSSQL
select cardnumber from card
cardnumber
19121
19122

Oracle (with dblink)
select cardnumber from card@dblink1
cardnumber
1912
1912

View 1 Replies View Related

PL/SQL :: Date Format - Display Fraction Of Seconds

Oct 8, 2012

select to_char(SYSDATE,'MMDDYYYYHHMISS') from dual;

Ouput:
10082012010338

It is giving me correct output, but i want to display even the Fraction of seconds, how can i?

I have tried this..but not working..

select to_char(SYSDATE,'MMDDYYYYHHMISSFF') from dual;

Output: Error.

View 4 Replies View Related

SQL & PL/SQL :: Display Time Format In Oracle By Create View

Nov 16, 2011

I am not able to find any information regarding inserting data with timestamp format without putting the date.

This is what i want to do:

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH24:MI TZR';

I create the column data type as timestamp with time zone. This is value i have inserted:

Insert Into TEST Values('05-OCT-2013 01:00 +08:00','05-OCT-2013 23:00 +08:00','SCIENCE);

And now i wish to display it in 'HH24:MI TZR' only after i deduct both of the time. This is the view i have created:

Create View TESTRECRDS As Select (STARTTIME- ENDTIME)*24 As DURATION, Science;

But when i select it display as weird timing because it would default in 'DD-MON-RR HH24:MI TZR' format.

BUT IF i create the view and display as 'HH24:MI TZR', it would gives me error:

Create View TESTRECRDS As Select To_Char(STARTTIME- ENDTIME, 'HH24:MI TZR')*24 As DURATION, Science;

Error would be invalid number. How can i display only 'HH24:MI TZR' after subtracting?

View 3 Replies View Related

Reports & Discoverer :: Change Format Of Figure Display On Bar Graph?

Aug 18, 2009

how i can change format of figure display on bar graph(data labels) in oracle 10g graphical reports .i want to change format fom 99.99 to 9999 .

View 1 Replies View Related

PL/SQL :: Delete Data Not Required Retaining Rows

Mar 18, 2013

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 .

View 14 Replies View Related

Format Of Select Statement

Jun 15, 2007

decode (a.cancel_time,'',sum ((to_date('23:59','hh24:mi') - a.alarm_time)*24*60), sum((a.cancel_time - a.alarm_time)*24*60)) Duration,

After executing this statement. I get negative values if cancel time is null

I want to subtract alarm_time by 24 hours, if cancel time is null. If not null then cancel-time - alarm_time

View 1 Replies View Related

Data Guard :: How Much Bandwidth Required For Active Setup

May 25, 2013

How much bandwidth required to setup Active data Guard? How to measure bandwidth requirement from primary site? Any ML or doc would be great.

View 1 Replies View Related

Time Format - Way To Change My Select To Creation

Jul 18, 2012

when I select I have to using AND creation_dt>='17-JUL-12' is there a way to change my select to creation_dt='17-JUL-12' ?
on meantime my local format is 18-JUL-12(18-JUL-12) is thee anything I can do?

View 5 Replies View Related

Display String Using Select Statement In Oracle 10g

Jul 27, 2010

i want display a string like this using a select statement in oracle 10g.i have tried but not yet done.

example:
-----------
from 'ABCDEFGH' to 'ACEG'
removing 'BDFH' from the source string 'ABCDEFGH'

i giving here the example you can take any valid string i want the result like the above example and also in a dynamic manner means we can give string to a select statement in run time.can it is possible in a select statement only.

View 14 Replies View Related

Windows :: Select Installation Method Does Not Display?

Feb 11, 2013

I have installed oracle 10g in D drive .

The instalation says it is succeessful, without asking the database name or the password .

I have followed the steps given in the attached file .

However it is not asking the databse name or password ( no configuration set up is appearing for the user to interact with the system ) .

So, after installation I can not give the username and password .

The tnsnames.ora file is given below

# tnsnames.ora Network Configuration File: D:DevSuiteHome_1
etworkadmin nsnames.ora
# Generated by Oracle configuration tools.

[Code]....

The files from where I installed is ds_windowx86 disk1 and disk 2.

View 26 Replies View Related







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