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


ADVERTISEMENT

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

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

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 :: Use Cartesian Product?

Mar 7, 2013

When to use Cartecian product???? is there any special cases to use cartecian product?

View 2 Replies View Related

SQL & PL/SQL :: Product Of Values In The Column?

Jul 29, 2010

how to find the product of the values in the column.. assume that the column has number(2) as its data type..

as we use sum(colname) for the sum of the values in the column there s no function to find the product.

View 3 Replies View Related

SQL & PL/SQL :: SUM Product By DATE By LOCATION

Dec 14, 2011

I am trying to query a Oracle database table that contains sample records like the one below...

DATE LOCN PROD1 PROD2
09/12/2011 L1 6 2
09/12/2011 L2 3 7
10/12/2011 L1 4 1
10/12/2011 L2 3 3
11/12/2011 L1 2 2
11/12/2011 L2 2 0
12/12/2011 L1 4 1
12/12/2011 L2 5 0

I am trying to use the Oracle Sum() to get a grouping by DATE, LOCATION, SUM(PROD1+PROD2) for DATE periods 10/12/2011 and 11/12/2011. Below is the desired end result.

DATE LOC1 LOC2
10/12/2011 5 6
11/12/2011 4 2

View 1 Replies View Related

SQL & PL/SQL :: Calculates Value Of Product Sold

Nov 25, 2011

Stored Procedure: Receives the MFR and PRODUCT identifying the product sold as well as the Quantity purchased by the customer.

 Calculates the value of the product sold (looks up the cost of the product in the products table and multiplies by the quantity sold)
 Returns the calculated value.

View 12 Replies View Related

PL/SQL :: Add Four More Column And Group By PRODUCT

Sep 28, 2012

I am new to PL/SQL environment.. ( USING PL/SQL Oracle)

SELECT TABLE1.PRODUCTID, TABLE1.TYPE , TABLE1.PRODUCT, TABLE2.#SOLD
FROM TR.TABLE1
INNER JOIN
PR.TABLE2
ON (TR. PRODUCTID = PRODUCTID )

Query result.

Product ID     Product          Type          #Sold
1     Fruit          Banana          2
2          Food          Rice          4
3          Food          Sugar          6
4          Fruit          Orange          2
5          Fruit          Apple          10
6          Food          Sugar          12
7          Food          Corn          3
8          Fruit          Apple          5
9          Fruit          Banana          2
10          Food          Sugar          1
11          Fruit          Banana          5
12          Food          Sugar          7

But would like to add Four More Column and group by by PRODUCT.Final Result will look like :

     {Product}     ,     {Type}     ,     {#Sold}     ,     0 to 3     ,     4 to 9     ,     >10          Total Sold     
          ,     {Corn}     ,     3     ,          ,          ,                    
          ,     {Rice}     ,          ,     4     ,          ,                    
          ,     {Sugar}     ,          ,          ,          ,     26               
     {Food}     ,          ,          ,          ,          ,               33     
          ,     {Apple}     ,          ,          ,          ,     15               
          ,     {Banana}     ,          ,          ,     9     ,                    
          ,     {Orange}     ,          ,     2     ,          ,                    
     {Fruit}     ,          ,          ,          ,          ,               36     }

View 9 Replies View Related

SQL & PL/SQL :: List Most Popular Product For Every State?

Feb 11, 2011

so here is the query : For every state, list the most popular product. Here is the query I have so far :

SELECT C.State, P.Product_Name, Count(*) cnt FROM Product P, Customer C, OrderTable O, LineItem L WHERE C.CID = O.CID AND O.OID = L.OID AND L.PID = P.PID GROUP BY C.State, P.Product_Name;

Result :
STATE PRODUCT_NAME COUNT(*)
---------- -------------------- ----------
New Jersey Computer 3
Texas Computer 1
New Jersey Speaker 2

I would need the result to only say New Jersey Computer and Texas Computer because I only want a list of the states with the product name that is sold the most in each state.All I need to do is have the query only select the product name with the max count for each state...

View 4 Replies View Related

Windows :: Oracle 10g Installation Asking For Product.xml

Jun 18, 2013

I have oracle10g installable in .rar format. I have unzipped it and started installer through setup.exe file, but in the secnd step itself it is asking product.xml file location , i searched entire installables but could not find any thing with such name, installing this oracle 10g on windows 2003 server.

View 12 Replies View Related

SQL & PL/SQL :: Return Any Product That Doesn't Have TEST_UOM_GROUP

Jan 13, 2011

I have the following tables: -

CREATE TABLEtest_articles
(idNUMBER(9),
code VARCHAR2(20),
description VARCHAR2(40),
uom_group_id NUMBER(9),
in_exchange NUMBER(1) DEFAULT 0);
[code]....

However, I'd also like it to return any product that doesn't have a TEST_UOM_GROUP, for example the 'Bud'.I've hit a brick wall and just keep going around in circles and not acheiving the result I'm after!how to either change the SQL Query.

View 22 Replies View Related

Forms :: Microsoft Product And Oracle Application Are In One Server?

Jun 19, 2012

Bulk of Microsoft Application is running on windows server 2003 (OS) , is it possible to run parallel an oracle application on the same server .

View 4 Replies View Related

