Query To Display Records In Particular Order

Feb 8, 2007

I need to write a query to sort the records in a particular order,

Say if I group the records by Dept number
Dept no Name
10A
10G
10f

20B
20K

30I
30M
30R
30Y

I need to write a query that will make this records listed like

Dept No Name
10A
20B
30I

10G
20K
30M

10F
20null
30R

10null
20null
30Y

View 1 Replies


ADVERTISEMENT

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 :: Display All Values In Same Order As In IN List

Mar 29, 2010

I had a table main with 2 fields. please see the contents below.

seq name
a aaa
b bbb
c ccc
d ddd

1. My query is as below

select seq,name from main where seq in ('a','b','c','b','d','d') output of query is

a aaa
b bbb
c ccc
d ddd

but I need the output to be as shown below.

a aaa
b bbb
c ccc
b bbb
d ddd
d ddd

I need to display all the values in the same order as in the "IN" list.

View 18 Replies View Related

Display Customers Living In GA Or FL Who Recently Placed Order Totaling > $80

Jul 17, 2013

what I want my code to do is display the customers who live in either Georgia or Florida who recently placed an order totaling more than $80.

My code so far:

SELECT LastName, FirstName, SUM(Retail * Quantity) AS Total
From CUSTOMERS, ORDERS, ORDERITEMS, BOOKS
Where (State = 'FL' OR State = 'GA')
Group by customers.customer# Having SUM(Retail * Quantity) > 80.00;

How would I get this to work?

View 4 Replies View Related

SQL & PL/SQL :: Order Records Just Same As Zip Codes?

Jun 10, 2010

I am using oracle 10g.

I have a query which is used to get the contact details based on the zip code

select * from contacts where
primary_address_postalcode like '65084%' or
primary_address_postalcode like '65011%' or
primary_address_postalcode like '65034%' or
primary_address_postalcode like '65078%' or
primary_address_postalcode like '65050%' or
primary_address_postalcode like '65037%' or
primary_address_postalcode like '65329%' or
primary_address_postalcode like '65072%' or
primary_address_postalcode like '65081%' or
primary_address_postalcode like '65038%'

Result

userid name primary_address_postalcode
5 abc 65072
6 def 65038 -12343
1 xyz 65050
24 pqr 65011
32 test 65011-23455
54 itsme 65329

i want to order the records just same as the zip codes we are using in like (65084,65011,65034, 65078,65050, 65037, 65329,65072, 65081, 65038)

How can i get the result like below

expected result

userid name primary_address_postalcode
24 pqr 65011
32 test 65011-23455
1 xyz 65050
54 itsme 65329
5 abc 65072
6 def 65038-12343

View 15 Replies View Related

SQL & PL/SQL :: SELECT Records In The Order They Were Inserted

Apr 12, 2011

I would like to write a select statement on a table and I want the records to be retrieved in the order they were inserted.

View 17 Replies View Related

PL/SQL :: How To Display All Records In A Table

Oct 3, 2013

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.

View 3 Replies View Related

How To Display Unmatched Records In Two Tables

Feb 28, 2008

I want to display all records from table 1 (even the null values) that do not match records in Table 2. Below I am creating both tables and I am posting the result query I need.

------------------------------------
CREATE TABLE temp_table1
(
name VARCHAR2(12 BYTE),
last_name VARCHAR2(12 BYTE),
STATE VARCHAR2(2 BYTE),
BIRTH_DATE DATE
);

CREATE TABLE temp_table2
(
name VARCHAR2(12 BYTE),
last_name VARCHAR2(12 BYTE),
STATE VARCHAR2(2 BYTE),
BIRTH_DATE DATE
);

[code].....

The result query need to have 5 rows as shown below

NAME LAST_NAME STATE BIRTH_DATE
------------ ------------ ----- ---------------------
john smith MA 12/1/1979
null null AZ null
null null CT null
null null MA null
null null CT null

View 5 Replies View Related

