PL/SQL :: Sort Column Based On Value

Oct 17, 2012

Query result gives out put like in the following order

CITY NAME
CHENNAI
DELHI
LONDON
RIO DE GENARO

How to get output in the following sort order

LONDON
CHENNAI
DELHI
RIO DE GENARO

i dont want to hardcode the column values ..it will be great if i get option to generate dynamically.

View 9 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Sort Query Based On Financial Year

Jun 12, 2012

Haw to sort the data based on financial year.

For example Indain Fiscal year 01-Apr-2010 to 31-Mar-2011

Apr2010 -1
May2010 -2
.
.
.
.
Feb2011-11
Mar2011-12

I had given a query..for Quarter idendification

SELECT distinct
'Qtr'
|| CASE
WHEN PERIOD BETWEEN TO_DATE ('01-04-2010', 'DD-MM-YYYY')
AND ADD_MONTHS (TO_DATE ('01-04-2010', 'DD-MM-YYYY')-1, 3)

[code].....

View 2 Replies View Related

Forms :: Sort On Non Database Column

Jan 26, 2011

I have a master detail form that presents a list of 22 items that can be checked or unchecked depending on whether the test is needed.

The issue I have is trying to order or sequence the list in a specific order without a database column.

Here is the code I am trying to use in a post query trigger:

CASE :SAMPLE_TESTS.TESTCODE
when 'L001'
then :SAMPLE_TESTS.SEQ := '02' || :SAMPLE_TESTS.TESTCODE;
when 'L002'
then :SAMPLE_TESTS.SEQ := '03' || :SAMPLE_TESTS.TESTCODE;
when 'L003'
then :SAMPLE_TESTS.SEQ := '04' || :SAMPLE_TESTS.TESTCODE;

[Code]..

The non-database field is character with a length of 6.

When I try to use this field in the order by property of the data block it is unable to perform query.

Is there another way?

View 1 Replies View Related

PL/SQL :: How To Sort Inside Column Values (VC2)

Jan 14, 2013

I need to sort values inside a column. Data in column is concat with '|' (pipe). There could be 1 to many values in one column.

The order of the rows is not relevant here.

Test case:
-------------

create table t1 (col1 varchar2(100));

insert into t1 values ('H302|H411|H317|H314|H312');

insert into t1 values ('H315|H410|H400|H318');

insert into t1 values ('H226|H400|H331|H318|H317|H315|H310|H301');

insert into t1 values ('H301');

commit;

select col1 from t1;

COL1
----------------------------------------
H302|H411|H317|H314|H312
H315|H410|H400|H318
H315|H318
H301
H226|H400|H331|H318|H317|H315|H310|H301

Need is this:

COL1
----------------------------------------
H302|H312|H314|H317|H411
H315|H318|H400|H410
H315|H318
H301
H226|H301|H310|H315|H317|H318|H331|H400

View 4 Replies View Related

PL/SQL :: Scalar Subquery To Sort Column

Jul 7, 2012

am using a scalar subquery in order to sort by department_name column. My sql statement is like this:

select employee_id,last_name
from employees e
order by
(select department_name
from departments d
where d.department_id= e.department_id)

i want to display also the department_name in my output.

select employee_id,last_name,
(select department_name
from departments d
where d.department_id= e.department_id) department_name,department_id

[Code]...

the result is sorted and name is also displaying.but am not getting the output properly.

result is like this:

EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME DEPARTMENT_ID
--------------------------------------------------------------------------------------------------------
205 Higgins Accounting 110
206 Gietz Accounting 110
200 Whalen Administration 10
107 Lorentz IT 60
105 Austin IT 60
103 Hunold IT 60
104 Ernst IT 60
106 Pataballa IT 60

am getting the first three line.but as 5 worker are in IT department so in that case how result is sorted..

View 1 Replies View Related

Create A Separate Sort Order Column On Table

Oct 31, 2006