SQL & PL/SQL :: Searching PRODUCT Column Based Upon User Input?

Apr 30, 2010

I have table in Oracle with one column PRODUCT. Column PRODUCT have following values -

Account Management
Active Directory
Adobe Acrobat Reader
NT Account
Application Security

[code]....

I am designing application where I need to search for PRODUCT based upon user's input. Lets say user wants search on 'Laptop Account Broken'. I want to search for all products which contains any of words in user's input. So based upon user's input I want output like below.

Expected Output:

Account Management
NT Account
WebSite Account
HP Laptop

View 2 Replies View Related

Forms :: Frm-41211 Integration Error / SSL Failure Running Another Product

Jan 20, 2010

I have just migrated my database from oracle7 to Oracle9i. My application is still on forms 4.5 and reports 2.5.

i am having a problem after the migration. I am able to run my forms, but i cannot run my reports from my form (button). i am having the error : "frm-41211 integration error: SSL failure running another product". I want to mention that i am able to run the report from the report builder.

View 6 Replies View Related

PL/SQL :: Difference Between CROSS JOIN And Comma-notated Cartesian Product?

May 22, 2013

Oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
OS: Linux Fedora Core 17 (x86_64)

I was practicing on Recursive Subquery Factoring based on oracle examples available in the documentation URL....I was working on an example which prints the hierarchy of each manager with his/her related employees. Here is how I proceed.

WITH tmptab(empId, mgrId, lvl) AS
(
    SELECT  employee_id, manager_id, 0 lvl
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT  employee_id, manager_id, lvl+1
    FROM employees, tmptab
    WHERE (manager_id = empId)
[code]....

107 rows selected.

SQL> However, by accident, I noticed that if instead of putting a comma between the table names I put CROSS JOIN, the very same query behaves differently.That is, if instead of writing

UNION ALL
    SELECT  employee_id, manager_id, lvl+1
    FROM employees, tmptab
    WHERE (manager_id = empId)I write
. . .
UNION ALL
    SELECT  employee_id, manager_id, lvl+1
    FROM employees CROSS JOIN tmptab
    WHERE (manager_id = empId)I get the following error message
ERROR at line 4: ORA-32044: cycle detected while executing recursive WITH query

I remember, oracle supports both comme notation and CROSS JOIN for Cartesian product (= cross product). For example

SQL> WITH tmptab1 AS
  2  (
  3      SELECT 'a1' AS colval FROM DUAL UNION ALL
  4      SELECT 'a2' AS colval FROM DUAL UNION ALL
  5      SELECT 'a3' AS colval FROM DUAL
  6  ),
 [code]....

SQL> So if both comma notated and CROSS JOIN have the same semantic, why I get a cycle for the above mentioned recursive subquery factoring whereas the very same query works pretty well with comma between the table names instead of CROSS JOIN? Because if a cycle is detected (ancestor = current element) this means that the product with CROSS JOIN notation is generating some duplicates which are absent in the result of the comma notated Cartesian product.

View 9 Replies View Related

Forms :: FRM-41211 / Integration Error / SSL Failure Running Another Product

Feb 5, 2013

I have a form with a button for "Print Report " when I click button following error occurred :

FRM-41211 :integration error : SSL failure running another product

View 1 Replies View Related

Forms :: Integration Error - SSL Failure Running Another Product With HP Deskjet D2680?

Sep 14, 2011

"Integration error: SSL failure running another product" with HP Deskjet D2680.We are using 10gR2, Forms6i, and Report6i. The server is Windows 2003 Server, and the OS of the printer is XPIn one of our module, upon calling one report we always encounter Integration error: SSL failure running another product. Sometimes when we do not encounter it, the spooling takes too much time and it takes 6minutes just to print the first page of the report, succeeding pages takes 2-3minutes in interval.

At first we thought that the memory of the PC is the problem, but we tried to connect it to a 2gig RAM Win7 laptop, another laptop with 1gig RAM XP, and a 1g RAM Desktop. We tested 5 computers but the same problem occurs.

The problem is not encountered after we tried other HP Printer(HP 3940, 6988, & D4360). I just want to know the problem with the HP Deskjet D2680.

View 2 Replies View Related

Networking And Gateways :: Installation Product Specific Prerequisite Checks Window?

Apr 18, 2010

in oracle instalation when Product-Specific Prerequisite Checks window appears it show me theres a warning with checking network configration as shown in pic.theres a sites says that its not necessary to make it succeeded and its ok to leave it. should i leave this warning or it should be succeeded?

View 9 Replies View Related

SQL & PL/SQL :: Writing Procedure In User 2 Schema Accessing Table Product - Not Compiled?

Dec 17, 2010

I have got 2 users as user1 and user2.I have used the following statements from user 'user1':

create role GENEVAOBJECTS;
grant select, insert, update, delete on PRODUCT to GENEVAOBJECTS;
grant GENEVAOBJECTS to user2;

In the above statements, product is a table. Now, I could able to access this table from user 'user2'. But however if I write a procedure in user2 schema accessing the table product, then the procedure is not getting compiled.

create or replace procedure test_prc as
v_test number(9);
begin
select product_id into v_test
from PRODUCT where rownum=1;

[code]...

why I cannot access that table from procedure?

View 8 Replies View Related







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