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


ADVERTISEMENT

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

Forms :: Display Next Record?

Apr 22, 2010

I have a form in which the fields are not database items i want to display the next record, i know i can use the next_record for the same but it does not work since it is not the database item, is there any way when the item is not the database item and i can see the next record. I use the cursor for loop but since it fecthes all the records in one go it cannot display one record at the time in the form..

View 3 Replies View Related

SQL & PL/SQL :: Get Value From One Column But Display ID Different Fields

Oct 22, 2012

get members's home and work and cell phone number if it's avaiable. if not just disply null.

Table name : Test

member entity_id Phone_id phone_code Phone_num
abc 400175 200201 HME 49.6171.59501
abc 400175 200202 CEL 491.170.9174054
abc 400175 200203 WRK 49.6142.7.76675
def 521985 199991 HME 555-555-5555
ghi 345634 188881 HME 222-345-2345
ghi 345634 188882 CEL 222-456-6565

[Code]...

View 6 Replies View Related

Forms :: Display Map Using Mapviewer?

Sep 6, 2012

How do I display map on the oracle forms using mapviewer?

View 1 Replies View Related

SQL & PL/SQL :: Display Ref Cursor In A Procedure

Aug 9, 2010

I have created a Pl/SQl block as the following

Declare

v_dname dept.dname%type;
v_loc dept.loc%type;
v_empno emp.empno%type;
v_job emp.job%type;
p_result SYS_ refcursor;

Begin
open p_result for select distinct d.dname, d.loc, e.empno, e.job from dept d, emp e where d.deptno=e.deptno;
fetch p_result into v_dname,v_loc,v_empno,v_job;
dbms_output.put_line(v_dname||' '||v_loc||' '||v_empno||' '||v_job);
end;

This is throwing me error saying
" ORA-06550: line 7, column 15:
PLS-00103: Encountered the symbol "REFCURSOR" when expecting one of the following:"

View 11 Replies View Related

Forms :: Display Dynamic LOV?

Aug 28, 2007

I want to create dynamic Lov. How can I create it? For practice I create a LOV at design time

RECORD_GROUP_QUERY value:
SELECT * FROM
EMP.

THEN I write some code in WHEN-BUTTON-PRESSED Trigger to replace RECORD_GROUP_QUERY at run time. Code is:

........
v_rg_id:=CREATE_GROUP_FROM_QUERY('TEST','SELECT * FROM DEPT');
........

After this I found a error at run time which is FRM-41826: Cannot replace group; columns don't match LOV.

View 6 Replies View Related

SQL & PL/SQL :: How To Display All The Rows In A Table

Sep 8, 2010

I am bit confused to display all the rows in a column using stored procedure like

In sqlserver:
create procedure test
as
begin
select * from table;
end
in sqlserver it is working perfectly.

In oracle:
create or replace procedure test
is
begin
select * from table;
end;

But in oracle i am getting an error like select * into something like that.I understand the error. i.e. i need to move the column values into variables.if it a single row then there will be no problem.i want to display all the rows in the table at one time.i can not use cursors.

View 3 Replies View Related

SQL & PL/SQL :: Display All The Months Between Two Dates?

Aug 17, 2010

I have this same problem and still i am getting one error message

ORA-00933: SQL command not properly ended

and the code in highlighted in 'by'

'from test_case partition by'

View 6 Replies View Related

SQL & PL/SQL :: Output Layout And Display Changes?

Oct 11, 2011

SQL

SELECT ug_name, rss_user_name
FROM ru_ug
WHERE rss_name = 'EXAMPLE' AND ug_name LIKE 'cn=SNG-PWI11_%'
ORDER BY ug_name

Output

UG_NAMERSS_USER_NAME
cn=SNG-PWI11_DBE_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname55,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_DBE_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname49,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_DBE_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname19,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_I_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname932,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_I_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname56,ou=Netgroup,dc=TMM,dc=netops
cn=SNG-PWI11_I_TMM,ou=Netgroup,dc=TMM,dc=netopscn=hostname77,ou=Netgroup,dc=TMM,dc=netops

But I wish other output:

UG_NAME1 UG_NAME2
RSS_USER_NAME1 RSS_USER_NAME3
RSS_USER_NAME2

Table:

CREATE TABLE RU_UG
(
RSS_USER_NAME VARCHAR2(255 BYTE) NOT NULL,
UG_NAME VARCHAR2(255 BYTE) NOT NULL,
RSS_NAME VARCHAR2(32 BYTE) NOT NULL,
RSS_TYPE VARCHAR2(12 BYTE) NOT NULL,

[code]....

View 1 Replies View Related







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