I have data that i am sorting, the data is mostly numeric (format of XXX-1234). is there any way to have my query sort xxx-1000 AFTER xxx-999? right now i am thinking i will have to create a separate sort order column on my table.

View 2 Replies View Related

Sort Ascending Or Descending Of Last Total Column Of Matrix Report R6i

Nov 3, 2010

How can I Sort Ascending or Descending of Last Total Column of Matrix Report R6i i.e. F_SumsalPerempno, summing total salary of each empno at end of each row.

I need the employee paid highest amount of total salary during the year to appear on first row, while months to display as per original order.

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy

Oct 15, 2013

I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically

ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')

500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498

View 1 Replies View Related

Reports & Discoverer :: Value In Table Column Based On Some Existing Column Value Automatically Without User Intervention

May 15, 2011

i have two questions.

(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'

(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.

View 3 Replies View Related

SQL & PL/SQL :: Trigger Which Changes Content Of One Column Based On Information Within Another Column

Sep 10, 2010

I am trying to create a trigger which changes the content of one column based on the information within another column.

For Example, if the 'ITEMQUANTITY' field drops below 1 then I want the STATUS column to say 'Out Of Stock'.

View 23 Replies View Related

Max Row Based On Value In Column?

Mar 3, 2011

How to get the max row based on a value in a column

View 1 Replies View Related

Decode Based On Different Column

Jul 24, 2007

Is it possible to decode based off a different column? I have a status column that i want to change the value of with a decode, but only if my date column has been populated.

So if status has a value of "New" and my date column is null, then i want it to stay new. If it is populated, i want my status to change to "released"

View 5 Replies View Related

SQL & PL/SQL :: Identifying Column Name Based On A Value

Apr 11, 2011

I would like to know, how to:

1) search for a given value in a row, and
2)in that row, identify the name of the column that contains the value

Search scope is only a single row.

View 7 Replies View Related

SQL & PL/SQL :: Rows Based On Column

Jun 25, 2012

I have a table with below structure

Id - Desc -repeat_times
1a - andy - 3
2b - santa - 5

I want to write a sql query which outputs two columnns like below. the particular row to be repeated as per the repeat_times column.

1a-1 andy
1a-2 andy
1a-3 andy
2b-1 santa
2b-2 santa
2b-3 santa
2b-4 santa
2b-5 santa

I have tried but am failing..

View 1 Replies View Related

SQL & PL/SQL :: Search Based On Column

Nov 18, 2012

can we make a search based on column , actually i have one requirement where the data is stored in column as comma seperated values like below

arif,123455,123456,77777
sait,123455,123456,66666
tomm,666666,123455,123456

I want to filter only 123455 and 123456 from this list, is it possible to get a range as i want to use it as report parameters , i can do it by using like operator as below

create table prd_mast ( prd_cd varchar2(12), prd_desc varchar2(3000));
insert into prd_mast values ('1','arif,123455,123456,77777');
insert into prd_mast values ('2','sait,123455,123456,66666');

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Table Search Based On Column Value?

Aug 25, 2010

in one of the data base some of the column values are TE. i wanted to serch in what are the tables this TE values are present. so m running the below function

CREATE OR REPLACE FUNCTION find_in_schema(val VARCHAR2)
RETURN VARCHAR2 IS
v_old_table user_tab_columns.table_name%TYPE;
v_where VARCHAR2(4000);
v_first_col BOOLEAN := TRUE;

[code]....

but v_where := v_where || ' or ' || r.column_name || ' like ''%' || val || '%''' is giving me numaric or value error when i run as select find_in_schema('@TL') from dual; so how can i go ahed with the serch ?

View 2 Replies View Related

SQL & PL/SQL :: Multiple Rows Based On One Column

Mar 24, 2013

I have one table , with one column having 2,3 or 4 machine codes , i need to display them as each row per machine code will it be possible to do as i have thousands of records similar to the test case and which i had to do it manually in excel and then upload it back.

