SQL & PL/SQL :: Display In Two Different Columns

Feb 1, 2013

Below is returning rows in single column.I need to display in two different columns

SELECT * FROM TABLE(PLUSER.SPLIT('a,b,c'))
union all
SELECT * FROM TABLE(PLUSER.SPLIT('1,2,3'))

this returning result like
a
b
c
1
2
3

but need abc in one column and 1,2 3 in one column
a 1
b 2
c 3

i was tried like

select * from TABLE(PLUSER.SPLIT('1,2,3')),(select * from TABLE(PLUSER.SPLIT('a,b,c')));

but it's giving cartesion result like below
1a
1b
1c
2a
2b
2c
3a
3b
3c

View 8 Replies


ADVERTISEMENT

Reports & Discoverer :: Create A Report By Using One Field / Text As Columns Name In Layout But Display All Columns

Jun 16, 2010

I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.

View 4 Replies View Related

PL/SQL :: Display All Columns In Different Rows?

Oct 9, 2013

i want to display all the columns of emp table in rows.O/P should be like below:---------------------------------------------Columns------------empnoenamesaldeptnomgr

View 7 Replies View Related

Application Express :: Interactive Report - Moving Selected Columns To Do Not Display Section

Dec 19, 2012

We have built an interactive report based on dynamic query. Normally, all the selected columns are shown in "Display" Section of the interactive report. We have a requirement to move some of the columns to the "Do not Display" when the search is done. Is it possible to do it programmatically ?

View 1 Replies View Related

Performance Tuning :: Index 15 Columns When Cannot Predict Columns Will Be Used In Where Clause?

Apr 4, 2011

I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.

Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.

Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.

So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.

Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.

What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.

I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.

What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.

So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.

View 12 Replies View Related

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

Jan 21, 2011

I have a two question.

Question 1:How to select all columns from table except those columns which i type in query

Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data

View 5 Replies View Related

SQL & PL/SQL :: Function In Oracle To Select Not-null Columns At Beginning And Null Columns At End?

Jul 12, 2012

I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :

Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

View 8 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

SQL & PL/SQL :: Transpose Rows-to-columns And Columns-to-rows

Oct 6, 2010

I need to transpose the following table columns to rows and rows to columns...Im not quite sure how to acheive this...I have the following table with fixed number of columns and dynamic number of rows based on date filter in query

MONTH_YEAR RMS RMS_OCC TTL_RMS
---------------------------------------
SEPTEMBER 200917790017790
OCTOBER 2009183831278818347
NOVEMBER 2009177901460517762

and I need to display this as

COL1 SEPTEMBER 2009 OCTOBER 2009 NOVEMBER 2009
--------------------------------------------------------------
RMS 17790 18383 17790
RMS_OCC 0 12788 14605
TTL_RMS 17790 18347 17762

View 3 Replies View Related

PL/SQL :: Display One Row Only

Apr 16, 2013

This query

SELECT
SCRATTR_ATTR_CODE
from scrattr_TEST,scbcrse_TEST
where
SUBSTR(scbcrse_subj_code,1,3)  = SUBSTR(scrattr_subj_code,1,3)
and SUBSTR(scbcrse_crse_numb,1,4)  = SUBSTR(scrattr_crse_numb,1,4)Returns this

SCRATTR_ATTR_CODE
A
INS
MCSR

How I can make to return someting like A INS MCSR in one row there is a row for every code

hERE is some code to create the tables and insert the data