Forms :: Display All Records Of DB In A Table?

Dec 29, 2012

i want to display all the records of my database in a table with scrollingexample if i have 3records in my DB so a table must have 3records too !!!this is my code :

DECLARE
Cursor cur IS
SELECT numb_ph AS phone,
name_emp AS name

[code]...

but the problem 's all records of my DB are display in one record (to view another record i use the scrolling ..

View 31 Replies View Related

SQL & PL/SQL :: How To Display Data Without Duplicate Records

Nov 16, 2011

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,

[Code]....

output
-----------------
compid compname policy_id policy_name

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

View 10 Replies View Related

PL/SQL :: Unable To Fetch And Display Records?

Aug 21, 2013

I am using Oracle 11g

The procedure is compiled without any errors.

The select statement retrieves records well when executed at SQL promt.

But the same records are not displayed in this procedure by fetch from cursor.

The last message in the procedure 'completed' is also displayed.

create or replace procedure disp_rec(vid IN varchar2, vfeat IN number)
is
cursor c1 is select gid, listagg(vindex, ',') within group (order by tid) idx from ridrecords where idarpt=vid and feattype=vfeat group by gid;
type vg is table of number;
type vi is table of varchar2(3500);

[Code]....

View 19 Replies View Related

Forms :: Before / While Changing - Display All Records?

Jun 22, 2011

I have created two tabs tab1 & tab2. after changed(not while changing/before changing)) into tab2 all records should be display.

I have written execute_query in when-tab-page-changed.but that is before/while changing.i want after entered in tab2 it should be display.

View 1 Replies View Related

SQL & PL/SQL :: Returning List Of Records That Do Not Have Associated Records From A Sub-query

Sep 6, 2010

I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:

The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.

Query for anyone interested:

Select distinct
Project_code,
project_name
from
niku.nbi_project_current_facts nbip,
niku.odf_ca_project cst,
niku.prtask t,
[code]........

I have a separate query which returns a list of support resources.

select res.full_name, res.unique_name , dep.description
from niku.srm_resources res,
niku.pac_mnt_resources pac,
niku.departments dep
where res.unique_name = pac.resource_code
and pac.departcode = dep.departcode
and res.is_active = 1
and description like 'IMS%'
and UPPER(dep.description) like '%SUP%'

What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.

(the res.unique_name field in the second query can be linked to the same in the first query)

Logically, the process would be:
1. Identify Support Project
2. Identify Resources allocated to the project team
3. Compare with List of Support Resources
4. If any Resources in that list do NOT appear on the project, then return project.

View 18 Replies View Related

SQL & PL/SQL :: Query Against EBS Order Lines And Headers

Sep 6, 2012

I want added one new column in the below report which will shows only the items purchased on Cash basis but when i am running the query the column not showing any data.

The query is :

Select DISTINCT ou.name||' Spare Parts' ORG, oh.order_number, trx.trx_number, trx.trx_date, p.party_name CUSTOMER, l.ordered_item,
/* ( Select Distinct it.description From MTL_SYSTEM_ITEMS_B it Where it.inventory_item_ID = l.inventory_item_id and it.organization_id = o.organization_id) Item_Desc, */
l.ordered_quantity, l.line_number,
l.inventory_item_id, l.unit_list_price , ----
a.account_number CST_NO ,
oh.Attribute1 MODEL , oh.Attribute2 SERL_NO, oh.attribute3 PAYM_MODE ,
u.user_name Prep_By, oh.CUST_PO_NUMBER PO_NUM,
[code]......

View 1 Replies View Related

Default In (order By) Select Query Different In 11g From 10g?

Nov 12, 2013

if when you are querying a table in 11g and you use the order by clause and there is more than one occurrences with the same values in the order by, if the 11g default is different than from 10g.

For instance. 

DECLARE MHBulk CURSOR FOR                          select invoice_nbr,               customer_nbr,                post_century,                post_yymmdd,     from CUSTOMERS     where customer_nbr = 1234     order by           post_century,           post_yymmdd; 

