SQL & PL/SQL :: UNION ALL Output In Multiple Columns?

Nov 26, 2012

Following is my table structure:

14:54:17 PYMTPRODIAT@UCS43 > select * from DGTEST;

ID AMOUNT DI
---------- ---------- --
1 50 D
2 50 D
3 20 D
4 60 C
2 60 C
3 20 C

now, I want the output in the following format.what could be the easiest way.. I need the output in one query,,

ID D_Net_Amount C_Net_Amount
--- ------------- --------------
1 50
2 50 60
3 20 20
4 60

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Formatting Output Into Multiple Columns?

Apr 16, 2013

as part of an exercise we are to demonstrate a FOR LOOP from 1 - 100.

We must show the output as evidence of doing the work.

Is it possible to display the results in two columns, instead of all in one row? Because 100 rows will not fit it one screenshot!

BEGIN
FOR i in 1..100
LOOP
DBMS_OUTPUT.put_line(i);
END LOOP;
END;
/

View 5 Replies View Related

SQL & PL/SQL :: How To Print The Multiple Output Statements In Single Output

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

SQL & PL/SQL :: Suppress Dummy Columns From UNION Clause

Mar 18, 2011

I want to Suppress the Dummy columns from the UNION Clause of Query.

SELECT
'HEADER' AS record_type
,null C2
,null C3
,null C4
,null C5
FROM dual
UNION ALL
SELECT empid
[code]....

Now I need to remove the all the Dummy fields associated to "HEADER" and "TRAILER" records.

View 2 Replies View Related

Multiple Tables With (UNION All) Clause

Aug 9, 2012

I have a view that contain multiple tables with ( UNION all ) clause , is there any way that if i query from this view I can explicitly specify the table that i need the data from ?

Let say i have view that contain salaries of 2011

Create view sals_2011 as select * from sals_jan2011 union all sals_feb2011 ..... union all select * from sals_dec2011.

if i issued select * from sals_2011 where emp_id >500 and date < 01-feb-2011 the explain plan show me that full tables and indexes are in processing, while i know that i need only to scan sal_jan2011, and of course it is taking much longer time than selecting from the original table only.

I am using oracle RAC 11g R2

View 5 Replies View Related

Reports & Discoverer :: SQL Union All - Query Block Has Incorrect Number Of Result Columns

Feb 1, 2012

I have the following Union All query. It throws the following error in SQL plus

ERROR at line 27: ORA-01789: query block has incorrect number of result columns

After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like