CREATE TABLE SCRATTR_test
(
  SCRATTR_SUBJ_CODE      VARCHAR2(4 CHAR)       NOT NULL,
  SCRATTR_CRSE_NUMB      VARCHAR2(5 CHAR)       NOT NULL,
  SCRATTR_EFF_TERM       VARCHAR2(6 CHAR)       NOT NULL,
  SCRATTR_ATTR_CODE      VARCHAR2(4 CHAR)
 
[Code]....

View 1 Replies View Related

How To Display The Second MAX Value In A Table

Jul 31, 2007

a sample query to display second MAX value from the oracle database

View 1 Replies View Related

SQL & PL/SQL :: Display Zero If Negative Value

Jan 9, 2012

I am doing a Gross Profit percentage. I want to display zero if the GP% is have negative value.

for example

If GP% shows -75 I want to display as 0.

trunc((sum(total)-(sum(DECODE(po_pur,'',0,PO_PUR))+sum(DECODE(pl_pur,'',0,PL_PUR))+SUM(parts))) / SUM(DECODE(TOTAL,0,1,TOTAL))*100,2) "GP%"

View 2 Replies View Related

SQL & PL/SQL :: Can Display Value From A Query Into New Row

Apr 13, 2013

I have a requirement where I want to display amount what I have billed to my client and then how I received my amounts from that Bill. For eg.lets say there is bill no 001 amounting 10000 and after some days I received a part payment cheque/cash from client worth 2000 then he gave second part 2000 and then finally I received 2000. Still 4000 is remaining to be received.So in tabular format I want to display data which will have 3 records of 2000 and one record of balance 4000.

In attached file i have given few examples, where highlighted area is what i want to derive. There are formula's in Collection date, delay by and Int calc. Basically highlighted lines are the ones which should be shown in query result. And data shown above that is what i have right now.

View 1 Replies View Related

PL/SQL :: Add A Column To Display?

Mar 21, 2013

I have a procedure in this i want add a column to display my procedure is

set serveroutput on;
create or replace PROCEDURE IdentifySparesInDSIGNALTables
AS
V_SQL VARCHAR2(1024);
cnt1 number;

[code]...

i wnat to add dlstation in this procedure to display output...

View 1 Replies View Related

SQL & PL/SQL :: Data Display

Jul 26, 2013

I have a requirement where i need to display like

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

is this possible.????

View 14 Replies View Related

SQL & PL/SQL :: How To Display 18-MAR-13 As 20130318

Jun 3, 2013

I want to display '18-MAR-13' as 20130318.

View 9 Replies View Related

SQL & PL/SQL :: How To Display Result

Aug 7, 2012

I need to return the stored result in a record by ref cursor. In the below code I am displaying the result through DBMS_OUTPUT.PUT_LINE but i need to do it through ref cursor to display the result.

declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
[code].....

View 1 Replies View Related

How To Display Row Value Of Each Class As Column Value

May 27, 2008

How to display row value of each class as a column value

IDNAMECLASSHOURSSCHOOLNAME
1AllenMaths12school1
2SamMaths12school1
4PamMaths10school1
3SamComp10school1
9SmithComp15school1
7SmithPhy20school1
5PamPhy12school1

[code]....

I wanted output like below for School1

NamemathsCompPhyAccountsBusiness
Allen120000
Sam1210000
Pam10012150
Smith01520120
Adams000030

I wanted output like below for School2

NameCompAccountsBusiness
Anthony2000
Chris102020

I wanted output like below for School3

NamemathsAccounts
Angelo2015
Robert1515

how to write a single query, Also i may add /delete school/class anytime. Query should support mesans everything dynamically should get the data.Even stored procedure will work for me.

View 3 Replies View Related

Display 10 Most Costly Missions

Jan 22, 2009

i have to display the 10 most costly missions. but if the 11th record has the same cost then it needs to display 11 records instead of 10. how can i do this?

View 5 Replies View Related

Display Different Number Of Rows?

Jan 23, 2009

i have a table displaying location, cost, inner_num and rownum ordered by the descending order of the cost.

the question is: display locations of the highest 10 costs. if the cost of 11th row is the same as 11th, then display 11 rows.

View 3 Replies View Related

How To Display Hierarchy List

Oct 1, 2009

query in order to display a hierarchy list. Example:

Database has three tables

Heading --> id, status, order, title
Sub-heading --> id, status, order, title, heading_id
Item --> id, status, order, title, heading_id, sub-heading_id, actual_content

order = the sequence in which they are to be displayed a sub-heading's heading_id refers to the parent heading, likewise an item's sub-heading_id refers to the parent sub-heading.

I assumed there was no need to track child content.

Now, the output format should be something like this, where the sub-headings are not repeated, nor are the items. Sort of like chapters in a book, things in a catalogue, or even topics in a forum.

Heading 1
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Heading 2
Sub-heading 1
Item 1
Item 2
Item 3
Heading 3
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Sub-heading 3
Item 1
Item 2
Item 3

Where I'm getting hung up is how to query the data so it's quick, efficient, clean and easy to read and maintain. I've only gotten to listing the sub-headings and already the query looks convoluted. So, how should I approach this? Should it be done all in one query, and always checking against the results to know when to print a new heading or sub heading. Or should I run multiple queries and save the results to a variable, and then run sub-queries off that? Is there a better way to do this, am I going about it the hard way or am I in the right direction and just haven't gone far enough with it yet?

View 11 Replies View Related

Display All Tables In DB (with Their Definition)?

Sep 19, 2007

I've tried using Select * from ALL_TABLES but this does not return me the column info for each table.

How do I get an output of all tables in a database along with column (e.g datatypes etc)?

I'm using TOAD v8.6.1.

View 3 Replies View Related

How To Display Time In Minutes

Oct 15, 2012

I have two columns which I need to add together then devide by 60 in order to display that time in minutes. the problem I am facing is that at times I get numbers above 60 and my client doesn't want to see numbers above 60.

i.e (col1 + col 2)/60 = 34.87

what I need to do is make sure that when the number reaches 60 it moves on to 35.

View 2 Replies View Related

Display Date And Time

Sep 28, 2011

I had a database at microsoft access but i had move it to the oracle database.

When i check the datatype at oracle, date column is at DATE datatype. but it only display date. like in oracle. it display:

 oracle_date.PNG ( 74.61K )
Number of downloads: 4

But when i link it to microsft access. like i will control the oracle from access. the result is at below:

 access_date.PNG ( 136.11K )
Number of downloads: 3

So, my problem is how to display the date and time fully in oracle?

View 1 Replies View Related

SQL & PL/SQL :: Display Some Value When There Is No Record In Table

Jul 8, 2010

I have a following table,

create table test ( col1 date,col2 number);

Data:

col1 Col2
----------
NULL NULL

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 ?

View 9 Replies View Related

SQL & PL/SQL :: Display A Value In Between Single Quotes?

Nov 18, 2011

how to write a select query to display a number column value in between single quotes. Ex:i have a emp table in that sal column is a number type.

Ex:
select sal from emp;
sal
----
1234
231
3456
3211
23445I want to display the same above result in b/w single quotes.

ex:
sal
----
'1234'
'231'
'3456'
'3211'
'23445'
for this i need to write a query,

View 5 Replies View Related

SQL & PL/SQL :: How To Display Result Horizontally

Feb 11, 2013

1.Requirement....

deptno ename
++++++++++++++++++++++++++++++
10 a,d,e,g
20 b,c,j
30 k,l,o

2.How to swap the column values ? ex. col2 into col1 and col1 into col2

3.What is the major Diff. b/w Right outer join & Left outer join ?

View 3 Replies View Related

SQL & PL/SQL :: Display Results Using CASE WHEN?

May 24, 2011

I am trying to create a query that displays the given error message if the result of my COUNT(*) is smaller than 1, but displays the result of my first query (data) if the total count is bigger than 1 (read: the query found data, so it needs to display the rows according to the search).

What do I need to do to display 'data' if 'data2' contains rows?

WITH data
AS (SELECT a.order_id, a.session_id, a.log_id, b.date_of_order, a.operation,
b.funct_prod_code, b.sts_status_code, b.ost_order_situation_code, c.order_situation_oms,

[Code]....

View 11 Replies View Related

Forms :: To Display Specified Image

Jan 14, 2012

I want to display a specified image if not form_success!

declare
image_path varchar2(80) := 'C:R_ORACLEPHOTOS';
photo_name varchar2(80);
begin
:system.message_level := '25';
[code].......

View 1 Replies View Related

Forms :: How To Display Photo

Jun 23, 2010

My form failed to display photo (data type long row).

scenario a)
If photo in black and white with
width : 472 pixels
height: 590 pixels
horizontal resolution : 300dpi
vertical resolution : 300dpi
bit depth : 8
size file : 28KB

scenario b)
If photo in color with
width : 124 pixels
height: 155 pixels
horizontal resolution : 75dpi
vertical resolution : 75dpi
bit depth : 24
size file : 14KB

Above 2 scenario:

My form 6 program can successful display the photo for scenario a , while for scenario b, my form 6 program failed to display the photo.

View 4 Replies View Related







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