SQL & PL/SQL :: Write Select Query To Get Sum Of Quantity For Each Type Of Item?

May 26, 2010

I want to write select query to get sum of quantity for each type of item ,in the table I have itemnum,itemtype and quantity ,type have value(A,B,C,....)

the result will be like that ex:

item.No A B C D ....

10 50 60 80 20

--- 50 will be sum(quantity) for item type A

how can I do that, I mean how can I retrieve many column from the same field?

View 17 Replies


ADVERTISEMENT

Write A Query Based On Inventory On Hand Packed Quantity

Jul 18, 2013

which table do I have to use to get the Inventory On Hand Packed Quantity  based on Oracle EBS Suite using backend? 

View 2 Replies View Related

Client Tools :: Possible To Write Select Query O/p In HTML Tag?

Jan 24, 2011

is it possible to write select query o/p in html tag?

View 6 Replies View Related

Application Express :: Possible To Avoid Hard-coding Item In Shared Select List Query

Sep 4, 2012

I created a "Select List" in the Shared Components area as follows:

My goal is to create a shared select list that can be used on any form for a particular Field.

IF :P10_FK_RSTA_CODE is NULL THEN
RETURN
'select col_description, col_code_pk
from CODE_TABLE
where col_active_ind = ''Y''';
[code].....

Which works great, but how can I make the select list work for the same column on a different form? Because :P10_FK_RSTA_CODE is hard-coded in the function, I can't reuse this Function from a different form. (I could rename the Field on every form to the same name, but I'm hoping there is a better way.)

Is there a way I can pass in a variable from the Field Item to make this dynamic where I can use it on any form?

View 1 Replies View Related

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

Jan 21, 2011

I have a two question.

Question 1:How to select all columns from table except those columns which i type in query

Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data

View 5 Replies View Related

PL/SQL :: Oracle 10g - Write Select Statement With XML

Sep 26, 2012

I have Oracle 10g. In a table with just one record there is a clob with the following xml. In the following clob, there could be any number of Emp elements.

<?xml version="1.0"?>
<ROWSET>
<Emp>
<ENAME>SMITH</ENAME>
<EMPNO>7369</EMPNO>
<SAL>800</SAL>
</Emp>
<Emp>
<ENAME>ALLEN</ENAME>
<EMPNO>7499</EMPNO>
[code]...

I want to write a select statement which gives me result as follows (all ENAME and EMPNO from the xml)

ENAME EMPNO
SMITH 7369
ALLEN 7499
WARD 7521

View 4 Replies View Related

Forms :: Item Validation With Type

Oct 31, 2011

I have one column Value his Datatype is varchar2 and I want to validate it with Type column his values are(Char,Date,Number). If Column Type have value Number then column value should accept number input only. How we 'll achieve this.

View 3 Replies View Related

SQL & PL/SQL :: Write A SELECT Statement That Returns One Row For Each Customer With Four Columns

Feb 16, 2012

I am student of Oracle programming(SQL).Has a question on MIN and MAX functions:

- "Write a SELECT statement that returns one row for each customer with four columns--the customer's first and last name, the date of the customer's most recent order, and the date of the customer's oldest order and the difference between the two dates. Sort the result set so the customers who have been ordering with the company the longest appear first. Use most recent order date as a tie-breaker for the sort."

where orders and order_details,customers are 3 different.this is my query ...but its not working.

SELECT DISTINCT customers.customer_first_name||' '||customers.customer_last_name AS Customer_name,MAX(order_date) AS "Most recent order", MIN(order_date) AS "Oldest order"
FROM customers
INNER JOIN orders
ON customers.customer_id=orders.customer_id
LEFT JOIN order_details
ON orders.order_id=order_details.order_id
LEFT JOIN items
ON order_details.item_id=items.item_id
ORDER BY Customer_name

View 6 Replies View Related

SQL & PL/SQL :: Access Individual Item From A Table Of Type ROWTYPE

Jun 1, 2010

Script for test:

CREATE TABLE TEST(empno VARCHAR2(4), empname VARCHAR2(50), empstd NUMBER(2))
insert into test values(0001,'A',2);
insert into test values(0002,'B',5);
insert into test values(0003,'C',2);
insert into test values(0004,'D',7);
insert into test values(0005,'E',9);

Now I want to get empno for the particular employees based upon subscript and I have written below

DECLARE
CURSOR cur_rec
IS
SELECT *
FROM TEST;
TYPE cur_type IS TABLE OF cur_rec%ROWTYPE;
v_cur_rec cur_type;
BEGIN
OPEN cur_rec;
FETCH cur_rec
BULK COLLECT INTO v_cur_rec;
[code]....

how to access individial item froma table of rowtype data.

CM: Added [code] tags, please do so yourself next time, see the orafaq forum.

View 5 Replies View Related

Forms :: Text Item With Long Data Type?

Sep 6, 2011

I have a form with an item with "datatype=long". when I am typing in "Arabic" or "Persian" it break the text before line ends.but when I type in English "text" go to end line(in other words line fills to the end) and then breake to next line.It show that the problem is LANGUAGE.

nls_lang=AMERICAN_AMERICA.ar8mswin1256
Regional and language/regional option=Arabic(U.A.E)
in the block/item property : wrap_style= word ----multi_line=yes

View 2 Replies View Related

JDeveloper, Java & XML :: How To Write Results Of Select Statement Into File

Feb 25, 2011

SQL> desc res;
Name Null? Type
----------------------------------------- -------- ----------------------------
RESULT PUBLIC.XMLTYPE

SQL> select * from res;

RESULT
--------------------------------------------------------------------------------
<fine No="2"><stdNo>2</stdNo><value>300</value><reason>breaks keyboard</reason><
date>2011-10-03</date></fine>

how can me write results of select statement into xml file instead of show them on screen?

View 20 Replies View Related

Application Express :: Change Font Size For Item Of Type Display

Jul 13, 2012

how to change the font size for an item of type display only ?

View 4 Replies View Related

SQL & PL/SQL :: How To Write Correlated Sub Query

Sep 13, 2010

I have employee, location, city tables, I have written following code to use joins

select ename from employee e, location l, city c
where e.c_locationid= l.locationid and l.cityid= c.cityid and c.cityname='XYZ';

The same can be written by using non- correlated sub query as follow..

select ename
from employee
where c_locationid in (select locationid from location where
cityid in (select cityid from city where cityname='XYZ'))

I need to implement the same concept using correlated sub query...

View 15 Replies View Related

SQL & PL/SQL :: How To Write Column To Row In A Query

Nov 17, 2005

How to write column to row...in a SQL query?

For example..

SQL> select empno,deptno from emp where empno = 7369;
EMPNO DEPTNO
---------- ----------
7369 20
7369 10
7369 40

The above output to be written in a single row like given below.

7369 20 10 40

1) Actually it may change dynamically...It may be 2 records for some values and there may be 10 records for some value and different for some other

2) It should be in SQL query only..Not in procedures or functions.

View 9 Replies View Related

Forms :: How To Write Query For ADD Button

Jul 23, 2010

Without using any templates, i created a customized sample form....now i want to add data thru that form....i dont know where to write code and what is code if i want to add data?

View 9 Replies View Related

SQL & PL/SQL :: How To Write Query To Convert XML To Table

Oct 15, 2012

I have table xx_xml_test m which have row single entry

<logentry
revision="3">
<author>MA111300</author>
<date>2012-10-03T12:42:40</date>
<paths>
<path
[code].......

i want convert the table like below

Revision author date kind action path
3 MA111300 2012-10-03 12:42:40 file A /root.txt
3 MA111300 2012-10-03 12:42:40 file A /sample2/test_2.txt

View 2 Replies View Related

SQL & PL/SQL :: Write Query To Show Monthly Salary?

Jul 12, 2012

i have table structure (emp_no, emp_sal,emp_startwork)

i want query to show the monthly salary such as jan month salary Feb month salary

View 2 Replies View Related

SQL & PL/SQL :: Write A Query To See How Many (no) Employees Getting Salary 3000?

Dec 21, 2010

write a query to see how many(no) employees getting salary 3000 & what are their names respectively.

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

SQL & PL/SQL :: Write A Single Query Showing Number Of Requests

Jan 15, 2011

I need to write a single query showing the number of requests (job_no) in my organization sections (1A,3A,4C,9Z....etc) based on these conditions..Less than 2 months & between 2 and 6 & between 6 and 12month & more than 12 months in my organization sections.

--Table structure

SQL> desc job_transfer
Name
----------------------
JOB_NO (Request number)
IN_SECT (name of entered section)
IN_DATE (date entered the section)
OUT_SECT
OUT_DATE (date went out from section)

I tried using UNION ALL but it didnt work fine.

View 3 Replies View Related

SQL & PL/SQL :: Write A Query Which Shows Data Of One Table Not Present In Other?

Apr 3, 2011

I am writing following query
SELECT DISTINCT a.list_type_code, a.list_type_name
FROM jls_list_type a, jls_list_control b
WHERE b.jalsa_srl = :jalsa_srl
AND b.list_no != a.list_type_code
ORDER BY list_type_code

I just want to display only those records from JLS_LIST_TYPE which is not present in other table JLS_LIST_CONTROL ... for this i wrote above query but it is not working.

View 9 Replies View Related

SQL & PL/SQL :: Write Outer Query Which Will Bring Individual Records Which Don't Sum Up To Zero

May 9, 2011

I have to write a sub query / build a logic for the below.

There are several accounts which should have a zero balance i.e sum of all the amoutns in that account should be zero. If they are non zero , i have to report which amounts make up non zero balance.

If i have amts as +20 , -20 , -30,-10 i.e the sum is -40 indicating a non zero amount. I need the entire details of the records which makes up non zero sum. So in above case details related to -30 aand -10.

I'm using a sum group clause on several fields at which sum is required to be checked ie. date , account , currency . query that will bring individual records that don't make the sum zero.

Is it possible to write a outer query which will bring individual records which don't sum up to zero.

View 14 Replies View Related

Client Tools :: Write Sql Query O / P In HTML Page?

May 31, 2012

is it possible to write sql query o/p in HTML page?

View 3 Replies View Related

SQL & PL/SQL :: Write Single Row Sub-query Which Returns ID_2 Value Of Row With Del_flag As N

Feb 13, 2012

In my main query, There is a single row sub query which returns row with del_flag as N. Now in my table, there are 2 rows getting added from the incoming feed with del_flag as Y & N.

Here is my Table:

ID_1 DEL_F ID_2
-------------------------------
16643162 Y 49696
16643162 N 16643162
16612344 Y 98888

So how can i write a single row sub-query which returns ID_2 value of a row with del_flag as N. If there is no row with DEL_F as N, it should return ID_2 value of row with DEL_F as Y.i have tried below query, but it showing the error.

select
(case
when min(del_f)='N' then to_number(ID_2)
when min(del_f)='Y' then to_number(ID_2)
end ) ID_2 from table where ID_1=?

Here, im passing ID_1 value from main query

View 8 Replies View Related

PL/SQL :: Write Dynamic SQL To Query Unknown Number Of Columns

Jun 6, 2012

I need to write a dynamic SQL in PL SQL to query an unknown number of columns. Let me take a simple example query here:

SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS

If I have known the number of columns, e.g. querying two columns: "FIRST_NAME" and "LAST_NAME", I can write a DYNAMIC SQL based on the template in table 8-2 of URL....

DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
FIRST_NAME varchar2(200);
LAST_NAME varchar2(200);
BEGIN
cur_hdl := dbms_sql.open_cursor; -- open cursor
stmt_str := 'SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS';
[code]....

However, if I wish to write a dynamical sql to query these two columns for a more general purpose (which should meet the requirement to query different number of columns, e.g. three columns, FIRST_NAME, LAST_NAME, BIRTHDAY instead of two columns FIRST_NAME and LAST_NAME). To do this I first try to query the same two columns but using a different method, following URL.....My code for the same query has error, and I cannot solve it.

DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
l_columns dbms_sql.desc_tab;
l_dummy NUMBER;
l_value NUMBER;
[code]....

View 7 Replies View Related

SQL & PL/SQL :: Select All The Elements Of A Record Type

May 24, 2011

Is there any way that I can check what are the elements present in a pl sql record type by querying in table?

For example if I want to check what are elements present in oe_order_pub.header_rec_type and I don't want to open the package, then in which table I should query? Is it possible?

View 2 Replies View Related

Export/Import/SQL Loader :: How To Write The Query Option In Expdp

Jun 5, 2012

how to write the query option in expdp ....in expdp by using query option...

where columnname between '05-May-12 02:57:00.000 AM' and '6-May-12 02:59:59.999 AM';

View 4 Replies View Related

Insert Or Select Date Data Type Value In AM Or PM?

Oct 26, 2006

How do i insert or select date data type value in am or pm Eg 10:30:00 am or 10:35:00 pm

View 3 Replies View Related

SQL & PL/SQL :: Write Query To Delete Similar Records In Particular Fields (columns) In Different Tables

Jul 17, 2012

write a query to delete similar records in particular fields(columns) in different tables.

View 2 Replies View Related

JDeveloper, Java & XML :: Select Data From XML Type Column

Jan 24, 2013

I have a table Table_xml_tmp which has a column MESSAGECONTENTS as XMLType data which contains data as follows


"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xclimSchemaMain xmlns="http://clones.telcordia.com">
<response>
<clHeader>
<msgName>queryPlace</msgName>
[code]......

I need to get all rows where node <operationStatus> value as SUCCESS. I am running following query but it is returning null value insted of data of columns

SELECT a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()
FROM Table_xml_tmp a
WHERE a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()= 'SUCCESS'

View 2 Replies View Related







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