If you have more than one occurrence of the same customer_nbr, post_century, and post_yymmdd

it looks like the default order for how 11g retrieves the records is a bit different than the 10g default. 

View 9 Replies View Related

PL/SQL :: Limit Order By Rows Without Using Sub Query

Sep 4, 2013

Here is my query to fetch only 10  records from order by result set. 

select *from (select * from EOE_POC.PRODUCT_TEST_REPORT where PRODUCT_CODE='214d' order by CREATE_DATE desc ) where ROWNUM <= 10  

I am having problem binding it with java API . how to query this without using sub query ?

View 11 Replies View Related

Forms :: Display Records Of Two Tables In One Form

Mar 27, 2013

want to display records of two tables in in one form.I have 2 tables. tab1 and tab2

columns in tab1 are code, desc, part, onhand_qty AND
columns in tab2 are code, room_no, row_no, rack, shelf, qty, remarks.

Common column in both the tables is code.

i want to display all the columns of both the tables where tab1.code=tab2.code ,and tab1.onhand_qty is not equal to SUM of tab2.qty

View 1 Replies View Related

SQL & PL/SQL :: Display The Records Of Financial Year Wise?

Jul 20, 2011

i hav the following records i need to display the records of financial year wise (i.e april year-1 to mar year) here there are no records for months 9,10(september and october)

what i want is 0 to be displayed over there in the below table

s.no key id yyyymm ce1 ce2 ce3 t ihu pf mi pwd F
920110600000156431372010043424541500065457709539XXXXXXXXXXU
10201106000001565313720100534245415000009539XXXXXXXXXXU
11201106000001566313720100634245415000009539XXXXXXXXXXU
12201106000001567313720100738555415000009539XXXXXXXXXXU
13201106000001568313720100836445415000009539XXXXXXXXXXU
1420110600000157131372010110000009539XXXXXXXXXXU
15201106000001572313720101276881082200001888009539XXXXXXXXXXU
1620110600000157331372011010000009539XXXXXXXXXXU

View 2 Replies View Related

Performance Tuning :: Display All Records From TAB_ONE

Jul 8, 2010

My query formation is like below..

*Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi*

SELECT T1.COL1,T1.COL2,
T1.COL3,T2.COL2,
T3.COL1,T3.COL2,
T4.COL1,T4.COL3,
<CASE statements and calculations results - Some Amount1>,
<CASE statements and calculations results - Some Amount2>
[code]..........

First I need to display all the records from TAB_ONE which contains more than 10million records. If you see there are columns like AMOUNT_ONE,AMOUNT_TWO which has got some complex calculations and that is based on some other calculations and which in turn and goes on.... Like this I have some ten amount columns. Finally these records has to be inserted into a new table.

To get this I have written nested inline queries to calculate these AMOUNT columns but since considering the huge amount of records, it takes more than 8 hours for inserting this into a new table.

View 7 Replies View Related

SQL & PL/SQL :: Query Gets Slower By Adding Order By Clause

Apr 14, 2010

I have a query, running the query with order by and without orderby clasue casues big performance difference.

here without order by it takes 5 sec
.
SELECT
audit_number,
formatted_audit_number,
ag.sys_audit_id,
audit_begin_date,
audit_end_date,

[Code]....

here with order by it takes 2 min

SELECT
audit_number,
formatted_audit_number,
ag.sys_audit_id,
audit_begin_date,
audit_end_date,
auditee_name,
ein,

[Code]....

I have like 10,000 records.

View 16 Replies View Related

SQL & PL/SQL :: Write A Query In Order To Receive A New Table?

Apr 23, 2012

i need to write a query in order to receive a new table.