create table ow_oper_setup (wo_no varchar2(12),mrk_no varchar2(20),pos_no varchar2(30),mc_code varchar2(60))

insert into ow_oper_setup VALUES ('1270','1270001','W165','IR HO BV ')
insert into ow_oper_setup VALUES ('1270','1270001','W1332','IR BV ')
insert into ow_oper_setup values ('1270','1270001','W1367','RE HO SC BV ')
insert into ow_oper_setup values ('1270','1270001','W389','RE HO SC BV')

commit;

SELECT * FROM ow_oper_Setup;

WO_NOMRK_NOPOS_NOMC_CODE
12701270001W165IR HO BV
12701270001W1332IR BV
12701270001W1367RE HO SC BV
12701270001W389RE HO SC BV

--i want the output in the following way or the same table data to be replaced as below

WO_NOMRK_NOPOS_NOMC_CODE
12701270001W165IR
12701270001W165HO
12701270001W165BV
12701270001W1332IR
12701270001W1332BV
12701270001W1367RE
12701270001W1367HO
12701270001W1367SC
12701270001W1367BV
12701270001W389RE
12701270001W389HO
12701270001W389SC
12701270001W389BV

View 12 Replies View Related

SQL & PL/SQL :: Find Out Table Name Based On Column Name

Jul 28, 2011

i need an sql query or procedure to find out table name based on the column name..

View 4 Replies View Related

SQL & PL/SQL :: Create New Column Based On MIN DATE

Jun 26, 2012

