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


ADVERTISEMENT

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

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

Forms :: Executing Query When Display Box Value Changes

Aug 24, 2010

In my form i have 2 datablocks, the first contains only a display item which is populated from a lov when a user presses a button. The second datablock shows a list of items which should be queried dependant on the value of the above LOV. What i thought i could do is on a post-change trigger for the display item is:

go_block('block_name');
do_key('execute_query');

However, i am told i cannot do these in a post-change trigger, how to acheive what I am looking. Would i perhaps have to create my own trigger somehow or is their a simplier way.

View 2 Replies View Related

SQL & PL/SQL :: Query To Display The Office And Count(*)?

Nov 1, 2012

I need a query to display the office and count(*)

where rownum > 6 i.e

here is the data

office
------
hq
hq
hq
hq
hq
hq1
hq1

[code]....

I need a query to display the top 3 counts and the 4 row should be sum of count of other offices.

my result should look like this

office count(*)
--------- --------
hq 5
hq1 4
hq2 3
other 10

View 4 Replies View Related

SQL & PL/SQL :: Query On Single Column To Display As Rows

Aug 5, 2010

I has a table of structure of varchar2 datatype.

NO
----------
1-2
3-4
5-6

desired output is:

SQL>1
2
3
4
5
6

The table has single column & the values may differ,that is, they may have 1-2-3-...-n in a single row, but the desired output is to be in the rows as shown above.

I tried concepts of SQL up to my knowledge, but I failed. The query to be done only in SQL.complete this query.

View 10 Replies View Related

SQL & PL/SQL :: Hierarchical Query To Display Chart Of Account?

Apr 25, 2013

I want Hierarical query to display my Chart_Of_Account. I want to make a tree Form in 6i error i am getting is connect by nocycle prior account_code=parent_code
*
ERROR at line 4:
ORA-00920: invalid relational operator

Table

Create Table Chart_Of_Account (Account_Code Char(19), Account_Title varchar2(70), Parent_Code Char(19))
insert into Chart_Of_Account ('DGHOA01010101000001','TEST','DGHOA01010100000000')
insert into Chart_Of_Account ('DGHOA01010101000002','TEST1','DGHOA01010100000000')
insert into Chart_Of_Account ('DGHOA01010101000003','TEST2','DGHOA01010100000000')

select -1,level,account_code||' - '||ACCOUNT_Title,'NULL',to_char(ACCOUNT_CODE)
connect_by_iscycle from chart_of_account
start with Substr(account_code,13,7)='0000000'
connect by nocycle prior account_code=parent_code

View 3 Replies View Related

PL/SQL :: Create Query To Display Total No Of Employees?

Aug 22, 2012

How can I create a query to display the total no of employees and, of that total, the number of employees hired in 1995,1996,1997,1998.

I am using Oracle® Database Express Edition 10g Release 2 (10.2)

View 3 Replies View Related

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 :: Query Of Functions - Display Half Part Of String

Jun 8, 2011

I want to write a plsql program to display half part of a string .Example there is a string like gillmadden@myharbour.org.uk

I want to display only the later part of the string

myharbour.org.uk as output.

Can we write using string functions in plslql?

View 19 Replies View Related

SQL & PL/SQL :: Display Data In Required Format By Select Query?

Dec 12, 2012

i like to display a Table data like the below format,

Output:

EMPNO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
00094 122 153 145 224 245 545 114 544 444 111 555 222
00095 233 123 145 224 205 545 114 444 444 111 555 222
00096 163 123 145 224 215 545 114 551 444 111 555 222
00097 163 0 145 224 215 545 114 551 444 111 555 222
conditions:

where condition:
where year = 2007

Table Structure:

create table HR_PAYSLIP
(
EMP_NO VARCHAR2(6) not null,
YEAR NUMBER(4) not null,
MONTH NUMBER(2) not null,
BASIC_PAY NUMBER(9,2),
)

Insert Command;

INSERT INTO PAYSLIP (EMP_NO, YEAR,MONTH,BASIC_PAY)
VALUES(00046, 2007, 1, 2314);

Pls Note: The above table data i have mentioned is an example with employee numbers and the basic_pay for all months in the particular year 2007, the employee no may be more and that must be displayed only one time like above for year 2007, and if the basic salary is zero for a month then it should be displayed as zero for a particular month

So how to write a Query for that?

View 9 Replies View Related

SQL & PL/SQL :: Query To Display Repeated Column Values As Null?

Dec 13, 2012

I have table with the values as below.

C1C2C3C4
NAMEJOHN10ABC
NAMESMITH30DEF
NAMEROBERT60XYZ

I dont want to print the repeated value(NAME) of C1 multiple times as below.

C1C2C3C4
NAMEJOHN10ABC
SMITH30DEF
ROBERT60XYZ

I could do it using the below query using union with the rownum.

select * from (
select rownum rn, c1,c2,c3,c4 from table_new
) where rn =1
union
select * from (
select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new
) where rn between 2 and 3

Is there any other way of displaying using a single sql query.

View 17 Replies View Related

SQL & PL/SQL :: Display Office Address / House Address And Emp ID In Single Query?

Feb 23, 2013

I have two tables emp , emp address as below

emp table :
emp_id emp_name,
1 rajesh
2 suresh

emp address table :

emp id emp_addresstype emp address
1 O kukatpally
1 H Hitech
2 O Kolkata
2 H hydar nagar

I need the query to display the Office address & House address & emp id in single query

ex: 1,Kukatally,hitech
2,kolkata,hydarnagr

View 3 Replies View Related

SQL & PL/SQL :: Union Query Need To Add Extra Dummy Field To One Side Of Query

Dec 8, 2005

I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a

select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)

however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.

View 6 Replies View Related

SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)

Dec 5, 2012

I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.

View 1 Replies View Related

Reports & Discoverer :: Excluding SQL Query Data When Linked With XML Query?

Apr 26, 2013

I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?

e.g.:

Table emp:

e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800

XML File

<e_id>
143
876

So, I want the output to be:

e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100

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

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related

SQL & PL/SQL :: Oracle Query Joining A Table With Existing Query?

Jun 19, 2012

I have the following four tables with the following structures Table A

ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE

Table B

ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE

when these two tables were joined like the following:

Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)

Now i have to join the next table C with table B

Table C structure is

ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer

I have the common column such as ColD2 and colB4 to get the Col D3

how do i join the existing query + join between table b and table c?

View 4 Replies View Related

Forms :: F11(Query Mode) And Execute Query In Oracle?

Jul 17, 2011

how to achieve F11(Query mode) and Execute Query in Oracle Forms?

View 1 Replies View Related

SQL & PL/SQL :: Query Using DBLink Returns More Rows Than Direct Query?

Apr 6, 2010

I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.

For example:

select
x,y,z
from
mytable@dblink

returns 788,324 rows

while
select
x,y,z
from
mytable

returns 712,102 rows

It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.

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

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







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