(Select distinct c.contact_code

Following is the SQL query

Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......

View 1 Replies View Related

PL/SQL :: Avoid Multiple UNION Statements And Get Static Values In Single SQL?

Dec 31, 2012

Version oracle 11g

Is there a way to avoid multiple UNION statements and get statitc values in single SQL?

select 'Week1' as term from dual
UNION
select 'Week2' as term from dual
UNION
....
....Some functionality like selecting from dynamic table column-rows.... like .. "select term from table(term(rowvalues('Week1', 'Week2'...'Week5')) from dual" or something like that

View 4 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

Oct 17, 2012

How to merge multiple rows into single row (but multiple columns) efficiently.

For example

IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100

where different attributes mean different thing for each Information_type. For example for Information_Type=Location

Attribute_1 means Country
Attribute_2 means State and so on.

For example for Information_Type=Stats

Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.

I want to create a view that shows like below:

IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100

View 1 Replies View Related

SQL & PL/SQL :: Multiple Rows On A Table To Multiple Columns On One Row

Nov 26, 2010

I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below

TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan

My desired result is to be able to show the output as follows

Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan

View 6 Replies View Related

SQL & PL/SQL :: Space Between Output Columns

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

SQL & PL/SQL :: Sum And Create New Columns For Output?

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

PL/SQL :: Concat Multiple Output Lines?

Nov 21, 2012

I'm struggling with a problem for quite a while now and can't find any solution online so far ...

OK I got three different Tables

cust_tbl:
customer:id
nuxx :1
naxx :2
baxx :3
suxx :4

[code]...

As you see, I only need the Customer one time followed by part, amount and address - separated hyphen - comma, next part, amount, address and so on ...I've tried the PIVOT way and STRAGG (wm_concat) but all failed so far.The main problem is that when one customer fits more than one condition, ora throws: ORA-01427: single-row subquery returns more than one row

View 9 Replies View Related

SQL & PL/SQL :: Getting Output From Multiple Rows Return Only One Row In Oracle?

Mar 27, 2013

EMP table

create table emp1 (empno number,deptname varchar2(30),deptno number,sal number);
insert into emp1 values (1,'Bank',10,1000);
insert into emp1 values (1,'Finance',20,400);
insert into emp1 values (2,'Finance',20,4000);
insert into emp1 values (3,'Account',30,3000);
commit;
select * from emp1;

actual output :

empno deptname deptno sal
1 Bank 10 1000
1 Finane 20 400
2 Finane 20 4000
3 Account 30 3000

Expected output :

empno deptname deptno sal
1 Bank 10 1000
2 Finane 20 4000
3 Account 30 3000

I am looking a output like above one. if any empno belongs to deptname Bank then give priority to that values else go to other dept like Finance but empno 1 should return only one row.i.e Bank dept only.

I have to return only one row based on dept values for one empno. how to do in sql or plsql?

View 11 Replies View Related

SQL & PL/SQL :: How To Update Multiple Columns

Dec 12, 2011

how to over come this error, because i need to update only 3 columns in the table and iam getting error when iam updating like this

update country1 set cname='japan','usa'
where cid=100,101

ERROR:ORA-01747,INVALID USER.TABLE.COLUMN,TABLE.COLUMN,OR COLUMN
SPECIFICATION

View 6 Replies View Related

SQL & PL/SQL :: Sort On Multiple Columns

Jun 16, 2010

how does sorting on multiple columns work

suppose my query is

select * from person order by first_name desc
and sys_person_id asc

this query works , but is this write way to sort on multiple column ?

View 12 Replies View Related

SQL & PL/SQL :: Order By Multiple Columns

Oct 9, 2013

I have a query to pull the first contact of students.

The table has all contacts like parent/guardian, friends family, emergency contact etc.

I would like to the first primary contact in this order,
1. initial contact, 2. same as student address and also have to be parents,
3. live with and also a parent, 4. parents 5. friends.

I don't know how to pull 2 and 3 . because it looks like it needs to concatenate the columns.

here is my initial query

Select Min(U2.Id) Keep (Dense_Rank First Order By U2.Initial_Contact Desc, U2.Same_As_Students_Address Desc,u2.lives_with DESC,U2.Guardian Desc)
From Contacts

how to achieve 2 and 3?

The table script is attached. All the above columns are 1 or 0.

View 3 Replies View Related

SQL & PL/SQL :: Multiple Queries Into Different Columns?

Jan 31, 2013

I am trying to validate a monthly report so was trying to write queries to get different criteria into one table. So my first query returns all the product,second query returns all the enrolled customers, 3rd query returns all the cancelled customers and 4th query returns all the newly enrolled for a month. Is there a way I can pass the first query results into 1st column, 2 query results into 2nd column, 3 query results into 3rd column and so on.

I tired writing the SQL several different ways and have spent a day on it and still cannot figure it out. I am using SQL Developer.

View 9 Replies View Related

SQL & PL/SQL :: Top Row Based On Multiple Columns?

Dec 1, 2011

I have a table with Column A, B, C. I want to write a query to retrieve the top row of A, B combination. i.e, for every unique value of A,B combination I want the row having highest value for C. I tried using rank() function but am not able to get the top row with combination of A,B.

View 8 Replies View Related

PL/SQL :: Multiple Columns In IN Clause?

Jun 19, 2013

select * from nrc_trans_descr where type_id_nrc=60013   -- it has 18 columns and i have hard coded 60013 for simplification here.60013 is derived from 3 other table Output is ( it can have many rows too.typically for each type_id_nrc there is one row ).
 
TYPE_ID_NRC  TRIGGER_STATUS  INSTALLMENT_TYPE_ID_NRC
---------------------------------------------------------------------
      60013              0                 61013                  
i have to pass TYPE_ID_NRC  and  INSTALLMENT_TYPE_ID_NRC to restriction_id column in a different table. currently i am doing like this 
select * FROM DISCOUNT_RESTRICTIONS WHERE discount_id in (12085,12086)
and (restricted_id in (  select type_id_nrc from nrc_trans_descr where type_id_nrc=60013)
or restricted_id in (  select installment_type_id_nrc from nrc_trans_descr where type_id_nrc=60013));

am using ORACLE 10GR2(solution for 11gr2 is welcome too) 

View 16 Replies View Related

SQL & PL/SQL :: Query With Multiple Columns

Dec 17, 2012

Table-Name

ID Status description Tracking ID
1 Strat Frog 1
2 Start Dog 2
3 Process Frog 1
4 Completed Dog 2
5 Start Rabbit 3
6 Error Frog 1
7 Stop Rabbit 3
8 Start Elephant 4
9 process Elephant 4
10 Start Human 5
11 Stop Human 5
12 Start Butterfly 6
13 completed Butterfly 6
14 start lion 7
15 error lion 8
16 complted lion 8
17 start tiger 9
18 error tiger 9

select * from Table-Name where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss')
And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')And not description in (Select * from Table-Name where Status like ('%Complete%' or Status like '%stop%') and description in (Select description from Table-Name where Status Like '%start%'));

Result should be " Frog and Elephant and tiger"

Start of every record(descrpition --status is Start)
End of every record ( status is stop or done or completed)
status process is in btwn (their will be mulitple records with name s//y to process...ie. process 1 ...process 2...process 3 )

Note:

tracking IDs may change up on error

View 2 Replies View Related

PL/SQL :: Multiple Columns Using Group By

May 20, 2013

Can i select multiple columns while using group by single column?

View 5 Replies View Related

SELECT DISTINCT On Multiple Columns

Sep 17, 2010

I've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:

select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob
from patients join schedule using (key_patien)
join adtmirro using (key_patien)
where appt_state = 'ON HOLD'

Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.

View 3 Replies View Related

Convert Rows To Multiple Columns

Aug 30, 2004

I have a table called N1

N1_no Srvarea_type_cd
1 P
1 P
2 C
2 C
2 C
3 I

Another table N2

N1_no srvarea_txt
1 ABCD
2 DEFG
3 XYZA

Can i get a query so that the data can be displayed in the following way ..

P C I
ABCD DEFG XYZA

View 3 Replies View Related

Multiple Columns Right To Left Orientation?

Sep 15, 2009

i have downloaded ireports latest version (3.6.0) for working with jasper reports (3.5.2) and creating reports;How can I change the orientation of a 4 columns report in order to generate the columns starting from the right side.I need to generate a multiple columns report in arabic and it should be done from right to left.

View 2 Replies View Related

Sub Partition By List On Multiple Columns

May 16, 2011

Can i partition by list on multiple columns? i am trying the following code, and it is returning an error. Is there a way round this?

CREATE TABLE
(...)
PARTITION BY LIST (col1)
SUBPARTITION BY LIST (col2)
SUBPARTITION TEMPLATE (
SUBPARTITION DETAIL VALUES ('DETAIL')
SUBPARTITION ROLLUP VALUES ('ROLLUP'))
(
PARTITION RT VALUES('RT')
)

View 1 Replies View Related

SQL & PL/SQL :: Using Cursor To Update Multiple Columns

Oct 22, 2011

I need to write a script which copies 4 col data from one table to another table. there are three tables

cwat_curr_mst and cwat_assigned_customer and cwat_assignment_mst.
Cwat curr mst has PK curr_id and cwat_assigned_customer has PK assignment_id.
Also cwat_assigned_customer has customer_id.
In cwat_assignment_mst has Curr_id and Assignment_ID.

cwat_curr_mst and cwat_assigned_customer tables has 4 cols in common
they are
ASRT_SNM_NO, SNM_NO, FLORIDA_NO, CBRN_NO.

So from curr_mst all these 4 cols data needs to come/copy into cwat_assigned_customer.

View 20 Replies View Related

SQL & PL/SQL :: Groups With Multiple Columns And Links

Nov 29, 2010

the thread title was a bit confusing, couldn't come up with anything short to describe the question. What I am looking for is a query which will put records into groups based on matching values in one of two columns. So if two records have a matching value in column 1 or column 2 they are in the same group. See the example bellow and expected output for a "better" explanation:

--setup
CREATE TABLE foo
(foo_id NUMBER NOT NULL PRIMARY KEY,
record_number NUMBER,
record_value VARCHAR2(1));

[Code]...

--expected output

group# foo_id record_number record_value
1 1 1 A
1 2 1 B
1 3 2 B
1 4 2 C
2 5 3 D
3 6 4 E
3 7 5 E

My initial thought is that is feels a little bit like the sequential seat problem but not quite close enough. I know it could be done iteratively with PL/SQL but I am thinking there must be a way to do it in SQL I am not seeing yet.

View 6 Replies View Related

SQL & PL/SQL :: Split Multiple Columns Into Rows

May 2, 2012

I have data like :

ID NAME1 NAME2 NAME3
JJ AD MED
VI TIBO PH TIBO

I want output like

ID NAME
JJ AD
JJ MED
VI TIBO
VI PH
VI TIBO

View 4 Replies View Related

PL/SQL :: Multiple Indexes Using Same Columns On One Table?

Oct 4, 2012

I have a table like MyTab(a int, b int), and I am required to create a primary key index and a non-unique index on this table using columns (a,b) in a specific table space.

The back end database is Oracle 10g.

Here's what I have tried so far, needless to say, unsuccessfully.

Alter Table MyTab
Add Constraint c_1 primary key (a, b)
Using Index (Create index mytab_idx on MyTab(a, b))
Using index tablespace results_index

So my question are:

1. is this is possible? if so, what is the correct syntax.
2. assuming it is possible, using this sort of construct before? it appears to be conflicting and inconsistent to me.

View 4 Replies View Related

Group By Multiple Columns / Count And Then Find A Max

May 24, 2008

I have three tables,let's say

table stores
sid | store_name
1 | one
2 | two
3 | three

table products
pid | sid | p_name
1 | 2 | pone
2 | 2 | ptwo
3 | 3 | pthree

table sales
said | sid | pid
1 | 2 | 1
2 | 3 | 1
3 | 2 | 2
4 | 1 | 3
5 | 2 | 2
6 | 3 | 2
7 | 3 | 2

and i want display the product that sells best in every store. I try to group by multiple columns counting how many times each product was sold in every store, but don't know how to select the one which was best sold (maximal number of times)

View 5 Replies View Related







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