CREATE TABLE DAN_DATES
(ID VARCHAR2(12),
YEAR VARCHAR2(,
TERM VARCHAR2(,
START_DATE VARCHAR2(12))

INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120227','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120626','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110226','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110725','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2012','1201',to_date('20120227','YYYYMMDD'));

Want to take the Start_Date for that year, CREATE A NEW COLUMN and place that START_DATE (which is row 1 for the year (min)) in it. So for ID 1 TERM is 1201 and 1202 BUT we want the top start date (earliest start date) and CREATE a clumn (NEW_START_DATE) and place that date in there wherever year is 2012.

I want to get
IDYEARTERMSTART_DATEMIN_DATE
12012120127-Feb-1227-Feb-12
12012120126-Jun-1227-Feb-12
22011110126-Feb-1126-Feb-11
22011110125-Jul-1126-Feb-11
22011110126-Sep-1126-Feb-11
22012120227-Feb-1227-Feb-12

View 10 Replies View Related

SQL & PL/SQL :: Repeat Rows Based On Column Value

May 19, 2010

I have a select statement

select name, address, qty
from names_table

and it returns a result such as

name|address|qty
alan,1 the street,5
bert,2 the road,3

what I want to do is create an output that takes the value in the qty column and repeates the number of rows e.g. the alan record 5 times and the bert record 3 times

name|address|qty
alan,1 the street,5
alan,1 the street,5
alan,1 the street,5

[code]...

how I might go about acheiving this?

View 14 Replies View Related

SQL & PL/SQL :: Inserts Based On Column Values

Aug 15, 2012

I have a data in one table with 6 columns where user may be updating values in all of these 6 columns or he may enter 3 or 4 columns based on that inserts should take place, this is similar to my previous thread , i am using if condition to check column for null if its not null then i will make a insert , but is there any other easier way to do this.

CREATE TABLE ot_inspect_head
(inh_sys NUMBER,i_txn_code VARCHAR2(12),i_no NUMBER,i_ref_txn VARCHAR2(20),i_ref_no NUMBER);
CREATE SEQUENCE inh_sys START WITH 1;
CREATE TABLE ot_inspect_item
(ii_inh_sys NUMBER , ii_pi_sys NUMBER,ii_sys NUMBER,ii_item_cd VARCHAR2(12),
ii_grade VARCHAR2(12),ii_qty number, ii_flex_01 VARCHAR2(12),ii_flex01_qty number,
ii_flex_02 VARCHAR2(12),ii_flex02_qty number,ii_flex_03 VARCHAR2(12),ii_flex03_qty number);
CREATE SEQUENCE ii_sys START WITH 1;

insert into ot_po values ('ss-po',1,ph_sys.nextval);
insert into ot_inspect_head values (inh_sys.nextval,'ss-ins',1,'ss-po',1);
commit;
select * from ot_inspect_item

II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- -------------
2 1 2 HEA100 A 100

--Now if the inspection user issues the update statement , it will delete this row
--from ot_inspect_item and reinserts the values with values based on
--ii_flex_01,ii_flex_02,ii_flex_03
[code]...

View 5 Replies View Related

SQL & PL/SQL :: Substring Column Based On Condition

Oct 20, 2010

I have a column transaction Number . It has the transaction number of goods sold.. Below is the sample.

1. PIT0120029015554492215851181828221018554492R06
2. XY1029201195J05
3. YJ1039201176J01

My Query :

I need to substr the transaction number which starts with PIT to susbst (trxno,1,12) .. and anything other than PIT i need full number without substr . But when i use the above code :" susbstr(trxno,1,12) ..": it will substr entire column,. Is there any way to substr only PIT and leave others,

DB:oracle 10g

View 7 Replies View Related

Update A Column Based On Another In Same Table

Apr 1, 2012

I am trying to update a column based on another column in the same table (student table) and a column from another table (school table)

Code is:

update student_table
set student_code =
(select l.student_code
from school_table l, student_table n
where l.school = n.schoolname)

I get the following error ORA - 01427 Single-row subquery returns more than one row.

View 1 Replies View Related

SQL & PL/SQL :: How To Get Total Number Of Occurrences Based On Value Of Column

Apr 20, 2013

I have a query that should return count the number of rows depending on the value of SLOT. The expected result will be like this:

WIPDATAVALUE SLOT N M
1-2 TRALTEST43S1 1 3
1-2 TRALTEST43S1 2 3
3 TRALTEST43S1 3 3
4-6 TRALTEST43S2 1 4
4-6 TRALTEST43S2 2 4
4-6 TRALTEST43S2 3 4
7 TRALTEST43S2 4 4

The M column is used to count the total number of occurrences of a SLOT. Now, as for the N field, this is used to count the occurrence of the SLOT. In my example for the SLOT TRALTEST43S1, it has three occurrences so M will be 3. Why 3, is because of the WIPDATAVALUE. The WIPDATAVALUE of TRALTEST43S1 is 1-2 and 3. 1-2 WIPDATAVALUE signifies two occurrences (one to two) and 3 signifies only one occurrence. As for N, it should just count the number of occurrence. To further explain, see below:

WIPDATAVALUE SLOT N M
1-2 TRALTEST43S1 1 3 -> First occurrence in the total of 3
1-2 TRALTEST43S1 2 3 -> Second occurrence in the total of 3
3 TRALTEST43S1 3 3 -> Third occurrence in the total of 3
4-6 TRALTEST43S2 1 4 -> First occurrence in the total of 4
4-6 TRALTEST43S2 2 4 -> Second occurrence in the total of 4
4-6 TRALTEST43S2 3 4 -> Third occurrence in the total of 4
7 TRALTEST43S12 4 4 -> Fourth occurrence in the total of 4

This is the query that I have so far:

SELECT DISTINCT
WIPDATAVALUE, SLOT
, LEVEL AS n
, m
FROM
(
SELECT
WIPDATAVALUE
, SLOT
, (dulo - una) + 1 AS m
[code]....

I think that my current query is basing its M and N results on WIPDATAVALUE and not the SLOT that is why I get the wrong output. I have also tried to use the WITH Statement and it works well but unfortunately, our system cant accept subquery factoring.

View 2 Replies View Related

SQL & PL/SQL :: View With Column Based On Row Values Of Another Table

Apr 13, 2011

I have table with values :

PROV_IDMEASURE_IDPERCENTAGE
Z0000221P114 45
Z0000135P115 68

For the column all possible values are ( P102,P101,P103 etc toP124). I want to create a view ( if possible ) from the above with data output as :

PROV_ID P101 P102 P103 ............................P124
z000234 23 45 60 72

basically this view has columns based on the previous tables column ( MEASURE_ID) values and the values will be corresponding value in column Percentage.

View 2 Replies View Related

SQL & PL/SQL :: Nested Query Based On Value Of Dependent Column?

Sep 10, 2013

In the below Table Structure, TB_Vehicles is the Master table and TB_Cars, TB_Bikes, TB_Cars are Satellite tables which hold more info about respective vehicles.

I want users to search using the Name. So, when users enter Name as 'Access', my query should give all information about that vehicle, including that from Satellite Tables (using both TB_Vehicles and TB_Scooters).

SELECT *
FROM TB_Vehicles
WHERE V_NAME = 'Access';

Similarly, if user enters 'Linea', it should give info from TB_Vehicles and TB_Cars tables.

So, with V_NAME as input, I'll find V_TYPE from TB_Vehicles table, and using that, I need to identify which satellite table, I need to join to retrieve more info.

Do I need to use CASE or DECODE to achieve this?

View 25 Replies View Related

PL/SQL :: Update Column Based On Sum Of Data From Another Table

Apr 17, 2013

We had two tables.

Table 1: matusetrans

ITEMNUM Location Quantity transdate
AM1324 AM1 2 12-4-12
AM1324 AM1 2 15-5-12
AM1324 AM1 3 10-6-12
AM1324 AM1 4 5-1-13

[Code]....

Table 2: Inventory

ITEMNUM STORELOC lastyear currentyear
AM1324 AM1 need sum(quantity) here need sum(quantity)
AM1324 AM2 need sum(quantity) here need sum(quantity)

We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.

we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.

How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table

Edit/Delete Message

View 6 Replies View Related

Fetching Table Names Based On Column Value

Mar 6, 2013

I have a schema in which i have 10000 tables and i want to fetch table names from the schema where org_id is not equal to 1,

Note: Here org_id is the column name .

Provide me sql code or procedure to achieve this.

View 2 Replies View Related

PL/SQL :: How To Get Total Number Of Occurrences Based On Value Of Column

Apr 20, 2013

here on your forum but has been following several threads ever since.So anyway here is the thing, I have a query that should return count the number of rows depending on the value of SLOT. Something like this:

WIPDATAVALUE          SLOT             N            M
1-2                   TRALTEST43S1     1            3
1-2                   TRALTEST43S1     2            3
3                     TRALTEST43S1     3            3
4-6                   TRALTEST43S2     1            4
4-6                   TRALTEST43S2     2            4
4-6                   TRALTEST43S2     3            4
7                     TRALTEST43S2     4            4-----

As you can see above, on the SLOT TRALTEST43S1, there are three occurrences so M (Total number of occurrences) should be three and that column N should count it. Same goes with the SLOT TRALTEST43S2. This is the query that I have so far:

SELECT DISTINCT
WIPDATAVALUE, SLOT
, LEVEL AS n
, m
FROM
(
  [code]...

I think that my current query is basing its M and N results on WIPDATAVALUE and not the SLOT that is why I get the wrong output. I have also tried to use the WITH Statement and it works well but unfortunately, our system cant accept subquery factoring.

View 23 Replies View Related

PL/SQL :: How To Enter Value Based On Condition In Column Without Using Trigger

Feb 5, 2013

while replicating form mssql 2005 it is entering space for null in oracle clob col.so i wanted to know.

1)can check constaint modify the content of column on which it is defined?

like i want to enter null in a column , if entered date is greater than current date else the entered date.i do not want to use triggers or client side script.

View 1 Replies View Related







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