select table_name,col1, null col2
from
(SELECT b.table_name, b.col1 col1, a.CountPrimaryKeys
from
(SELECT user_constraints.table_name,
COUNT(user_cons_columns.column_name) CountPrimaryKeys

[code].......

View 21 Replies View Related

Forms :: Display All Records One By One As User Click On Next Record Button

Jul 13, 2010

I have created one tab canvased form

On first tab I am taking input (search criteria)
on that input i am trying to display all records one by one as user click on next record button....

for that i used when-tab-page-change trigger for first record display and it display correctly.but for further record what should i need to write on next_record command button so all record i can see one by one.

View 1 Replies View Related

Generate CASE WHEN Query With ORDER BY Involving Secondary Sort

Oct 31, 2012

Below Query I understand is improper wrt syntax. Need to modify query to fetch correct result for me.

select * from tableA order by
case when a is not null then
a DESC
else
b DESC, c DESC END

Note: DESC or ASC is dynamic coming from java-code.

View 3 Replies View Related

SQL & PL/SQL :: Does Ascending Index Ensures That Query Without Order By Will Have Result Set Sorted

Jul 4, 2013

Does ascending index ensures that query without order by will have the result set sorted?

E.g. the query is

select * from table_t where odm_type='I' and odm_uid>nvl(OpUidParm,-1);
column odm_type has index created like this (default is ASC):
create index ODM_UID_I on table_t (ODM_UID);

Will such a query always return the first record having the minimal odm_uid in all Oracle versions?

View 4 Replies View Related

Forms :: Display Checkbox Selected Records In One Column Of Form / When Push Button Is Pressed

Jul 2, 2011

how to display check box selected records in one column of form when push button is pressed.

View 1 Replies View Related

Performance Tuning :: Order Of Steps In Execution Path And Order Of Predicates In The Plan

Mar 20, 2012

Which step in the following plan is the first step of execution

I reckon it is "TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE"

Is that correct?

In the "Predicate Information (identified by operation id):"

section the predicates - access and filter for the step "TABLE ACCESS FULL | PYMNT_DUES" are displayed first

Isn't there any relation between the order of execution steps and the order in which predicates are displayed?

Execution Plan
----------------------------------------------------------
Plan hash value: 538700484
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2364 | 15 (14)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 2364 | 15 (14)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 2364 | 14 (8)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 2313 | 13 (8)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 2281 | 12 (9)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 2255 | 11 (10)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 175 | 6 (17)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INDX_2 | 12 | 612 | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PYMNT_DUES | 43 | 5332 | 3 (0)| 00:00:01 |
| 10 | VIEW PUSHED PREDICATE | | 1 | 2080 | 5 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 154 | 5 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 103 | 4 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE | 1 | 32 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | INDX_BBS_1 | 3 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| DAILY_CHECK | 1 | 71 | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | INDX_SEARCH | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | INDX_IAM_SR_NO | 1 | 26 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | INDX_2 | 1 | 32 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------

View 3 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

SQL & PL/SQL :: Query To Display Next Ten Dates

Apr 28, 2010

When i had a interview last week they ask me to write a query to display the next ten dates using sql query ( without using PL/SQL).

View 11 Replies View Related

Pagewise Display In Oracle Query

Aug 14, 2010

the scott.emp table has 14 employee records. I want to display like this in a select query .

ename ename ename
-------- -------- --------
adams abc def
scott xyz pal
smith hij efg
------ -------- -------
page1 page2 page3

and another 5 records
ename ename
-------- --------
asd rty
afg uio
wrt no name
------- ---------
page4 page5

View 1 Replies View Related

Getting Query To Display Only 150 And 162 As They Have Purchased Only One Product?

Jun 24, 2011

Is there a way to find customers purchased only single product from the following table?

cusno Product Date
----- ------ ----
121 ES 03/12
121 NT 30/12
131 ST 03/12
13 WT 04/12
150 ES 05/12
150 ES 06/12
150 ES 07/12
160 MN 05/12
160 ES 06/12
160 ES 07/12
162 NT 08/12

I need a query to display only 150 and 162 as they have purchased only one product.

View 2 Replies View Related







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